Include and where the predicate calls left join instead of inner join

Question

In 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 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 third query does it generate a LEFT OUTER JOIN in the Events table a second time? Although the query gives the correct results, it seems strange why EF / LINQ cannot use [Extent2] in the SELECT and WHERE , and why is it a LEFT OUTER JOIN ?

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

Answer 1

The left join will be to ensure that there are no rows in the Donations table in case the donation indicates an event that does not exist. They don’t want the Include keyword to have a side effect that causes no rows in the source table, so they should use the left join for security.

As for including the table twice, this is probably just an EF constraint. You mentioned this twice in your request and are not smart enough to do the optimization.

I have to say that if you want to optimize SQL, then write SQL, do not worry about EF. What you are doing can be compared with C # decompilation and the demand why the assembler does not have a certain optimization. If you use EF, then close your eyes to the fact that it produces SQL :-)

Answer 2

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

You have everything you need to protect against ORM ussage (including EF) - everything you said about the quantity and quality of SP. Any approach will have problems, including pure sql, if that sql is not well written or difficult to maintain.

So, if some kind of ORM (EF, etc.) sometimes produces inefficient code, and it really causes performance problems, it becomes a “requirement”, and it needs to be solved even with SP.

So, look at your problems from a business point of view - you have poorly structured and difficult to maintain a bunch of stored procedures. Most of your team is probably C #, not SQL developers.

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

The poor SQL code generated by ORM in some cases is hardly the “wrong” to use this technology unless it is proven that this will create more problems than solving existing ones.