Include和Where谓词导致左连接而不是内连接 [英] Include and Where predicate cause left join instead of inner join

问题描述

使用以下表格结构（移除了外部列）

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
)

我使用以下Linq-to-Entities查询：

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

生成（来自SQL Profiler）：

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%')

为什么在第3个查询中在 Events 表中再次生成一个 LEFT OUTER JOIN 当查询产生正确的结果时，似乎很奇怪，为什么EF / LINQ在 SELECT [Extent2] >和 WHERE 子句，为什么它是一个 LEFT OUTER JOIN ？

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?

我使用的是Visual Studio 2010 sp1 .NET 4，我正在连接到Sql Server 2008 Express。

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

推荐答案

左侧连接将确保在捐赠指向不存在的事件的情况下，Donations表中不会丢失任何行。他们不希望包含关键字具有导致原始表格中丢失行的副作用，因此必须使用左侧连接来进行安全。

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.

考虑包括表两次这可能只是EF的限制。您在查询中提及它两次，并不足以做优化。

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.

我不得不说，如果要优化SQL，那么写SQL，不要打扰EF。您正在做的可以与反编译C＃进行比较，并询问为什么汇编器没有一定的优化。如果你使用EF，那么闭上眼睛看看它产生的SQL： - ）

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

这篇关于Include和Where谓词导致左连接而不是内连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！