Two different SQL statement generated by same Linq and Lambda expression

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 Lambda Expression for 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 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 same statement returning different SQL?

---

It's not the same query at all - you've got the GroupJoin in the lambda expression version, sure - but you haven't got the SelectMany that would correspond to:

from results in Cps.DefaultIfEmpty()

It gets a bit complicated working out the exact query translation due to the transparent identifiers introduced, but I'm sure that's the difference.

---

Additionally to Jon's response you have:

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

which has Prods = p (pulling the whole product record) where the Linq query has ProdName = results.Name (pulling just the name string from the grouped results). That's a fair number of differences.

Try entering your Linq into LinqPad and seeing what the resultant Lamba looks like in the results pane.