How to use Date function in LINQ to entities?

c# entity-framework linq linq-to-entities

Question

My method should return a list of user Notes so I did it like this:

  var saturday = DayOfWeek.Saturday;
  var query = from note in userNotes
  where note.NoteDate > lastMonth && note.NoteDate.DayOfWeek != saturday
        select note;

But i get this error:

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

Any ideas of how can I compare day of week using linq?

1
5
9/18/2013 8:43:48 AM

Accepted Answer

Use SqlFunctions.DatePart static method. It will be transformed into DATEPART TSQL function call.

var saturday = (int)DayOfWeek.Saturday;
var query = from note in userNotes
            where note.NoteDate > lastMonth && SqlFunctions.DatePart("dw", note.NoteDate) != saturday
            select note;
16
9/17/2013 7:12:50 PM

Popular Answer

As I'm using Oracle, I couldn't use SqlFunctions class. Eventually I found a simple workaround for this problem:

Typically when you attempt to use a property that LINQ doesn't natively support, you'll need to create a concrete implementation of the collection before applying your LINQ constraints.

You can do this using the ToList() or AsEnumerable() methods prior to your Where clause as seen below :

 //Using the ToList() method
IEnumerable<CalendarDate> LessonDates = db.CalendarDates.ToList().Where(cd => cd.Date.DayOfWeek == DayOfWeek.Friday);

//Using the AsEnumerable() method
IEnumerable<CalendarDate> LessonDates = db.CalendarDates.AsEnumerable().Where(cd => cd.Date.DayOfWeek == DayOfWeek.Friday);

SOURCE: link



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow