Entity Framework refusing Date comparison The specified type member 'Date' is not supporte-CodePudding

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

CodePudding user response：

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