Use Linq query to compare date only with DateTime

问题:

I need to compare just the date only in a Linq query that involves a datetime field. However, the syntax below results in the following error message

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

Does anyone know how to extract just the date out of a datetime field?

var duplicate = from a in _db.AgentProductTraining
                where a.CourseCode == course.CourseCode &&
                a.DateTaken.Date == course.DateTaken.Date &&
                a.SymNumber == symNumber
                select a;

回答1:

It might seem a little roundabout, but you can use the SqlFunctions class' DateDiff method for doing this. Just pass in both values and use "Day" for finding the difference between them in days (which should be 0 if they are on the same day).

Like the following:

from a in _db.AgentProductTraining
                where a.CourseCode == course.CourseCode &&
                SqlFunctions.DateDiff("DAY", a.DateTaken, course.DateTaken) == 0 &&
                a.SymNumber == symNumber
                select a;

回答2:

You can use EntityFunctions.TruncateTime() under the namespace System.Data.Objects

Ex.

db.Orders.Where(i => EntityFunctions.TruncateTime(i.OrderFinishDate) == EntityFunctions.TruncateTime(dtBillDate) && i.Status == "B")

Works like charm.

UPDATE

This function works only when you querying entities through LINQ. Do not use in LINQ-Object.

For EF6 use DbFunctions.TruncateTime() under System.Data.Entity namespace.

回答3:

You can do it like bellow:

var data1 = context.t_quoted_value.Where(x => x.region_name == "Pakistan" 
                        && x.price_date.Value.Year == dt.Year
                        && x.price_date.Value.Month == dt.Month
                        && x.price_date.Value.Day == dt.Day).ToList();

回答4:

you must use System.Data.Entity.DbFunctions.TruncateTime

回答5:

try this

DateTime dt =course.DateTaken.Date;
var duplicate = from a in _db.AgentProductTraining
                where a.CourseCode == course.CourseCode &&
                a.DateTaken == dt &&
                a.SymNumber == symNumber
                select a;

if a.DateTaken contains Time also, then refer these links to modify your date.
The Date property cannot be used in LINQ To Entities.

Compare Dates using LINQ to Entities (Entity Framework)

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

http://forums.asp.net/t/1793337.aspx/1

回答6:

This is how I ended by doing a similar Date search which I had to consider time (Hour and Minutes portion) also

from x in _db.AgentProductTraining
                where 
                       x.CreatedOn.Year == mydacourse.DateTakente.Year
                    && x.CreatedOn.Month == course.DateTaken.Month
                    && x.CreatedOn.Day == course.DateTaken.Day
                    && x.CreatedOn.Hour == course.DateTaken.Hour
                    && x.CreatedOn.Minute == course.DateTaken.Minute
                select x;

回答7:

Take off the .Date If the field is a DateTime it can be compared with ==

var duplicate = from a in _db.AgentProductTraining
                where a.CourseCode == course.CourseCode &&
                a.DateTaken == course.DateTaken &&
                a.SymNumber == symNumber
                select a;