EF6 несколько попаданий в дБ с выражениями linq, переданными как параметры
У меня есть запрос (пользователи фильтруются по некоторым параметрам), который мне интересен, чтобы оставить внешнее объединение с другим запросом (агрегации пользователей, которые совершили покупки) и заполнить нулями пользователей, которых нет в результате второго запроса.
Я использую статические деревья выражений, которые выполняются динамически, когда весь запрос преобразуется в матрицу, например:
public static Func<ApplicationUser, bool> IsPassedDisclaimerLambda
{
get { return u => u.Roles.Any(ro => ro.Role.Name
== SystemConstants.Roles.MobileUser_PassedDisclaimer); }
}
Когда я пишу лямбда-выражение в строке, как
var users = Db.Users.Where(u => u.Roles.Any(ro => ro.Role.Name
== SystemConstants.Roles.MobileUser_PassedDisclaimer));
во время материализации запрос выполняется как одно попадание в БД.
и когда я использую
var users = Db.Users.Where(ApplicationUser.IsPassedDisclaimerLambda);
запрос работает (после того как я поставил MultipleActiveResultSets=True в строке подключения), но в журнале я вижу попадание в БД для каждого пользователя, а не простое внешнее соединение слева, чего я и ожидал.
Как будто EF материализовал первый запрос, а затем отправляет в БД каждого пользователя для создания группировки.
Почему это может быть и что можно сделать?
Код:
public ActionResult UsersSummary(ReportFilter filter)
{
var purchases = FilterPurchases(filter);
//users which made purchases - works "fine" - creates a 210-line query, execution time 410 ms for some 50k rows
var rows = purchases.Where(pu => pu.Valid).GroupBy(p => p.Buyer.UserName).Select(gr =>
new UserSummary
{
Name = gr.Key,
PurchasesCount = gr.Count(),
PurchasesSum = gr.AsQueryable().Sum(Purchase.CurrentPurchaseSumLambda) ?? 0,
ReturnSumPending = gr.AsQueryable().Sum(Purchase.CurrentUnpaidSumLambda) ?? 0,
ReturnSumReceived = gr.AsQueryable().Sum(Purchase.CurrentPaidSumLambda) ?? 0,
TotalReturnSum = gr.AsQueryable().Sum(Purchase.TotalReturnSumLambda) ?? 0
});
//var users = Db.Users.Where(u => u.Roles.Any(ro => ro.Role.Name == SystemConstants.Roles.MobileUser_PassedDisclaimer)); //1 hit
var users = Db.Users.Where(ApplicationUser.IsPassedDisclaimerLambda); //multiple hits
//left outer join
var total = users.GroupJoin(rows, u => u.UserName, r => r.Name, (u, g) => g
.Select(r => new UserSummary
{
Name = u.UserName,
PurchasesCount = r.PurchasesCount,
PurchasesSum = r.PurchasesSum,
ReturnSumPending = r.ReturnSumPending,
ReturnSumReceived = r.ReturnSumReceived,
TotalReturnSum = r.TotalReturnSum
})
.DefaultIfEmpty(new UserSummary
{
Name = u.UserName,
PurchasesCount = 0,
PurchasesSum = 0,
ReturnSumPending = 0,
ReturnSumReceived = 0,
TotalReturnSum = 0
}))
.SelectMany(gr => gr);
var ret = total.OrderBy(r => r.Name).ToList();
return View(ret);
}
с
var users = Db.Users.Where(u => u.Roles.Any(ro => ro.Role.Name == SystemConstants.Roles.MobileUser_PassedDisclaimer));
Я получаю 1 большой запрос, который выполняется за 500 мс
Opened connection at 28/04/2014 11:17:11 +03:00
SELECT
[Project20].[C1] AS [C1],
[Project20].[C2] AS [C2],
[Project20].[C3] AS [C3],
[Project20].[C4] AS [C4],
[Project20].[C5] AS [C5],
[Project20].[C6] AS [C6],
[Project20].[C7] AS [C7]
FROM ( SELECT
CASE WHEN ([Project18].[C10] IS NULL) THEN 1 ELSE [Project18].[C10] END AS [C1],
CASE WHEN ([Project18].[C10] IS NULL) THEN [Extent1].[UserName] WHEN ([Project18].[C10] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE [Extent1].[UserName] END AS [C2],
CASE WHEN ([Project18].[C10] IS NULL) THEN 0 ELSE [Project18].[C1] END AS [C3],
CASE WHEN ([Project18].[C10] IS NULL) THEN cast(0 as float(53)) WHEN ([Project18].[C10] IS NULL) THEN CAST(NULL AS float) WHEN ([Project18].[C2] IS NULL) THEN cast(0 as float(53)) ELSE [Project18].[C3] END AS [C4],
CASE WHEN ([Project18].[C10] IS NULL) THEN cast(0 as float(53)) WHEN ([Project18].[C10] IS NULL) THEN CAST(NULL AS float) WHEN ([Project18].[C4] IS NULL) THEN cast(0 as float(53)) ELSE [Project18].[C5] END AS [C5],
CASE WHEN ([Project18].[C10] IS NULL) THEN cast(0 as float(53)) WHEN ([Project18].[C10] IS NULL) THEN CAST(NULL AS float) WHEN ([Project18].[C6] IS NULL) THEN cast(0 as float(53)) ELSE [Project18].[C7] END AS [C6],
CASE WHEN ([Project18].[C10] IS NULL) THEN cast(0 as float(53)) WHEN ([Project18].[C10] IS NULL) THEN CAST(NULL AS float) WHEN ([Project18].[C8] IS NULL) THEN cast(0 as float(53)) ELSE [Project18].[C9] END AS [C7]
FROM [dbo].[AspNetUsers] AS [Extent1]
LEFT OUTER JOIN (SELECT
[Project17].[UserName] AS [UserName],
[Project17].[C1] AS [C1],
[Project17].[C2] AS [C2],
[Project17].[C3] AS [C3],
[Project17].[C4] AS [C4],
[Project17].[C5] AS [C5],
[Project17].[C6] AS [C6],
[Project17].[C7] AS [C7],
[Project17].[C8] AS [C8],
[Project17].[C9] AS [C9],
1 AS [C10]
FROM ( SELECT
[Project16].[UserName] AS [UserName],
[Project16].[C1] AS [C1],
[Project16].[C2] AS [C2],
[Project16].[C3] AS [C3],
[Project16].[C4] AS [C4],
[Project16].[C5] AS [C5],
[Project16].[C6] AS [C6],
[Project16].[C7] AS [C7],
[Project16].[C8] AS [C8],
(SELECT
SUM([Filter22].[A1]) AS [A1]
FROM ( SELECT
(SELECT
SUM([Extent33].[Sum]) AS [A1]
FROM [dbo].[UserPayments] AS [Extent33]
WHERE ([Extent31].[ID] = [Extent33].[Purchase_ID]) OR (([Extent31].[ID] IS NULL) AND ([Extent33].[Purchase_ID] IS NULL))) AS [A1]
FROM [dbo].[Purchases] AS [Extent31]
INNER JOIN [dbo].[AspNetUsers] AS [Extent32] ON ([Extent32].[Discriminator] = N'ApplicationUser') AND ([Extent31].[Buyer_Id] = [Extent32].[Id])
WHERE ((DATEDIFF (minute, @p__linq__0, [Extent31].[PurchaseTime])) >= 0) AND ([Extent31].[Valid] = 1) AND (([Project16].[UserName] = [Extent32].[UserName]) OR (([Project16].[UserName] IS NULL) AND ([Extent32].[UserName] IS NULL)))
) AS [Filter22]) AS [C9]
FROM ( SELECT
[Project15].[UserName] AS [UserName],
[Project15].[C1] AS [C1],
[Project15].[C2] AS [C2],
[Project15].[C3] AS [C3],
[Project15].[C4] AS [C4],
[Project15].[C5] AS [C5],
[Project15].[C6] AS [C6],
[Project15].[C7] AS [C7],
(SELECT
SUM([Filter20].[A1]) AS [A1]
FROM ( SELECT
(SELECT
SUM([Extent30].[Sum]) AS [A1]
FROM [dbo].[UserPayments] AS [Extent30]
WHERE ([Extent28].[ID] = [Extent30].[Purchase_ID]) OR (([Extent28].[ID] IS NULL) AND ([Extent30].[Purchase_ID] IS NULL))) AS [A1]
FROM [dbo].[Purchases] AS [Extent28]
INNER JOIN [dbo].[AspNetUsers] AS [Extent29] ON ([Extent29].[Discriminator] = N'ApplicationUser') AND ([Extent28].[Buyer_Id] = [Extent29].[Id])
WHERE ((DATEDIFF (minute, @p__linq__0, [Extent28].[PurchaseTime])) >= 0) AND ([Extent28].[Valid] = 1) AND (([Project15].[UserName] = [Extent29].[UserName]) OR (([Project15].[UserName] IS NULL) AND ([Extent29].[UserName] IS NULL)))
) AS [Filter20]) AS [C8]
FROM ( SELECT
[Project14].[UserName] AS [UserName],
[Project14].[C1] AS [C1],
[Project14].[C2] AS [C2],
[Project14].[C3] AS [C3],
[Project14].[C4] AS [C4],
[Project14].[C5] AS [C5],
[Project14].[C6] AS [C6],
(SELECT
SUM([Filter18].[A1]) AS [A1]
FROM ( SELECT
(SELECT
SUM([Extent27].[Sum]) AS [A1]
FROM [dbo].[UserPayments] AS [Extent27]
WHERE (([Extent25].[ID] = [Extent27].[Purchase_ID]) OR (([Extent25].[ID] IS NULL) AND ([Extent27].[Purchase_ID] IS NULL))) AND (N'Paid' = [Extent27].[Status])) AS [A1]
FROM [dbo].[Purchases] AS [Extent25]
INNER JOIN [dbo].[AspNetUsers] AS [Extent26] ON ([Extent26].[Discriminator] = N'ApplicationUser') AND ([Extent25].[Buyer_Id] = [Extent26].[Id])
WHERE ((DATEDIFF (minute, @p__linq__0, [Extent25].[PurchaseTime])) >= 0) AND ([Extent25].[Valid] = 1) AND (([Project14].[UserName] = [Extent26].[UserName]) OR (([Project14].[UserName] IS NULL) AND ([Extent26].[UserName] IS NULL)))
) AS [Filter18]) AS [C7]
FROM ( SELECT
[Project13].[UserName] AS [UserName],
[Project13].[C1] AS [C1],
[Project13].[C2] AS [C2],
[Project13].[C3] AS [C3],
[Project13].[C4] AS [C4],
[Project13].[C5] AS [C5],
(SELECT
SUM([Filter16].[A1]) AS [A1]
FROM ( SELECT
(SELECT
SUM([Extent24].[Sum]) AS [A1]
FROM [dbo].[UserPayments] AS [Extent24]
WHERE (([Extent22].[ID] = [Extent24].[Purchase_ID]) OR (([Extent22].[ID] IS NULL) AND ([Extent24].[Purchase_ID] IS NULL))) AND (N'Paid' = [Extent24].[Status])) AS [A1]
FROM [dbo].[Purchases] AS [Extent22]
INNER JOIN [dbo].[AspNetUsers] AS [Extent23] ON ([Extent23].[Discriminator] = N'ApplicationUser') AND ([Extent22].[Buyer_Id] = [Extent23].[Id])
WHERE ((DATEDIFF (minute, @p__linq__0, [Extent22].[PurchaseTime])) >= 0) AND ([Extent22].[Valid] = 1) AND (([Project13].[UserName] = [Extent23].[UserName]) OR (([Project13].[UserName] IS NULL) AND ([Extent23].[UserName] IS NULL)))
) AS [Filter16]) AS [C6]
FROM ( SELECT
[Project12].[UserName] AS [UserName],
[Project12].[C1] AS [C1],
[Project12].[C2] AS [C2],
[Project12].[C3] AS [C3],
[Project12].[C4] AS [C4],
(SELECT
SUM([Filter14].[A1]) AS [A1]
FROM ( SELECT
(SELECT
SUM([Extent21].[Sum]) AS [A1]
FROM [dbo].[UserPayments] AS [Extent21]
WHERE (([Extent19].[ID] = [Extent21].[Purchase_ID]) OR (([Extent19].[ID] IS NULL) AND ([Extent21].[Purchase_ID] IS NULL))) AND ( NOT ((N'Paid' = [Extent21].[Status]) AND ([Extent21].[Status] IS NOT NULL)))) AS [A1]
FROM [dbo].[Purchases] AS [Extent19]
INNER JOIN [dbo].[AspNetUsers] AS [Extent20] ON ([Extent20].[Discriminator] = N'ApplicationUser') AND ([Extent19].[Buyer_Id] = [Extent20].[Id])
WHERE ((DATEDIFF (minute, @p__linq__0, [Extent19].[PurchaseTime])) >= 0) AND ([Extent19].[Valid] = 1) AND (([Project12].[UserName] = [Extent20].[UserName]) OR (([Project12].[UserName] IS NULL) AND ([Extent20].[UserName] IS NULL)))
) AS [Filter14]) AS [C5]
FROM ( SELECT
[Project11].[UserName] AS [UserName],
[Project11].[C1] AS [C1],
[Project11].[C2] AS [C2],
[Project11].[C3] AS [C3],
(SELECT
SUM([Filter12].[A1]) AS [A1]
FROM ( SELECT
(SELECT
SUM([Extent18].[Sum]) AS [A1]
FROM [dbo].[UserPayments] AS [Extent18]
WHERE (([Extent16].[ID] = [Extent18].[Purchase_ID]) OR (([Extent16].[ID] IS NULL) AND ([Extent18].[Purchase_ID] IS NULL))) AND ( NOT ((N'Paid' = [Extent18].[Status]) AND ([Extent18].[Status] IS NOT NULL)))) AS [A1]
FROM [dbo].[Purchases] AS [Extent16]
INNER JOIN [dbo].[AspNetUsers] AS [Extent17] ON ([Extent17].[Discriminator] = N'ApplicationUser') AND ([Extent16].[Buyer_Id] = [Extent17].[Id])
WHERE ((DATEDIFF (minute, @p__linq__0, [Extent16].[PurchaseTime])) >= 0) AND ([Extent16].[Valid] = 1) AND (([Project11].[UserName] = [Extent17].[UserName]) OR (([Project11].[UserName] IS NULL) AND ([Extent17].[UserName] IS NULL)))
) AS [Filter12]) AS [C4]
FROM ( SELECT
[Project6].[UserName] AS [UserName],
[Project6].[C1] AS [C1],
[Project6].[C2] AS [C2],
(SELECT
SUM([Apply4].[A1]) AS [A1]
FROM ( SELECT
CASE WHEN ([Project9].[EntrySum] IS NULL) THEN CASE WHEN (cast(0 as float(53)) - ((SELECT
SUM([Extent15].[EntrySum]) AS [A1]
FROM [dbo].[PurchaseHistories] AS [Extent15]
WHERE (([Project9].[ID] = [Extent15].[Purchase_ID]) OR (([Project9].[ID] IS NULL) AND ([Extent15].[Purchase_ID] IS NULL))) AND (N'PartialReturn' = [Extent15].[EntryType]))) IS NULL) THEN cast(0 as float(53)) ELSE cast(0 as float(53)) - ((SELECT
SUM([Extent14].[EntrySum]) AS [A1]
FROM [dbo].[PurchaseHistories] AS [Extent14]
WHERE (([Project9].[ID] = [Extent14].[Purchase_ID]) OR (([Project9].[ID] IS NULL) AND ([Extent14].[Purchase_ID] IS NULL))) AND (N'PartialReturn' = [Extent14].[EntryType]))) END ELSE [Element4].[EntrySum] END AS [A1]
FROM (SELECT
[Project7].[ID] AS [ID],
[Element3].[EntrySum] AS [EntrySum]
FROM (SELECT
[Extent10].[ID] AS [ID]
FROM [dbo].[Purchases] AS [Extent10]
INNER JOIN [dbo].[AspNetUsers] AS [Extent11] ON ([Extent11].[Discriminator] = N'ApplicationUser') AND ([Extent10].[Buyer_Id] = [Extent11].[Id])
WHERE ((DATEDIFF (minute, @p__linq__0, [Extent10].[PurchaseTime])) >= 0) AND ([Extent10].[Valid] = 1) AND (([Project6].[UserName] = [Extent11].[UserName]) OR (([Project6].[UserName] IS NULL) AND ([Extent11].[UserName] IS NULL))) ) AS [Project7]
OUTER APPLY (SELECT TOP (1)
[Extent12].[EntryType] AS [EntryType],
[Extent12].[EntrySum] AS [EntrySum],
[Extent12].[Purchase_ID] AS [Purchase_ID]
FROM [dbo].[PurchaseHistories] AS [Extent12]
WHERE ([Project7].[ID] = [Extent12].[Purchase_ID]) AND (N'NewPurchase' = [Extent12].[EntryType]) ) AS [Element3] ) AS [Project9]
OUTER APPLY (SELECT TOP (1)
[Extent13].[EntryType] AS [EntryType],
[Extent13].[EntrySum] AS [EntrySum],
[Extent13].[Purchase_ID] AS [Purchase_ID]
FROM [dbo].[PurchaseHistories] AS [Extent13]
WHERE ([Project9].[ID] = [Extent13].[Purchase_ID]) AND (N'NewPurchase' = [Extent13].[EntryType]) ) AS [Element4]
) AS [Apply4]) AS [C3]
FROM ( SELECT
[Project1].[UserName] AS [UserName],
[Project1].[C1] AS [C1],
(SELECT
SUM([Apply2].[A1]) AS [A1]
FROM ( SELECT
CASE WHEN ([Project4].[EntrySum] IS NULL) THEN CASE WHEN (cast(0 as float(53)) - ((SELECT
SUM([Extent9].[EntrySum]) AS [A1]
FROM [dbo].[PurchaseHistories] AS [Extent9]
WHERE (([Project4].[ID] = [Extent9].[Purchase_ID]) OR (([Project4].[ID] IS NULL) AND ([Extent9].[Purchase_ID] IS NULL))) AND (N'PartialReturn' = [Extent9].[EntryType]))) IS NULL) THEN cast(0 as float(53)) ELSE cast(0 as float(53)) - ((SELECT
SUM([Extent8].[EntrySum]) AS [A1]
FROM [dbo].[PurchaseHistories] AS [Extent8]
WHERE (([Project4].[ID] = [Extent8].[Purchase_ID]) OR (([Project4].[ID] IS NULL) AND ([Extent8].[Purchase_ID] IS NULL))) AND (N'PartialReturn' = [Extent8].[EntryType]))) END ELSE [Element2].[EntrySum] END AS [A1]
FROM (SELECT
[Project2].[ID] AS [ID],
[Element1].[EntrySum] AS [EntrySum]
FROM (SELECT
[Extent4].[ID] AS [ID]
FROM [dbo].[Purchases] AS [Extent4]
INNER JOIN [dbo].[AspNetUsers] AS [Extent5] ON ([Extent5].[Discriminator] = N'ApplicationUser') AND ([Extent4].[Buyer_Id] = [Extent5].[Id])
WHERE ((DATEDIFF (minute, @p__linq__0, [Extent4].[PurchaseTime])) >= 0) AND ([Extent4].[Valid] = 1) AND (([Project1].[UserName] = [Extent5].[UserName]) OR (([Project1].[UserName] IS NULL) AND ([Extent5].[UserName] IS NULL))) ) AS [Project2]
OUTER APPLY (SELECT TOP (1)
[Extent6].[EntryType] AS [EntryType],
[Extent6].[EntrySum] AS [EntrySum],
[Extent6].[Purchase_ID] AS [Purchase_ID]
FROM [dbo].[PurchaseHistories] AS [Extent6]
WHERE ([Project2].[ID] = [Extent6].[Purchase_ID]) AND (N'NewPurchase' = [Extent6].[EntryType]) ) AS [Element1] ) AS [Project4]
OUTER APPLY (SELECT TOP (1)
[Extent7].[EntryType] AS [EntryType],
[Extent7].[EntrySum] AS [EntrySum],
[Extent7].[Purchase_ID] AS [Purchase_ID]
FROM [dbo].[PurchaseHistories] AS [Extent7]
WHERE ([Project4].[ID] = [Extent7].[Purchase_ID]) AND (N'NewPurchase' = [Extent7].[EntryType]) ) AS [Element2]
) AS [Apply2]) AS [C2]
FROM ( SELECT
[GroupBy1].[K1] AS [UserName],
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
[Extent3].[UserName] AS [K1],
COUNT(1) AS [A1]
FROM [dbo].[Purchases] AS [Extent2]
LEFT OUTER JOIN [dbo].[AspNetUsers] AS [Extent3] ON ([Extent3].[Discriminator] = N'ApplicationUser') AND ([Extent2].[Buyer_Id] = [Extent3].[Id])
WHERE ((DATEDIFF (minute, @p__linq__0, [Extent2].[PurchaseTime])) >= 0) AND ([Extent2].[Valid] = 1)
GROUP BY [Extent3].[UserName]
) AS [GroupBy1]
) AS [Project1]
) AS [Project6]
) AS [Project11]
) AS [Project12]
) AS [Project13]
) AS [Project14]
) AS [Project15]
) AS [Project16]
) AS [Project17] ) AS [Project18] ON ([Extent1].[UserName] = [Project18].[UserName]) OR (([Extent1].[UserName] IS NULL) AND ([Project18].[UserName] IS NULL))
WHERE ([Extent1].[Discriminator] = N'ApplicationUser') AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[AspNetUserRoles] AS [Extent34]
INNER JOIN [dbo].[AspNetRoles] AS [Extent35] ON [Extent34].[RoleId] = [Extent35].[Id]
WHERE ([Extent1].[Id] = [Extent34].[UserId]) AND (N'PassedDisclaimer' = [Extent35].[Name])
))
) AS [Project20]
ORDER BY [Project20].[C2] ASC
с
var users = Db.Users.Where(ApplicationUser.IsPassedDisclaimerLambda);
Я получаю выбор первого запроса, а затем отдельное попадание БД в таблицу ролей для каждого пользователя следующим образом:
SELECT
[Extent1].[UserId] AS [UserId],
[Extent1].[RoleId] AS [RoleId]
FROM [dbo].[AspNetUserRoles] AS [Extent1]
WHERE [Extent1].[UserId] = @EntityKeyValue1
-- EntityKeyValue1: 'f6a7a631-a237-40e7-9efc-92747cd2305f' (Type = String, IsNullable = false, Size = 128)
-- Executing at 28/04/2014 11:23:07 +03:00
-- Completed in 0 ms with result: SqlDataReader
SELECT
[Extent1].[UserId] AS [UserId],
[Extent1].[RoleId] AS [RoleId]
FROM [dbo].[AspNetUserRoles] AS [Extent1]
WHERE [Extent1].[UserId] = @EntityKeyValue1
-- EntityKeyValue1: 'fe12b621-2f07-41d1-8e24-7b80315b2c8e' (Type = String, IsNullable = false, Size = 128)
-- Executing at 28/04/2014 11:23:07 +03:00
-- Completed in 0 ms with result: SqlDataReader
...
Спасибо!
1 ответ
Решение
Как насчет
public static Expression<Func<ApplicationUser, bool>> IsPassedDisclaimerLambda
{
get { return u => u.Roles.Any(ro => ro.Role.Name
== SystemConstants.Roles.MobileUser_PassedDisclaimer); }
}
скорее, чем
public static Func<ApplicationUser, bool> IsPassedDisclaimerLambda
{
get { return u => u.Roles.Any(ro => ro.Role.Name
== SystemConstants.Roles.MobileUser_PassedDisclaimer); }
}
Передача выражения вместо делегата позволит EF построить запрос из выражения. С другой стороны, если вы передаете делегат, нет другого способа применить его, кроме как "на ряд".