Implementing DateTime.ToString(string) with Linq to Entities

c# entity-framework linq

402 观看

1回复

308 作者的声誉

Is there a way around Linq to Entities inability to use the DateTime.ToString(string) method? Using the method below throws the exception LINQ to Entities does not recognize the method 'System.DateTime ToString(System.String)' method, and this method cannot be translated into a store expression., which is to be expected as it cannot be converted into sql.

protected override bool TryGetEntitiesByKey(IEnumerable<string> keys, out IEnumerable<Trade> entities)
{
  return this.TryLoadBase(x => keys.ToList().Contains(x.date.ToString("yyyyMMdd"), out entities);
}

The code in the generic provider, which is what uses the func to filter the results from the db, is below.

protected virtual bool TryLoadBase(Expression<Func<TEntity, bool>> filter, out IEnumerable<TEntity> entities)
{
  bool loaded = true;
  try
  {
    using (var db = this.dbContext)
    {
      entities = db.Set<TEntity>().Where(filter).ToList();
    }
  }
  catch (Exception ex) // Exception caught here
  {
    loaded = false;
    entities = new List<TEntity>();
  }
  return loaded;
}

After getting the exception stated earlier, I tried replacing the date.ToString("yyyyMMdd") with this:

SqlFunctions.StringConvert((double)notional.date.Year).Trim() +
SqlFunctions.Replicate("0", 2 - SqlFunctions.StringConvert((double)notional.date.Month).Trim().Length) + SqlFunctions.StringConvert((double)notional.date.Month).Trim() +
SqlFunctions.Replicate("0", 2 - SqlFunctions.StringConvert((double)notional.date.Day).Trim().Length) + SqlFunctions.StringConvert((double)notional.date.Day).Trim()

Whilst this works, it has to be inline since otherwise you get the same message about LINQ to Entities not recognising the method. Which means it's a lot harder to read, and can't be reused by anything else.

After looking around I found this question: (Reusable Calculations For LINQ Projections In Entity Framework) but am reluctant to include any external packages simply to solve this one issue.

There are also the answers on Entity Framework 4 / Linq: How to convert from DateTime to string in a query?, but as far as I'm aware using AsEnumerable() like in the accepted answer would mean enumerating the db set into memory, which I cannot do due to memory limitations. And I couldn't get the second answer to work properly, and it would again suffer from the issues of the SqlFunctions mess above.

If this simply isn't possible, would anybody be able to offer a cleaner alternative to the SqlFunctions block?

作者: JChristen 的来源 发布者: 2017 年 12 月 27 日

回应 1


1

127948 作者的声誉

You are asking the provider to translate the call DateTime.ToString(format) to SQL, which is not supported. You might also need to use EntityFunctions.TruncateTime to compare only the date portion (since that's what the string comparison would do):

You will need to either parse the keys to date/time objects or pass in an IEnumerable<DateTime> instead:

protected override bool TryGetEntitiesByKey(IEnumerable<string> keys, out IEnumerable<Trade> entities)
{
  var keyDates = keys.Select(s => DateTime.ParseExact("yyyyMMdd")).ToList();
  return this.TryLoadBase(x => keyDates.Contains(EntityFunctions.TruncateTime(x.date)), out entities);
}
作者: D Stanley 发布者: 2017 年 12 月 27 日
32x32