LINQ-to-SQL Grouping By Week

Wednesday, May 13th, 2009 | Stuff

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: , ,

3 Comments to LINQ-to-SQL Grouping By Week

Shishir
December 10, 2009

Really helpful…
I used it in my Application. Earlier I was using SQL Procedure to pull this kind of data, but this seems faster then that…

Thanks a lot.

WADE
July 4, 2010


Pillspot.org. Canadian Health&Care.Best quality drugs.Special Internet Prices.No prescription online pharmacy. No prescription drugs. Order drugs online…

Buy:Cialis Soft Tabs.Maxaman.Cialis Professional.VPXL.Tramadol.Viagra Soft Tabs.Viagra Professional.Super Active ED Pack.Soma.Viagra.Levitra.Propecia.Viagra Super Active+.Cialis Super Active+.Cialis.Zithromax.Viagra Super Force….

Leave a comment

Categories