How to compare date and time in EF

Question

I heard people talking about comparing dates do not work just because of the time part, because datetime has a time part.

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

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

how did i simulate this in a LINQ query?

Answer 1

Keep in mind that operations with DateTime structures that represent database columns are not translated into SQL. Thus, you cannot write a query, for example:

 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 does not know how to translate the Date sub-property.

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

• If you want to include all employees who have DOB 12/01/2011, as well as those who were born after this date, simply run the 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 run the query:

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

In short, criteria meaning a fixed or letter date that you are comparing can be customized as you want. You simply cannot make drastic changes to the properties that the database columns represent inside the predicate. This means that you cannot compare one DateTime column with the projection of another DateTime column, for example:

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

Answer 2

If you are using .NET 4 or higher, just use the EntityFunctions.TruncateTime helper method. This will convert this type of date and time conversion to SQL for you.

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