6

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)
ariel
  • 15,620
  • 12
  • 61
  • 73
  • When you say it doesn't work, what do you mean? Doesn't compile, doesn't return expected results? – sgriffinusa Aug 06 '10 at 21:25
  • doesnt compile.. "Range variable name can be inferred only from a simple or qualified name with no arguments." – ariel Aug 06 '10 at 21:27
  • ok, the answer is "From F In DB.T Group R By WeekOfYear = (F.Date.DayOfYear / 7)" – ariel Aug 06 '10 at 21:59

6 Answers6

8

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
Greg Bray
  • 14,929
  • 12
  • 80
  • 104
  • 2
    Just remember that this will not directly correspond to to the Calendar weeks, as it assumes the year always starts on a Sunday. To match up fully you would need to use the Calendar.WeekOfYear or wrap the DatePart call into a SQL Function – Greg Bray Aug 06 '10 at 22:24
2

You can use the SqlFunctions.DatePart method from the System.Data.Entity.SqlServer namespace.

// Return the week number
From F In DB.T Group R By SqlFunctions.DatePart("week", F.Date)
Fred Fickleberry III
  • 2,439
  • 4
  • 34
  • 50
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.

sgriffinusa
  • 4,203
  • 1
  • 25
  • 26
0

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;
Richard Slater
  • 6,313
  • 4
  • 53
  • 81
  • this dont work-- "Method 'Int32 GetWeekOfYear(System.DateTime, System.Globalization.CalendarWeekRule, System.DayOfWeek)' has no supported translation to SQL." – ariel Aug 06 '10 at 21:45
  • It seems that Calendar.GetWeekOfYear isn't support in Linq2Sql or Linq to Entities; you would have the pull the whole data set out first then use cal.GetWeekOfYear(x, rule, firstDayOfWeek). – Richard Slater Aug 06 '10 at 22:02
  • yes.. but dividing DayOfYear by 7 is enough for my needs. thanks! – ariel Aug 06 '10 at 22:09
0

First you should get the date of the first day in the week.

To get the date of the first day in 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.

So this code in regular SQL :

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

can be done in Linq to SQL like this

T.GroupBy(i => i.Date.StartOfWeek(DayOfWeek.Monday));
hoss77
  • 375
  • 3
  • 9