CODEWITHSUNDEEP
c#entity-frameworklinqlinq-to-entities
Manprit Singh
Manprit Singh

Reputation: 855

Best way to compare date in Entity Framework

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

enter image description here

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.

Upvotes: 9

Views: 34739

Answers (4)

ssilas777
ssilas777

Reputation: 9764

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

Upvotes: 8

Harsh Vyas
Harsh Vyas

Reputation: 326

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

Upvotes: 6

Ivan Stoev
Ivan Stoev

Reputation: 205849

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

Upvotes: 24

MutantNinjaCodeMonkey
MutantNinjaCodeMonkey

Reputation: 1249

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

Upvotes: 1

Related Questions