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?
This question is related to
linq
entity-framework-4
linq-to-entities
EntityFunctions
is obsolete. Consider using DbFunctions
instead.
var eventsCustom = eventCustomRepository.FindAllEventsCustomByUniqueStudentReference(userDevice.UniqueStudentReference)
.Where(x => DbFunctions.TruncateTime(x.DateTimeStart) == currentDate.Date);
Use the bellow code for using EF6:
(DbFunctions.TruncateTime(x.User.LeaveDate.Value)
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));
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.
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.
You should now use DbFunctions.TruncateTime
var anyCalls = _db.CallLogs.Where(r => DbFunctions.TruncateTime(r.DateTime) == callDateTime.Date).ToList();
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);
Another solution could be:
var eventsCustom = eventCustomRepository.FindAllEventsCustomByUniqueStudentReference(userDevice.UniqueStudentReference).AsEnumerable()
.Where(x => x.DateTimeStart.Date == currentDate.Date).AsQueryable();
Simplified:
DateTime time = System.DateTime.Now;
ModelName m = context.TableName.Where(x=> DbFunctions.TruncateTime(x.Date) == time.Date)).FirstOrDefault();
Source: Stackoverflow.com