Trouble understanding the SQL generated from this Entity Framework query

Question

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?

Answer 1

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

Answer 2

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...