Linq to Entities group by week number

Question

I am using ASP.NET v4.5 and linq to entities I am trying to group my data by week using the below code

var groupedByWeek = salesOrdersList
    .GroupBy(i => i.DueDate.AddDays(-(int)i.DueDate.DayOfWeek));

However I am getting a "yellow screen of death" with the error:

LINQ to Entities does not recognize the method 'System.DateTime AddDays(Double)' method, and this method cannot be translated into a store expression.

Ideally I would like to put

var groupedByWeek = salesOrdersList.GroupBy(i => i.DueDate.WeekNumber);

But life isn't that easy!

Does anyone know a way of using week numbers with Linq to Entities?

Answer 1

Use SqlFunctions.DatePart() method:

var groupedByWeek = salesOrdersList.GroupBy(i => SqlFunctions.DatePart("week", i.DueDate));

It will add DATEPART sql function call into generated SQL query.