1

I have a database with content, designed to be very generic. One of the columns is MetaTag1, of type nvarchar(255), which contains dates in string format. I can't change this to DateTime type. I use Entity Framework code first to query the table with LINQ.

Now I have to do something like:

 var dateAsString = "12/04/2015";
 var result = context.MetaTags.Where(mt => DateTime.Parse(dateAsString) > DateTime.Parse(mt.MetaTag1));

This doesn't work, as I get the exception:

 'LINQ to Entities does not recognize the method 'System.DateTime Parse(System.String)' method, and this method cannot be translated into a store expression.'

One solution would be to first materialize the results in memory with ToList() and do the filtering in LINQ to entities. But this means that all results have to be retrieved first.

Is there another way to compare strings as dates in LINQ?

Thanks!

L-Four
  • 13,345
  • 9
  • 65
  • 109
  • I take it the string in the database is stored in the stupid format "12/04/2015" as well? – gregmac Apr 27 '15 at 19:25
  • It may be possible to use a direct a string comparison. Take a look at this: http://stackoverflow.com/questions/7740693/big-issue-in-converting-string-to-datetime-using-linq-to-entities – David Tansey Apr 27 '15 at 19:27
  • @gregmac: We could agree on a format that consumers must use. – L-Four Apr 27 '15 at 19:27
  • possible duplicate of [Linq to Entities DateTime Conversion](http://stackoverflow.com/questions/2555927/linq-to-entities-datetime-conversion) – Jason Boyd Apr 27 '15 at 19:28
  • @JasonBoyd: my question is not about LINQ to entities. – L-Four Apr 27 '15 at 19:28
  • 6
    @L-Three You are using LINQ to query Entity Framework. The error even contains 'LINQ to Entities' in it. Your question is related to LINQ to entities. See the chosen answer in the link I provided but also look at the answer with the highest votes (which happens to not be the chosen answer). – Jason Boyd Apr 27 '15 at 19:31
  • Ok I will check tomorrow and let you know if it worked. – L-Four Apr 27 '15 at 19:34
  • You can move your comparison into an Expression that Linq can use. See: http://stackoverflow.com/a/19618035/49251 – DWright Apr 27 '15 at 19:35
  • 3
    If you can actually use [ISO 8601](http://en.wikipedia.org/wiki/ISO_8601) (`YYYY-MM-DD`) then it sorts lexicographically, which means string comparison of dates in this format work without any conversion. It's part of the reason it's [so popular](https://xkcd.com/1179/) in computing. Of course, if you can change that format, I'm not sure why you can't just use a native Date type.. – gregmac Apr 27 '15 at 20:23
  • @JasonBoyd: just to let you know that the DateDiff method works. Thanks! – L-Four Apr 28 '15 at 12:32

1 Answers1

0

Solved like:

 var result = context.MetaTags.Where(mt => SqlFunctions.DateDiff("s", mt.MetaTagFilter1, keyValueMetaTagFilter.MetaTagValue) <= 0);

Dates have to be stored in database in format "YYYY-MM-DD".

L-Four
  • 13,345
  • 9
  • 65
  • 109