Why does LINQ to SQL translate GroupBy into multiple queries

Question

I've noticed than even my simpler LINQ queries using GroupBy get translated into as many SQL queries as group keys. I haven't found any explanation as to why this happens or how I can avoid it.

For instance, the query:

from p in People group p by p.Name into g select g

gets translated into as many selects as different values for the column Name, just like this one:

-- Region Parameters
DECLARE @x1 VarChar(20) SET @x1 = 'John'
-- EndRegion
SELECT [t0].[Name], [t0].[SurName]
FROM [People] AS [t0]
WHERE ((@x1 IS NULL) AND ([t0].[Name] IS NULL)) 
     OR ((@x1 IS NOT NULL) AND ([t0].[Name] IS NOT NULL) AND (@x1 = [t0].[Name]))
GO

However, if I bring the whole table to memory, such as calling AsEnumerable(),

from p in People.AsEnumerable() group p by p.Name into g select g

just a single select is issued, retrieving all the rows and then LINQ performs the grouping in memory.

I find this behavior rather confusing and error-prone since I often find myself composing complex queries in different statements and I must be careful enough to call AsEnumerable or ToList before performing a GroupBy or my performance gets degraded. Even worse, it forces me to finish my LINQ to SQL query and continue with LINQ to Objects.

I've tested this both using LINQ to Entities and LINQ to SQL (through LINQPad), the DBMS being SQL Server.

Am I missing something? Is this by design or is there any way to write the LINQ query in such a way that SQL's GROUP BY is used instead of multiple individual queries being generated?

Answer 1

You need to change your select statement so it is more SQL friendly.

change: select g

to something like this:

select new
{
  g.Key,
  Count = g.Count(),
};