c# - Include 和 Where 谓词导致左连接而不是内连接

Question

具有以下表结构(删除了无关的列)

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 查询:

// 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 事件探查器):

-- 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 和 WHERE 子句中重复使用 [Extent2]，为什么它是 LEFT OUTER JOIN？

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

Answer 1

在捐赠指向不存在的事件的情况下，左连接将确保捐赠表中没有遗漏任何行。他们不希望 Include 关键字产生导致原始表中的行丢失的副作用，因此为了安全起见，他们必须使用左联接。

关于两次包含表格，这可能只是 EF 的限制。您在查询中提到它两次，它不够智能，无法进行优化。

我不得不说，如果你想优化SQL然后写SQL，不要费心用EF。您正在做的事情可以比作反编译 C# 并询问为什么汇编程序没有进行某种优化。如果您使用 EF，那么请不要看它生成的 SQL :-)