Include and Where predicate cause left join instead of inner join

Question

With the following table structure (extraneous columns removed)

create table [Events]
(
    ID int not null identity,
    Name nvarchar(128) not null,
    constraint PK_Events primary key(ID)
)

create table [Donations]
(
    ID int not null identity,
    EventID int not null,
    Amount decimal(10, 2) not null,

    constraint PK_Donations primary key(ID),
    constraint FK_Donations_Events foreign key(EventID) references [Events](ID) on update no action on delete no action
)

I use the following Linq-to-Entities queries:

// 1
ents.Donations.Where(d => d.Amount > 25.0m && d.Event.Name.Contains("Run")).ToList();

// 2
ents.Donations.Include("Event").Where(d => d.Amount > 25.0m).ToList();

// 3
ents.Donations.Include("Event").Where(d => d.Amount > 25.0m && d.Event.Name.Contains("Run")).ToList();

Produces (from an SQL Profiler):

-- 1
SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[EventID] AS [EventID], 
[Extent1].[Amount] AS [Amount]
FROM  [dbo].[Donations] AS [Extent1]
INNER JOIN [dbo].[Events] AS [Extent2] ON [Extent1].[EventID] = [Extent2].[ID]
WHERE ([Extent1].[Amount] > 25.0) AND ([Extent2].[Name] LIKE N'%Run%')

-- 2
SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[EventID] AS [EventID], 
[Extent1].[Amount] AS [Amount], 
[Extent2].[ID] AS [ID1], 
[Extent2].[Name] AS [Name]
FROM  [dbo].[Donations] AS [Extent1]
INNER JOIN [dbo].[Events] AS [Extent2] ON [Extent1].[EventID] = [Extent2].[ID]
WHERE [Extent1].[Amount] > 25.0

-- 3
SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[EventID] AS [EventID], 
[Extent1].[Amount] AS [Amount], 
[Extent3].[ID] AS [ID1], 
[Extent3].[Name] AS [Name]
FROM   [dbo].[Donations] AS [Extent1]
INNER JOIN [dbo].[Events] AS [Extent2] ON [Extent1].[EventID] = [Extent2].[ID]
LEFT OUTER JOIN [dbo].[Events] AS [Extent3] ON [Extent1].[EventID] = [Extent3].[ID]
WHERE ([Extent1].[Amount] > 25.0) AND ([Extent2].[Name] LIKE N'%Run%')

Why in the 3rd query, does it generate a LEFT OUTER JOIN on the Events table a second time? While the query produces correct results, it seems odd, why cannot EF / LINQ re-use [Extent2] in the SELECT and WHERE clause, and why is it a LEFT OUTER JOIN?

I'm using Visual Studio 2010 sp1 .NET 4 and I am connecting to Sql Server 2008 Express.

Answer 1

The left join would be to ensure no rows are missing from the Donations table in the case that a donation points to an event that does not exist. They don't want the Include keyword to have the side effect of causing rows to be missing from the original table so they must be using a left join for safety.

With regards to including the table twice this is probably just a limitation of EF. You mention it twice in your query and it's not smart enough to do the optimisation.

I have to say that if you want to optimise SQL then write SQL, don't bother with EF. What you are doing could be compared to decompiling C# and asking why the assembler doesn't have a certain optimisation. If you use EF then shut your eyes to what SQL it produces :-)