LINQ to SQL – GROUP BY Date (Weekly, Monthly, Quaterly, Yearly)

I thought I’d share a bit of LINQ to SQL (actually LINQ to Entities) knowledge I just acquired while developing a filtered report. It took me some time to figure this out so I hope this saves you a lot of time dealing with GROUP BY dates. I apologize ahead of time for the VB syntax… I had no choice.

GROUP BY Week

Private Function GetISOWeek(day As DateTime) As Integer
        Return System.Globalization.CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(day, System.Globalization.CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Sunday)
End Function

Dim report As New List(Of YourCustomClass) = (From p In pFilter
           Group p By WeekYear = New With {Key .week = GetISOWeek(p.CreatedOn), Key .year = p.CreatedOn.Year} Into Group
           Select New YourCustomClass() With {
               .Name = "Week " + GetISOWeek(Group.ToList.Item(0).CreatedOn).ToString(),
               .Count = Group.Count
           }).ToList()

This will display data like so:

Week 1  5326
Week 2  6598
Week 3  9845
Week 4  6523

GROUP BY Month

Dim report As New List(Of YourCustomClass) = (From p In pFilter
           Group p By MonthYear = New With {Key .month = p.CreatedOn.Month, Key .year = p.CreatedOn.Year} Into Group
           Select New YourCustomClass() With {
               .Name = MonthName(Group.ToList.Item(0).CreatedOn.Month, True) + " " + Group.ToList.Item(0).CreatedOn.Year.ToString(),
               .Count = Group.Count
           }).ToList()

This will display data like so:

Jan 2014  5326
Feb 2014  6598
Mar 2014  9845
Apr 2014  6523

GROUP BY Quarter

Private Function GetQuarter(ByVal month As Integer) As Integer
        Select Case month
            Case 1, 2, 3
                Return 1
            Case 4, 5, 6
                Return 2
            Case 7, 8, 9
                Return 3
            Case Else
                Return 4
        End Select
End Function

Dim report As New List(Of YourCustomClass) = (From p In pFilter
           Group p By MonthYear = New With {Key .month = GetQuarter(p.CreatedOn.Month), Key .year = p.CreatedOn.Year} Into Group
           Select New YourCustomClass() With {
               .Name = "Q" + GetQuarter(Group.ToList.Item(0).CreatedOn.Month).ToString() + " " + Group.ToList.Item(0).CreatedOn.Year.ToString(),
               .Count = Group.Count
           }).ToList()

This will display data like so:

Q1  5326
Q2  6598
Q3  9845
Q4  6523

GROUP BY Year

Dim report As New List(Of YourCustomClass) = (From p In pFilter
           Group p By p.CreatedOn.Year Into Group
           Select New YourCustomClass() With {
               .Name = Group.ToList.Item(0).CreatedOn.Year.ToString(),
               .Count = Group.Count
           }).ToList()

This will display data like so:

2011  5326
2012  6598
2013  9845
2014  6523

2 Replies to “LINQ to SQL – GROUP BY Date (Weekly, Monthly, Quaterly, Yearly)”

  1. And have you checked what actual SQL queries are being performed against database?

    I am afraid many of your groupings above actually happen on client side, which is very bad when we’re talking about a real-world large database.

    • These groupings are happening server side and I have used them with a real-world database. In my case, I didn’t have access to the stored procedures returning the data so this was the only way.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please Do the Math      
 

*