Use Linq query to compare date only with DateTime field

Question

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;

score 15 · Accepted Answer · answered Feb 08 '13 at 18:19

15

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;

answered Feb 08 '13 at 18:19

Corey Adler

15,897
18
66
80

Wouldn't the diff be zero if the dates are on different dates, but are spaced at less than one day, say, `Jan-5, 2013 23:30:00` and `Jan-6, 2013 01:30:00`? – Sergey Kalinichenko Feb 08 '13 at 18:21
Unfortunately this did not work either. Since it's nested in the Linq query, I'm looking into finding out why but not having any luck... – NealR Feb 08 '13 at 18:43
Did it still throw that exception? – Corey Adler Feb 08 '13 at 19:01
System.NotSupportedException: This function can only be invoked from LINQ to Entities. – Usman Khalid Apr 08 '15 at 06:25
@UsmanKhalid That is correct: You can only use this method with a LINQ to Entities call. Judging by the fact that there is an object called `_db` I assume that this question has to do with such a query. – Corey Adler Apr 08 '15 at 14:04

صفي · Answer 2 · 2018-04-27T10:42:27.917

13

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.

edited Apr 27 '18 at 10:42

answered Dec 05 '13 at 09:52

صفي

1,068
2
15
34

Hey @AlexAngas, you can check to verify this is working in EF4. which version are you using? – صفي Jan 17 '14 at 05:21
I used EF5 and got this exception: `System.NotSupportedException: This function can only be invoked from LINQ to Entities. at System.Data.Objects.EntityFunctions.TruncateTime(Nullable`1 dateValue)`. Is there something i'm missing? – Alex Angas Jan 17 '14 at 06:16
@AlexAngas This function works only when you querying entities through LINQ. Do not use in LINQ-Object. – صفي Jan 18 '14 at 08:00
1

Dear down-voter, no offence in down-voting. But please comment what was missing / can be potentially improved, otherwise it spoils the reputation of the question that might be useful to someone in need. – صفي Jan 18 '14 at 08:00
3

EntityFunctions is deprecated in EF6, DbFunctions should be used instead. – VahidNaderi Jul 11 '14 at 11:15

score 6 · Answer 3 · answered Jun 24 '14 at 06:46

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

score 3 · Answer 4 · answered Aug 21 '16 at 19:42

3

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

answered Aug 21 '16 at 19:42

Caner

813
1
12
26

score 2 · Answer 5 · edited May 23 '17 at 12:33

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

ssilas777 · Answer 6 · 2018-04-15T19:43:08.790

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;

score 0 · Answer 7 · answered Sep 08 '22 at 21:47

0

Hey when I was building a query this below worked

                    DateTime date = Convert.ToDateTime(SearchText); 
                    query = query.Where(x => x.Date.Month == date.Month
                                          && x.Date.Day == date.Day
                                          && x.Date.Year == date.Year);

// Let me know if this worked for you as it pulled the date that was searched for me

answered Sep 08 '22 at 21:47

Jamey Geoghagan

9
2

you can just do `x.Date == date.Date` or `x.Date.Equals(date.Date)` – T.S. Sep 14 '22 at 13:23

score 0 · Answer 8 · answered Apr 05 '23 at 10:45

A little bit ugly but it works as expected without using any sql functions:

var sessions = await Db.Set<Sessions>()
    .Where(x => x.Timetable == form.TimetableId)
    .Where(x => x.DateAndTimeOfSession >= form.BookingDate.Date) // <--
    .Where(x => x.DateAndTimeOfSession < form.BookingDate.Date.AddDays(1)) // <--
    .ToListAsync();

score -1 · Answer 9 · answered Apr 11 '15 at 18:51

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;

Use Linq query to compare date only with DateTime field

9 Answers9

Linked