Two different SQL statements generated by the same Linq and Lambda expression

Question

This is Linq for Left Join -

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 }; 

This is a Lambda expression for the same -

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

Linq uses 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]

And Lambda uses the following 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

My question is: why does the statement return another SQL?

Answer 1

This is not the same query at all - you have a GroupJoinlambda in the version of the expression, of course - but you don't have SelectManyone that matches:

from results in Cps.DefaultIfEmpty()

It complicates the development of accurate translation of queries a bit due to the entered transparent identifiers, but I'm sure the difference.

Answer 2

In addition to John's answer, you have:

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 .