Group By

LINQ-to-SQL Grouping By Week

Wednesday, May 13th, 2009 | Stuff | 3 Comments

I came across the problem of requiring to group items from my SQL database by week yesterday.  After a quick Google search, I found a few other people were having the same problem.  Although I must admit this was not hard, it might not seem immediately obvious to everyone.

The following code example will group items from a database by week, and is supported by SQL Server:

var byWeek = from t in ctx
             where t.Date >= startWeek && t.Date < endWeek
             group t by t.DateFieldInDb.Date.AddDays(-(int)t.DateDateFieldInDb.DayOfWeek) into tw
             orderby tw.Key
             select new
             {
                 Date = tw.Key,
                 Count = tw.Count()
             };

I should note that this groups weeks starting with Sunday.  To start with Monday, you could try the following code (untested, but should work):

var byWeek = from t in ctx
             where t.Date >= startWeek && t.Date < endWeek
             group t by t.DateFieldInDb.Date.AddDays(-((int)t.DateFieldInDb.DayOfWeek == 0 ? 6 : (int)t.DateFieldInDb.DayOfWeek - 1)) into tw
             orderby tw.Key
             select new
             {
                 Date = tw.Key,
                 Count = tw.Count()
             };

And that’s it, simple as that.  Let me know if you find this useful, or find a better way!

Tags: , ,

Categories