CODEWITHSUNDEEP
c#entity-frameworklinqlinq-to-entitiesdbfunctions
GibboK
GibboK

Reputation: 73898

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

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?

Upvotes: 174

Views: 132986

Answers (11)

whatever
whatever

Reputation: 3617

I've faced this same issue and it seems that it is really caused by the presence of a call to the .Date property within the Where method. When removed, the error disappears. Consider that calling the .Date property on any side of the comparison operator causes this error. Calling the .Date property outside and before the Where method is enough to solve this error.

Upvotes: -2

Willy David Jr
Willy David Jr

Reputation: 9131

I have the same issue with Entity Framework 6.1.3

But with different scenario. My model property is of type nullable DateTime

DateTime? CreatedDate { get; set; }

So I need to query on today's date to check all the record, so this what works for me. Which means I need to truncate both records to get the proper query on DbContext:

Where(w => DbFunctions.TruncateTime(w.CreatedDate) == DbFunctions.TruncateTime(DateTime.Now);

Upvotes: 1

user6243946
user6243946

Reputation:

Simplified:

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

Upvotes: 5

WaZ
WaZ

Reputation: 1757

You should now use DbFunctions.TruncateTime

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

Upvotes: 107

Abdus Salam Azad
Abdus Salam Azad

Reputation: 5502

Use the bellow code for using EF6:

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

Upvotes: 2

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236188

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.

Upvotes: 336

cozmin-calin
cozmin-calin

Reputation: 421

Another solution could be:

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

Upvotes: 0

phil soady
phil soady

Reputation: 11308

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.

Upvotes: 7

Leonel Sanches da Silva
Leonel Sanches da Silva

Reputation: 7220

EntityFunctions is obsolete. Consider using DbFunctions instead.

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

Upvotes: 23

jvrdelafuente
jvrdelafuente

Reputation: 2002

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.

Upvotes: 19

Babul Mirdha
Babul Mirdha

Reputation: 3902

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

Upvotes: 8

Related Questions