为什么LINQ to SQL的转换成GROUPBY多个查询 [英] Why does LINQ to SQL translate GroupBy into multiple queries

问题描述

我已经注意到了，甚至比我的更简单的LINQ查询中使用GROUPBY得到翻译成尽可能多的SQL查询，组密钥。我还没有发现任何解释，为什么出现这种情况或如何避免它

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.

例如，查询：

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

不过，如果我把整个表到内存中，如调用 AsEnumerable（），

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

只是一个单一的选择发出后，检索所有行和然后LINQ执行在存储器中的分组。

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

我觉得这种行为相当混乱，而且容易出错，因为我经常发现自己作曲的复杂查询在不同的语句，我必须足够小心，叫 AsEnumerable 或了ToList 在执行之前 GROUPBY 或我的表现得到降低。更糟的是，它迫使我完成我的 LINQ到SQL 查询并继续 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.

我测试过这两者使用 LINQ到实体和的LINQ to SQL （通过 LINQPad ），数据库管理系统是 SQL服务器。

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

我缺少的东西吗？设计是这样的，或有什么办法来写LINQ查询以这样一种方式，SQL的 GROUP BY 是用来代替正在生成多个单独的查询？

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?

推荐答案

您需要改变你的选择语句，以便它更SQL友好。

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

变化：选择G以

要这样的：

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

这篇关于为什么LINQ to SQL的转换成GROUPBY多个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！