Getting the DayOfWeek in Linq to Entities

Entity Framework has several pros and cons. The pros of strongly typed objects and shorter code compared to the equivalent SQL kept me from going back to using SqlCommand directly. However, I met another cons of Entity Framework today — that is the lack of support for DateTime.DayOfWeek.

I have a table [Booking] with a column [StartDateTime]. Using the following code results in an error.

var bookings = from b in this.db.Bookings
               where b.StartDateTime.DayOfWeek == DayOfWeek.Monday
               select b;

The yellow screen of death says

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

The solution I found on MSDN forums is to use SqlFunctions.DatePart. However, it is a suboptimal solution, as I’ll be effectively tying myself to using MS SQL only. Even worse is the fact that the return value of the weekday (dw) date part depends on the value that is set by SET DATEFIRST. To get a value regardless of the DATEFIRST settings, I’ll have to use (@@DATEFIRST + DATEPART(DW, @SomeDate)) % 7, which is a statement that cannot be translated to Linq to Entities.

Luckily, Curt’s answer at StackOverflow inspired me. Instead of using DatePart, I can count the number of days from a base date which I know the day of week. Getting the remainder after dividing by 7 would then give me the day of week. So the final code looks like

DateTime firstSunday = new DateTime(1753, 1, 7);
var bookings = from b in this.db.Bookings
               where EntityFunctions.DiffDays(firstSunday, b.StartDateTime) % 7 == 1
               select b;

I have chosen the year 1753 because that is the earliest that the datetime datatype in MS SQL supports, and is way earlier than any dates that I will be using.