Linq to Entities Weekly

Question

I am using ASP.NET v4.5 and linq for objects. I am trying to group my data by weeks using the code below.

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

However, I get a "yellow screen of death" with the error:

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

Ideally, I would like to put

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

But life is not so simple!

Does anyone know a way to use week numbers with Linq for Entities?

Answer 1

Use the SqlFunctions.DatePart() method:

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

It will add a call to the DATEPART sql function in the generated SQL query.

Answer 2

Or you can get the date of the first day of the week, then the group, by that date.

Get the date of the first day of the week. you can use this code:

 public static class DateTimeExtensions { public static DateTime StartOfWeek(this DateTime dt, DayOfWeek startOfWeek) { int diff = dt.DayOfWeek - startOfWeek; if (diff < 0) { diff += 7; } return dt.AddDays(-1 * diff).Date; } } 

then you can group by the first date of the week as follows:

 var groupedByWeek = salesOrdersList.GroupBy(i => i.DueDate.StartOfWeek(DayOfWeek.Monday));