При следующей структуре таблицы (удалены лишние столбцы)
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 -- -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 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%')
Почему в третьем запросе он генерирует LEFT OUTER JOIN
в таблице Events
во второй раз? Хотя запрос дает правильные результаты, это кажется странным, почему EF/LINQ re -не может использовать [Extent2]
в предложении SELECT
и WHERE
, и почему это LEFT OUTER JOIN
?
Я использую Visual Studio 2010 sp1.NET 4 и подключаюсь к Sql Server 2008 Express.