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

Question

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?

I am able to use x.DateTimeStart.Date in EF Core 2.1.1 – Kirsten Aug 01 '19 at 19:54 — Kirsten, Aug 01 '19 at 19:54

score 325 · Accepted Answer · edited Mar 08 '16 at 08:44

325

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.

edited Mar 08 '16 at 08:44

Mr. Flibble

26,564
23
69
100

answered Jan 30 '13 at 10:29

Sergey Berezovskiy

232,247
41
429
459

1

I have to slight modify your version .Where(x => EntityFunctions.TruncateTime(x.DateTimeStart) == currentDate.Date); let me know your thougs – GibboK Jan 30 '13 at 10:46
1

I hope you do not mind I have edit your answer adding .date if you agree, so just for reference :-) thanks for your support, I really appreciate it – GibboK Jan 30 '13 at 11:16
1

@GibboK sure, no problem :) That was just for purpose of formatting long string. – Sergey Berezovskiy Jan 30 '13 at 11:20
77

EntityFunctions is obsolete, instead use DbFunctions.TruncateTime method – Devesh May 06 '14 at 11:26
2

The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported. – SAR May 07 '17 at 10:14
use DbFunctions.TruncateTime and EntityFunctions are deprecated. – Prasad Kanaparthi Sep 01 '19 at 17:44
It's block model join by foreign key. – love prince Nov 30 '21 at 03:27

score 107 · Answer 2 · edited Sep 06 '17 at 13:21

107

You should now use DbFunctions.TruncateTime

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

edited Sep 06 '17 at 13:21

Soner Gönül

97,193
102
206
364

answered Mar 12 '14 at 21:50

WaZ

1,757
4
19
27

see: http://stackoverflow.com/questions/23911301/what-are-the-difference-between-entityfunctions-truncatetime-and-dbfunctions-tru – juFo May 04 '15 at 08:32
1

The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported. – SAR May 07 '17 at 10:16

score 23 · Answer 3 · answered Apr 20 '14 at 06:06

EntityFunctions is obsolete. Consider using DbFunctions instead.

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

score 19 · Answer 4 · answered Feb 26 '14 at 09:58

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.

score 8 · Answer 5 · answered Jun 13 '13 at 17:32

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

phil soady · Answer 6 · 2014-06-25T23:50:08.247

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.

score 5 · Answer 7 · edited Dec 05 '17 at 23:48

5

Simplified:

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

edited Dec 05 '17 at 23:48

devlin carnate

8,309
7
48
82

answered Dec 01 '16 at 08:00

score 2 · Answer 8 · answered May 15 '17 at 15:40

2

Use the bellow code for using EF6:

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

answered May 15 '17 at 15:40

Abdus Salam Azad

5,087
46
35

score 1 · Answer 9 · answered Aug 25 '20 at 04:01

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

score 0 · Answer 10 · answered Jan 16 '15 at 08:16

0

Another solution could be:

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

answered Jan 16 '15 at 08:16

cozmin-calin

421
2
6
19

whatever · Answer 11 · 2022-07-13T12:09:24.377

-2

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.

edited Jul 13 '22 at 12:09

answered Jul 13 '21 at 08:38

whatever

2,492
6
30
42

*When removed, the error disappears* -- Yeah, but they *need* this `.Date` call, or an alternative, and not outside the `Where`. They already know it throws an exception. It doesn't "seem" to be caused by `.Date`, it *is* caused by it. – Gert Arnold Aug 01 '21 at 12:21
As already said, you can call the Date property before the Where and this solves it – whatever Jul 13 '22 at 12:11
How would you call `x.DateTimeStart.Date` outside the `Where`? – Gert Arnold Jul 13 '22 at 12:26
Using a `Select` – whatever Jul 13 '22 at 13:05
Just show how you make the code in the question run with your ideas. Words aren't convincing. – Gert Arnold Jul 13 '22 at 13:27

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

11 Answers11

Linked

Related