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.
我注意到甚至比使用GroupBy的更简单的LINQ查询也被转换为与组密钥一样多的SQL查询。我没有找到任何解释为什么会发生这种情况或我如何避免它。
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(),
但是，如果我把整个表带到内存中，比如调用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.
只发出一个select，检索所有行，然后LINQ在内存中执行分组。
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.
我发现这种行为相当混乱和容易出错，因为我经常发现自己在不同的语句中编写复杂的查询，我必须小心，在执行GroupBy之前调用AsEnumerable或ToList，否则我的性能会降低。更糟糕的是，它迫使我完成LINQ to SQL查询并继续使用LINQ to Objects。
I've tested this both using LINQ to Entities and LINQ to SQL (through LINQPad), the DBMS being SQL Server.
我使用LINQ to Entities和LINQ to SQL（通过LINQPad）测试了这个，DBMS是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?
我错过了什么吗？这是设计还是以任何方式编写LINQ查询的方式，使用SQL的GROUP BY而不是生成多个单独的查询？
1 个解决方案
#1
2
You need to change your select statement so it is more SQL friendly.
您需要更改您的select语句，以便它更友好。
change: select g
改变：选择g
to something like this:
这样的事情：
select new
{
g.Key,
Count = g.Count(),
};
#1
2
You need to change your select statement so it is more SQL friendly.
您需要更改您的select语句，以便它更友好。
change: select g
改变：选择g
to something like this:
这样的事情：
select new
{
g.Key,
Count = g.Count(),
};