Entity Framework refusing Date comparison The specified type member 'Date' is not supported in LINQ to Entities

Question

I'm trying to retrieve all the records that match today's date, but I get this exception when I run

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

I tried this solution https://stackoverflow.com/a/14601775/13211556

but no effect same exception, also tried to change the column in the DB to Date only instead of DateTime.

here is the code:

List<int> Ids = context.Record
            .Where(rec => DbFunctions.TruncateTime(rec.Date.Date) == date)
            .Select(rec => rec.Id)
            .ToList();

So I assume you have seen answers such as this https://stackoverflow.com/a/40651639/832052 , could you try using the DbFunctions.TruncateTime on `date` as well? — djv, Sep 16 '21 at 15:41
I wasn't suggesting you change your data model. Only if it is appropriate... — djv, Sep 16 '21 at 15:46

Svyatoslav Danyliv · Accepted Answer · 2021-09-16T16:07:10.750

0

For better performance you have to use comparison operators. You have date without time, so add one day and write appropriate LINQ query.

var nextDay = date.AddDays(1);

List<int> Ids = context.Record
            .Where(rec => rec.Date >= date && rec.Date < nextDay)
            .Select(rec => rec.Id)
            .ToList();

Only in this case indexes

edited Sep 16 '21 at 16:07

answered Sep 16 '21 at 15:43

Svyatoslav Danyliv

21,911
3
16
32

This only works if the column is nullable what if I want it not nullable – Khaled Ayman Sep 16 '21 at 15:59
Sorry, removed not needed `.Date` suffix. – Svyatoslav Danyliv Sep 16 '21 at 16:07

Entity Framework refusing Date comparison The specified type member 'Date' is not supported in LINQ to Entities

1 Answers1