Trouble understanding the SQL generated from this Entity Framework query

I created an Entity Framework model that contains two tables from the Northwind database to test some of its functionality: Products and CAtegories.

It automatically created an association between Category and Product which is 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 i could 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]

I am curious why the query inner joins to Categories and then left joins to it. The select statement is using the fields from the left joined table. Can someone help me understand the reason for this? If I remove the left join and change the select list to pull from Extent2 I get the same results for this query. In what situation would this not be true?

[Extent3] is a realization of Include(Category) and Include should not impact on result of selection from "main" table Product, so LEFT JOIN (all records from Product and some records from the right table Category).

[Extent2] is really to filter all records by related table Category with name "Beverages", so in this case it is the strong restriction (INNER JOIN)

Why two? :) Because of parsing expression-by-expression and auto generation for every statement (Include, Where)

You'll notice that the query is pulling all columns in the SELECT list from the copy of the Categories table aliased Extent3, but it's checking the CategoryName against the copy aliased Extent2.

In other words, in this scenario EF's query generation is not realizing that you're Include()ing and restricting the query via the same table, so it's blindly using two copies.

Unfortunately, beyond explaining what's going on, my experience with EF is not advanced enough to suggest a solution...

djacobson and igor explain pretty well why this happens. The way I personally use the Entity Framework, I avoid using Include altogether. Depending on what you're planning to do with the data, you could do something like this:

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

... which, at least in EF 4.0, will produce just a single inner join. Entity Framework is smart enough to make it so that the product's Category property is populated with the category that came back from the database with it.

Of course, it's very likely that SQL Server optimizes things away so this won't actually gain you anything.

(Not directly an answer to your question if the queries are the same, but the comment field is too restricting for this)

If you leave out the .Include(), doesn't it load it anyway (because of the where)? Generally 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();