The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

While querying LINQ to Entities with check of Date, one may face the error like 'Date' is not supported in LINQ to Entities……

For example consider following LINQ statement, in which we are trying to get all employees created before today’s date:

context.Employees.Where(c => c.CreatedOn >= DateTime.Now.Date);

If you compile this statement, it compiles successfully, however you'll get the error (mentioned below) when this query get executed.

“The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported"

Reason: Entity Framework doesn't allow the use of the Date member of DateTime inside an LINQ query. And because of the use of .Date member it throws the exception as when query get executed DateTime.Date cannot be converted to SQL statement.

Solution: As I mentioned above LINQ to Entities queries are translated to SQL statements, and it unable to translate date members.

To avoid this exception either

·         We need to modify the LINQ statement so that it can easily be translated into SQL statement

·         Or use EntityFunctions.TruncateTime method to get date part.

Solution1 -> Modify your statement to avoid error:

We can modify above mentioned statement by replacing: var currentDate = DateTime.Now.Date; context.Employees.Where(c => c.CreatedOn >= currentDate);

And this statement will be easily translated into LINQ to Entities.

Solution2 -> Use EntityFunctions.TruncateTime() method to get date part (This method is in .NET Framework 4+):

Modify the statement as: context.Employees        .Where(c => c.CreatedOn >= EntityFunctions.TruncateTime(DateTime.Now));