“The specified type member 'Date' is not supported in LINQ ”

4
votes
_dbEntities.EmployeeAttendances.Where(x => x.DailyDate.Date.Equals(DateTime.Now.Date)).ToList();

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

How can i do this get employees data on based on current date in linq query?

4
c#linqentity-frameworklambda

4 Answers

17
votes

EntityFramework cannot convert DateTime.Date to SQL. So, it fails to generate expected SQL. Instead of that you can use EntityFunctions.TruncateTime() or DbFunctions.TruncateTime()(based on EF version) method if you want to get Date part only:

 _dbEntities.EmployeeAttendances
            .Where(x => EntityFunctions.TruncateTime(x.DailyDate) == DateTime.Now.Date)
            .ToList();

Additional info:

EntityFunctions methods are called canonical functions. And these are a set of functions, which are supported by all Entity Framework providers. These canonical functions will be translated to the corresponding data source functionality for the provider. Canonical functions are the preferred way to access functionality outside the core language, because they keep the queries portable.

You can find all canonical functions here and all Date and Time Canonical Functions here.

Update:

As of EF6 EntityFunctions has been deprecated for System.Data.Entity.DbFunctions.

9
votes

Dont use EntityFunctions in EF 6. TruncateTime is in the DbFunctions class:

DbFunctions.TruncateTime(x.DailyDate)
7
votes

If the DailyDate property is already just a date, instead of a date and time, then it would be simplest to just use:

// Outside the query so it becomes a constant, effectively
var today = DateTime.Today;
var employees = _dbEntities.EmployeeAttendances
                           .Where(x => x.DailyDate == today)
                           .ToList();

If it does have times (making the above fail), you could always use:

// Outside the query so it becomes a constant, effectively
var today = DateTime.Today;
var tomorrow = today.AddDays(1);
var employees = _dbEntities.EmployeeAttendances
                           .Where(x => x.DailyDate >= today &&
                                       x.DailyDate < tomorrow)
                           .ToList();

... or use TruncateTime as Farhad's answer suggests. I'd still recommend evaluating DateTime.Today first though:

var today = DateTime.Today;
var employees = _dbEntities.EmployeeAttendances
                       .Where(x => EntityFunctions.TruncateTime(x.DailyDate) == today)
                       .ToList();

Note that Today (like DateTime.Now) uses the system default time zone. You should think carefully about whether that's what you want.

2
votes

Just in case it helps someone... In EF 6, EntityFunctions is obsolete, use DbFunctions class instead. You might want to include the Namespace System.Data.Entity;

eg:

_dbEntities.EmployeeAttendances.Where(x => DbFunctions.TruncateTime(x.DailyDate) == DateTime.Now.Date).ToList();