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

Answer 2

Not direct answer to your question, but an attempt to point you in the right direction after reading your comments for the other answers:

You have everything you need to defend some ORM ussage (incl. EF) - that's all you said about the quantity and quality of the SPs. Any approach will have problems, including pure sql, if that sql is not well written or hard to maintain.

So, if some ORM (EF, etc.) sometimes produces non-efficient code, and this really causes performance problems, this becomes "requirement", and needs to be solved, even using SP.

So, look at your problems from business perspective - you have bad structured and hard to maintain bunch of stored procedures. Probably most of your team is C# and not SQL developers.

Using ORM will increase the maintainability of the code base, as well as will allow for better usage of all team members expertise in C#.

Bad SQL code, produced by ORM on some specific occasions is hardly "no-go" for using the technology, unless proven that it'll create more problems than solving existing ones.