EF how to filter data by date

Question

I' using EF 4, I have a property DateTimeStart in my entities with datein this format 16/08/2012 08:14:40, I would like query with EF and find all the entities within the date 16/08/2012 only. Using this code below code I receive 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 });

Upstart

What datatype is the DataTimeStart? – Lappet

Answer 1

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 elaborate a bit:

Entity Framework cannot translate the Date property on your DateTime object at the database side.

Your options are:

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

(2) or if that isn't possible, you can make use of the EntityFunctions class, which exposes methods (such as TruncateTime) that can be translated by EF into the appropriate native function for the underlying data source.

e.g.

return db.EventCustoms
    .Where(x => EntityFunctions.TruncateTime(x.DataTimeStart) <= dateNow)

Festivity

While implementing this answer today, I discovered that System.Data.Entity.Core.Objects.EntityFunctions is deprecated and should be replaced by System.Data.Entity.DbFunctions. The same TruncateTime() method exists in the new namespace/class, so simply changing EntityFunctions to DbFunctions and adding the correct using statement should work well for future users of this answer. – Northern

But be aware, in what SQL statement this will be translated. It is something like ((convert (datetime2, convert(varchar(255), [Filter1].[DateTimeStart], 102), 102)) <= '2017-01-13 00:00:00'- so I think I will go with the dateTomorrow version, as this will not need any conversion in the DB on row level. – Nephoscope

Answer 2

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

Metempsychosis

hi Amiram Korach, unfortunately does not work, could you please have a look and let me know? many thanks for your time – Upstart

Sorry my mistake. It should be ">=". This method will give you positive number if the first value is smaller than the second. – Metempsychosis

Answer 3

In EF 6 :

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

Abnormality

Answer 4

it is not a proper solution but it will help you

DateTime startOfDay = new DateTime(invoiceDate.Value.Year, invoiceDate.Value.Month, invoiceDate.Value.Day, 0, 0, 0);
DateTime endOfDay = new DateTime(invoiceDate.Value.Year, invoiceDate.Value.Month, invoiceDate.Value.Day, 23, 59, 59, 999);
predicate.And(x => x.InvoiceDate >= startOfDay && x.InvoiceDate<= endOfDay);

Warring