Best way to compare date in Entity Framework

Question

I am using date in where clause in Entity Framework and getting following error:

It is due to the below code:

var entity = dbContext.MyTable
                      .Where(w => w.PId = 3 && w.CreatedOn.Date == mydate.Date)
                      .First();

If I convert data into list using .ToList(), then compare to date, it will work fine but if I do so then it will pull data first into code then filter it out. Please help me out with this issue. Currently I am using a stored procedure to resolve the issue but really want this to work.

Solution 1

You can use DbFunctions.TruncateTime canonical function like this

var entity = dbContext.MyTable
    .Where(w => w.PId == 3 && DbFunctions.TruncateTime(w.CreatedOn) == mydate.Date)
    .First();

Solution 2

This is how I ended up doing Date search when I had to consider time (Hour and Minutes portion) also

var entity = dbContext.MyTable
                      .Where(
                    x => x.CreatedOn.Year == mydate.Year
                    && x.CreatedOn.Month == mydate.Month
                    && x.CreatedOn.Day == mydatee.Day
                    && x.CreatedOn.Hour == mydate.Hour
                    && x.CreatedOn.Minute== mydate.Minute
                ).FirstOrDefault();

Solution 3

you cannot compare dates directly by using .Date for this you have to use DbFunctions or EntityFunctions.

I prefer to use DbFunctions

You can use it as given below:

var entity = dbContext.MyTable
                  .Where(w => w.PId = 3 && DbFunctions.TruncateTime(w.CreatedOn) == DbFunctions.TruncateTime(mydate))
                  .First();

Solution 4

EF doesn't currently support translating all granular operations on objects in LINQ to an SQL query (which is what happens prior to that ToList() operation). Some things are (such as .Contains() on primitive collections). But not all things.

So, while you may have expected the query to translate to various "DATEPART" comparisons in the SQL statement, it's just not supported. You have to change your LINQ to accommodate it a bit.

One way is to evaluate the 24-hour range. This will work on either side of the ToList():

DateTime minDate = new DateTime(mydate.Date.Year, mydate.Date.Month, mydate.Date.Second);
DateTime maxDate = minDate.AddSeconds(86399);

var entity = dbContext.MyTable
                      .Where(w => w.PId = 3 && w.CreatedOn >= minDate && w.CreatedOn <= maxDate).First();

View more solutions