LINQ-to-SQL - Group By Week in Date object

Question

I am a writing a query in LINQ-to-SQL, and in this query is a Date column. I want to group the results by the 'Week'. How would I do this in LINQ-to-SQL?

Thanks

This might be what your after; http://stackoverflow.com/questions/1059737/group-by-weeks-in-linq-to-entities — WooHoo, Oct 19 '11 at 14:18
I think the simplest solution is to call the datepart from an UDF — Adrian Iftode, Oct 19 '11 at 14:31
@AdrianIftode is right, especially when you would need the week number according to iso 8601. Remember to group by year too. — Gert Arnold, Oct 19 '11 at 20:58

score 3 · Accepted Answer · answered Oct 19 '11 at 16:33

Assuming Gregorian calendar, "first day" week rule and that the first day of the week is Monday.

First day week rule:

Indicates that the first week of the year starts on the first day of the year and ends before the following designated first day of the week

int firstDayOfWeek = (int)DayOfWeek.Monday;
var q = 
    from u in TblUsers
    let date = u.CreateDate.Value
    let num = date.DayOfYear - 1
    let num2 = ((int)date.DayOfWeek) - (num % 7)
    let num3 = ((num2 - firstDayOfWeek) + 14) % 7
    let week = (((num + num3) / 7) + 1)
    group u by week into g
    select new 
    {
        Week = g.Key,
        Count = g.Count ()
    };

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

Hope this helps:

    var d1 = new DateTime(1990, 1, 1);
    var d2 = new DateTime(1990, 1, 2);
    var d3 = new DateTime(1990, 1, 11);
    var d4 = new DateTime(1990, 1, 12);

    var records = new[] { 
        new { date = d1, freq = 1 }, 
        new { date = d2, freq = 2 }, 
        new { date = d3, freq = 3 }, 
        new { date = d4, freq = 4 } 
    };

    //ticks * WeekDays * DayHours * HourMins * MinSecs
    var weekTicks = (Int64)10000000 * 7 * 24 * 60 * 60;   
    var res = records.GroupBy(x => x.date.Ticks / weekTicks)
             .Select(x => new { week=x.Key ,sum=x.Sum(y=>y.freq)});

And this is the result:

[0] = { week = 99085, sum = 3 }

[1] = { week = 99086, sum = 7 }

LINQ-to-SQL - Group By Week in Date object

2 Answers2