How do I perform date-part comparison in EF

Asked 13/9, 2012 at 18:7 Answered 13/9, 2012 at 18:22

Solved c#entity-framework t-sql linq-to-entities datetime-comparison

i heard people saying date time comparison do not work just due to time part because datetime has time part.

in sql i always compare datetime like this way and it works fine

select * from employee
where convert(varchar,dob,112) > '20111201' // this yyyymmdd format.

how could i simulate this in a LINQ query?

Hammock answered 13/9, 2012 at 18:7 Comment(2)

Do you mean both Entity Framework (=LINQ to Entities) and LINQ to SQL (pre EF ORM)? – Isochronous 13/9, 2012 at 18:24

FWIW, if you're using SQL Server 2008 or above, you should probably be using Date (rather than DateTime) for fields whose time portion is irrelevant (it's unlikely that you would actually HAVE the time of day that someone was born, and even less likely that it would be meaningful to you). Using DateTime when Date is available and you don't care about the time would be like storing all of your numeric data in a float regardless of whether or not the data is inherently integral. – Auscultate 13/9, 2012 at 18:26

The one thing to keep in mind is that operations on DateTime structs that represent database columns don't translate to SQL. So, you cannot write a query like:

from e in EfEmployeeContext
where e.DOB.Date > new DateTime(2011,12,01);

... because e.DOB represents the DOB column in the database, and EF won't know how to translate the Date sub-property.

However, there's an easy workaround depending on what dates you want:

If you want to include all employees that have a DOB on 12/01/2011 as well as those born after that date, then simply query:
```
from e in EfEmployeeContext
where e.DOB > new DateTime(2011,12,01);
```
If you want to include only employees born after 12/01/2011, then query:
```
from e in EfEmployeeContext
where e.DOB >= new DateTime(2011,12,02);
```

In short, the criteria, meaning a constant or literal DateTime you're comparing against, can be set up however you want. You just can't make radical modifications to properties that represent DB columns within the where predicate. That means you can't compare one DateTime column to a projection of another DateTime column, for instance:

    //get all employees that were hired in the first six months of the year
    from e in EfEmployeeContext
    where e.HireDate < new DateTime(e.HireDate.Year, 7, 1);

Tractable answered 13/9, 2012 at 18:18 Comment(2)

does this code from e in EfEmployeeContext where e.DOB > new DateTime(2011,12,01); work in all culture because USA date format is different where UK has different. – Hammock 13/9, 2012 at 18:35

The constructor of a DateTime always takes the year first, then month, then day, regardless of the culture that may be used to display it as a string. – Tractable 13/9, 2012 at 20:39

If you're using .NET 4 or above, just use the EntityFunctions.TruncateTime helper method. This will translate this type of datetime-to-date conversion to SQL for you.

from e in EfEmployeeContext
where EntityFunctions.TruncateTime(e.DOB) > new DateTime(2011,12,01);

Literatim answered 13/9, 2012 at 18:22 Comment(4)

If you're using LINQ to SQL then I would remove the tags to EntityFramework and linq-to-entities, as well as the reference to EF in your question title. Using LINQ to SQL means that your question becomes irrelevant. – Literatim 13/9, 2012 at 18:25

date comparison can't be there when people use LINQ to SQL? – Hammock 13/9, 2012 at 18:37

That's not what I'm saying, I'm saying that your question is referencing EntityFramework not LINQ to SQL. It'd be the same as asking "How do I do x in C#" and then responding "Thanks, but how would I do this in Java?". – Literatim 13/9, 2012 at 18:40

EntityFunctions.TruncateTime(e.DOB) is obsolete, batter try this instead System.Data.Entity.DbFunctions.TruncateTime(e.DOB) – Lohengrin 24/8, 2015 at 5:26