How to use Date function in 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?

Answer 1

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;

Answer 2

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