The problem with understanding SQL generated from this Entity Framework query

Question

I created an Entity Framework model containing two tables from the Northwind database to test some of its functions: products and CAtegories.

He automatically created a link between the category and the product, which ranges from 0..1 to *.

I wrote this simple query:

var beverages = from p in db.Products.Include("Category")
                where p.Category.CategoryName == "Beverages"
                select p;

var beverageList = beverages.ToList();

I ran SQL Profiler and ran the code so that I can see the SQL that it generates, and this is what it generated:

SELECT 
    [Extent1].[ProductID] AS [ProductID], 
    [Extent1].[ProductName] AS [ProductName], 
    [Extent1].[SupplierID] AS [SupplierID], 
    [Extent1].[QuantityPerUnit] AS [QuantityPerUnit], 
    [Extent1].[UnitPrice] AS [UnitPrice], 
    [Extent1].[UnitsInStock] AS [UnitsInStock], 
    [Extent1].[UnitsOnOrder] AS [UnitsOnOrder], 
    [Extent1].[ReorderLevel] AS [ReorderLevel], 
    [Extent1].[Discontinued] AS [Discontinued], 
    [Extent3].[CategoryID] AS [CategoryID], 
    [Extent3].[CategoryName] AS [CategoryName], 
    [Extent3].[Description] AS [Description], 
    [Extent3].[Picture] AS [Picture]
FROM   [dbo].[Products] AS [Extent1]
    INNER JOIN [dbo].[Categories] AS [Extent2] 
        ON [Extent1].[CategoryID] = [Extent2].CategoryID]
    LEFT OUTER JOIN [dbo].[Categories] AS [Extent3] 
        ON [Extent1].[CategoryID] = [Extent3].[CategoryID]
WHERE N'Beverages' = [Extent2].[CategoryName]

, . select . - ? , Extent2, . ?

Answer 1

[Extent3] Include(Category), Include "" Product, LEFT JOIN ( Product Category).

[Extent2] Category "Beverages", (INNER JOIN)

?:) - (Include, Where)

Answer 2

, SELECT aliased Extent3, Extent2.

, EF , Include() ing , .

, , , EF , ...

Answer 3

djacobson igor , . Entity Framework, Include. , , - :

var beverages = from p in db.Products
                select new {p, p.Category} into pc
                where pc.Category.CategoryName == "Beverages"
                select pc;                    
return beverages.ToList().Select(pc => pc.p);

... , , EF 4.0, . Entity Framework , , , .

, , SQL Server , .

Answer 4

( , , )

If you leave .Include(), don’t download it anyway (due to where)? As a rule, it makes more sense to me to use projections instead of Include():

var beverages = from p in db.Products.Include("Category")
                where p.Category.CategoryName == "Beverages"
                select new { Product = p, Category = p.Category };

var beverageList = beverages.ToList();