The specified type member 'Date' is not su

问题:

Using this code in Entity Framework I receive the following error. I need to get all the rows for a specific date, DateTimeStart is of type DataType in this format 2013-01-30 12:00:00.000

Code:

 var eventsCustom = eventCustomRepository.FindAllEventsCustomByUniqueStudentReference(userDevice.UniqueStudentReference)
                    .Where(x =>  x.DateTimeStart.Date == currentDateTime.Date);

---

Error:

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

Any ideas how to fix it?

回答1:

DateTime.Date cannot be converted to SQL. Use EntityFunctions.TruncateTime method to get date part.

var eventsCustom = eventCustomRepository
.FindAllEventsCustomByUniqueStudentReference(userDevice.UniqueStudentReference)
.Where(x => EntityFunctions.TruncateTime(x.DateTimeStart) == currentDate.Date);

UPDATE: As @shankbond mentioned in comments, in Entity Framework 6 EntityFunctions is obsolete, and you should use DbFunctions class, which is shipped with Entity Framework.

回答2:

You should now use DbFunctions.TruncateTime

var anyCalls = _db.CallLogs.Where(r => DbFunctions.TruncateTime(r.DateTime) == callDateTime.Date).ToList();

回答3:

I would like to add a solution, that have helpt me to solve this problem in entity framework:

var eventsCustom = eventCustomRepository.FindAllEventsCustomByUniqueStudentReference(userDevice.UniqueStudentReference)
                .Where(x =>  x.DateTimeStart.Year == currentDateTime.Year &&
                             x.DateTimeStart.Month== currentDateTime.Month &&
                             x.DateTimeStart.Day == currentDateTime.Day
    );

I hope that it helps.

回答4:

EntityFunctions is obsolete. Consider using DbFunctions instead.

var eventsCustom = eventCustomRepository.FindAllEventsCustomByUniqueStudentReference(userDevice.UniqueStudentReference)
   .Where(x => DbFunctions.TruncateTime(x.DateTimeStart) == currentDate.Date);

回答5:

Always use EntityFunctions.TruncateTime() for both x.DateTimeStart and currentDate. such as :

var eventsCustom = eventCustomRepository.FindAllEventsCustomByUniqueStudentReference(userDevice.UniqueStudentReference).Where(x => EntityFunctions.TruncateTime(x.DateTimeStart) == EntityFunctions.TruncateTime(currentDate));

回答6:

Just use simple properties.

var tomorrow = currentDateTime.Date + 1;  
var eventsCustom = eventCustomRepository.FindAllEventsCustomByUniqueStudentReference(userDevice.UniqueStudentReference)
                            .Where(x =>  x.DateTimeStart >= currentDateTime.Date 
                                   and x.DateTimeStart < tomorrow);

If future dates are not possible in your app, then >= x.DateTimeStart >= currentDateTime.Date is sufficient.

if you have more complex date comparisons, then check Canonical functions and if you have EF6+ DB functions

More Generally - For people searching for issues Supported Linq methods in EF can explain similar issues with linq statements that work on Memory base Lists but not in EF.

回答7:

Use the bellow code for using EF6:

(DbFunctions.TruncateTime(x.User.LeaveDate.Value)

回答8:

Another solution could be:

var eventsCustom = eventCustomRepository.FindAllEventsCustomByUniqueStudentReference(userDevice.UniqueStudentReference).AsEnumerable()
   .Where(x => x.DateTimeStart.Date == currentDate.Date).AsQueryable();

回答9:

Simplified:

DateTime time = System.DateTime.Now;
ModelName m = context.TableName.Where(x=> DbFunctions.TruncateTime(x.Date) == time.Date)).FirstOrDefault();