Group by Week of year (Week number) in Linq to SQL

Question

In regular SQL i could do something like

SELECT * From T GROUP BY DATEPART(wk, T.Date)

How can i do that in Linq to SQL ?

The following don't work

From F In DB.T Group R By DatePart(DateInterval.WeekOfYear, F.Date)

Also don't work:

From F In DB.T Group R By (F.Date.DayOfYear / 7)

Answer 1

This works correctly.

from F in DB.T group F by F.Date.DayOfYear / 7;

You were specifying the group by incorrectly. The result of this code be a collection of objects. Each object will have a Key property which will be what you grouped by (in this case the result of F.Date.DayOfYear / 7. Each object will be a collection of objects from T that met the group condition.

Answer 2

Range variable name can be inferred only from a simple or qualified name with no arguments

Answer 3

If you are concerned about the culture you are in the following code will take that into account:

var ci = CultureInfo.CurrentCulture;
var cal = ci.Calendar;
var rule = ci.DateTimeFormat.CalendarWeekRule;
var firstDayOfWeek = ci.DateTimeFormat.FirstDayOfWeek;

var groups = from F in DB.T
             group F by cal.GetWeekOfYear(F, rule, firstDayOfWeek) into R
             select R;

Answer 4

LINQ to SQL does not support the Calendar.WeekOfYear method, but you could potentially create a TSQL function that wraps the call to DatePart. The DayOfYear / 7 trick should work for most cases and is much easier to use. Here's the code I ended up with:

var x = from F in DB.T
        group F by new {Year = F.Date.Year, Week = Math.Floor((decimal)F.Date.DayOfYear / 7)} into FGroup
        orderby FGroup.Key.Year, FGroup.Key.Week
        select new {
            Year = FGroup.Key.Year,
            Week = FGroup.Key.Week,
            Count = FGroup.Count()
        };

Results in something like this:

Year    Week    Count
2004    46      3
2004    47      3
2004    48      3
2004    49      3
2004    50      2
2005    0       1
2005    1       8
2005    2       3
2005    3       1
2005    12      2
2005    13      2