Search code examples
.netentity-frameworklinq

EF comparing strings of dates in format "YYYYMMDD" in a linq query?

In my table there is a varchar field which contains dates in format YYYYMMDD (that's how SAP stores dates). Anyway to run a query against this field similar to:

from c in db.Set<MyTable>()
   where c.Date >= "20120921" && c.Date <= "20121021"
   select c

I tried to do a String.Compare, but EF can't translate that to SQL.

where String.Compare(c.Date, "20120921") >= 0

I also tried to convert both strings to int and do the comparison, and again EF doesn't support that.

Solution

  • You can try using SqlFunctions.DateDiff method overload that takes three strings, like this:

    var res = from c in db.Set<MyTable>()
   where SqlFunctions.DateDiff("dd", c.Date, "20120921") >= 0
      && SqlFunctions.DateDiff("dd", c.Date, "20121021") < 0
   select c