CODEWITHSUNDEEP
asp.netsqllinqsql-server-2008
Tom Gullen
Tom Gullen

Reputation: 61755

Help with LINQ group by

I'm trying to replicate this SQL query

SELECT     COUNT(1) AS Records, MONTH(date) AS Month, YEAR(date) AS YEAR
FROM         tblBlogEntries
GROUP BY MONTH(date), YEAR(date)
ORDER BY year DESC, month DESC

Into LINQ, I've gotten as far as this:

var q = from Rec in db.tblBlogEntries
        group Rec by new { Rec.date.Value.Year, Rec.date.Value.Month } into G
        select new {
            Month = G.Key.Month,
            Year = G.Key.Year
        };

But I'm still new to LINQ and am a little lost. Thanks for any help!

Upvotes: 0

Views: 304

Answers (1)

Jon Skeet
Jon Skeet

Reputation: 1501626

Try this instead:

var query = from rec in db.tblBlogEntries 
            group rec by new { rec.date.Value.Month,
                               rec.date.Value.Year } into g
            orderby g.Key.Year descending, g.Key.Month descending
            select new {
                Count = g.Count(),
                g.Key.Year,
                g.Key.Month
            };

Upvotes: 3

Related Questions