Implementing DateTime.ToString(string) with Linq to Entities

Question

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?

It might be better to convert keys to dates and not date to string, since column you are filtering by is of date type. — Evk, Dec 27 '17 at 17:37
Note that catching _any_ exception and just returning an empty list is probably not a good design - you can't tell if there is an underlying error (or worse, what that error is) or if your filter just didn't return any objects. — D Stanley, Dec 27 '17 at 17:50
It is wise to specify a `CultureInfo` like `CultureInfo.Invariant` when you convert dates to strings for machine consumption. Otherwise, you might end up with a surprising result. To give a concrete example, if the culture of the user that your code executes as is Thai then the (UTC) year 2017 is formatted as 2560. — Martin Liversage, Dec 27 '17 at 20:50
@Evk Ideally yes, but not all of the keys will be solely dates, which is why I have to leave it as string — JChristen, Dec 28 '17 at 09:34
But I mean leave keys as strings but convert to dates just for this particular query (since there you filter by date). Approximately like existing answer suggests. — Evk, Dec 28 '17 at 10:36

score 1 · Answer 1 · answered Dec 27 '17 at 17:48

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);
}

Implementing DateTime.ToString(string) with Linq to Entities

1 Answers1