Geek asks and answers

EF how to filter data by date

I use EF 4, I have a property DateTimeStartin my entities with a date in this format 16/08/2012 08:14:40, I would like to query with EF and find all entities within the date 16/08/2012 only. Using this code below code, I get this error

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

my code

 DateTime dateTimeNow = DateTime.UtcNow;
        DateTime dateNow = dateTimeNow.Date;
        return db.EventCustoms.Where(x => x.DataTimeStart.Date <= dateNow)
            .Select(y => new { y.EventId, y.EventTitle, y.DataTimeStart });
+5
source share
3 answers
DateTime dateTimeNow = DateTime.UtcNow;
DateTime dateTomorrow = dateTimeNow.Date.AddDays(1);
return db.EventCustoms.Where(x => x.DataTimeStart < dateTomorrow) 
            .Select(y => new { y.EventId, y.EventTitle, y.DataTimeStart });

[Edit] @GibboK To clarify a bit:

Entity Framework cannot translate the Date property of a DateTime object on the database side.

Your options:

(1) (As stated above) Rethink your query and try a solution that does not require a function call on the database side for each row in your table .... which is good for query performance.

(2), , EntityFunctions, ( TruncateTime), EF .

.

return db.EventCustoms
    .Where(x => EntityFunctions.TruncateTime(x.DataTimeStart) <= dateNow)
+5
DateTime dateTimeNow = DateTime.UtcNow;
        DateTime dateNow = dateTimeNow.Date;
        return db.EventCustoms.Where(
             x => EntityFunctions.DiffDays(x.DataTimeStart, dateNow) >= 0)
            .Select(y => new { y.EventId, y.EventTitle, y.DataTimeStart });
+3

In EF 6:

using System.Data.Entity;
...
db.EventCustoms.Where(x => 
 DbFunctions.TruncateTime(x.DataTimeStart) <= DbFunctions.TruncateTime(dateNow))
0
source

More articles:


All Articles