GroupBy in LINQ turns into n+1 queries

I am doing a group by in a LINQ statement and LightSpeed is turning it into n+1 queries:

var pp = UnitOfWork.BlogPosts.GroupBy(p => new { p.PublishedDate.Month, p.PublishedDate.Year}).Select(g => new
  {
    Count = g.Count(),
    Year = g.Key.Year,
    Month = g.Key.Month
  }).ToList();

This is the good old get a count of how many blog posts were published each month. After I wrote it I checked in SQL profiler for what was happening and it is executing one SQL query with a group by to get the months/years and then for each result it executes another query with the month/year as arguments.

Right now I can't use this because in a couple of years this one LINQ query will be making dozens of database queries.

Yes this is the current behavior of our LINQ grouping queries. We first issue the grouped query to find what the possible grouping keys will be and then we need to issue subsequent queries to populate those sets with entities to handle any further operations over those sets. We do already have a backlog item open to review this and see if we can translate it into a single query where you are then directly projecting the output (as your query above does) as an optimization. Its not likely we will be doing this in the short term however.

Thanks!

Jeremy

Ok. I'm only doing this in one place and there are other bugs you already have that are more important to me so I'll rewrite it as a stored procedure.