由相同的 Linq 和 Lambda 表达式生成的两个不同的 SQL 语句

Question

这是左连接的 Linq -

var Records = from cats in Context.Categories
         join prods in Context.Products on cats.Id equals prods.Category_Id into Cps
         from results in Cps.DefaultIfEmpty()
         select new { CatName = cats.Name, ProdName = results.Name }; 

这是相同的 Lambda 表达式 -

var Records = Context.Categories.GroupJoin(Context.Products, c => c.Id, p => p.Category_Id, (c, p) => new { CatName = c.Name, Prods = p });     

Linq 使用 SQL -

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name], 
[Extent2].[Name] AS [Name1]
FROM  [dbo].[Categories] AS [Extent1]
LEFT OUTER JOIN [dbo].[Products] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Category_Id]

并且 Lambda 使用以下 SQL -

 SELECT 
 [Project1].[Id] AS [Id], 
 [Project1].[Name] AS [Name], 
 [Project1].[C1] AS [C1], 
 [Project1].[Category_Id] AS [Category_Id], 
 [Project1].[Description] AS [Description], 
 [Project1].[Id1] AS [Id1], 
 [Project1].[Name1] AS [Name1], 
 [Project1].[Price] AS [Price]
 FROM ( SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name], 
[Extent2].[Category_Id] AS [Category_Id], 
[Extent2].[Description] AS [Description], 
[Extent2].[Id] AS [Id1], 
[Extent2].[Name] AS [Name1], 
[Extent2].[Price] AS [Price], 
CASE WHEN ([Extent2].[Category_Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
FROM  [dbo].[Categories] AS [Extent1]
LEFT OUTER JOIN [dbo].[Products] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Category_Id])  AS [Project1]
ORDER BY [Project1].[Id] ASC, [Project1].[C1] ASC

我的问题是为什么相同的语句返回不同的 SQL？

Answer 1

这根本不是同一个查询 - 当然，您在 lambda 表达式版本中有 GroupJoin - 但您没有对应于的 SelectMany

from results in Cps.DefaultIfEmpty()

：由于引入了透明标识符，计算出精确的查询翻译有点复杂，但我确信这就是区别。

Answer 2

除了 Jon 的回复之外，您还有：

var Records = Context.Categories.GroupJoin(Context.Products, c => c.Id, p => p.Category_Id, (c, p) => new { CatName = c.Name, Prods = p });

其中有 Prods = p （提取整个产品记录），其中 Linq 查询有 ProdName = results.Name （仅提取名称字符串分组结果）。这是相当多的差异。

尝试将您的 Linq 输入 LinqPad 并在结果窗格中查看生成的 Lamba 的外观。