它会执行两次。我做了一个模拟的例子,对 ID 进行求和(不想编写所有那些类等等);
这是我的查询:
BookingRequests.Select(br => new {
quantity = br.BookingRequestCalendars.Sum(brc => brc.CalendarID),
total = br.BookingRequestCalendars.Sum(brc => brc.CalendarID) * br.Id
}).Dump();
这将生成以下SQL语句:
SELECT (
SELECT SUM([t1].[CalendarID])
FROM [BookingRequestCalendar] AS [t1]
WHERE [t1].[BookingRequestID] = [t0].[Id]
) AS [quantity], ((
SELECT SUM([t2].[CalendarID])
FROM [BookingRequestCalendar] AS [t2]
WHERE [t2].[BookingRequestID] = [t0].[Id]
)) * [t0].[Id] AS [total]
FROM [BookingRequests] AS [t0]
关于性能损失,我无法告诉你。也许DB会为您优化这一点。我建议对此代码进行分析以确定是否值得优化查询。
第二个查询看起来好像更好些:
BookingRequests.Select(br => new {
weight = br.Id,
quantity = br.BookingRequestCalendars.Sum(brc => brc.CalendarID),
total = 0
})
.Select(b => new {
quality = b.quantity,
total = b.weight * b.quantity
})
.Dump();
生成:
SELECT [t2].[value] AS [quality], [t2].[Id] * [t2].[value] AS [total]
FROM (
SELECT [t0].[Id], (
SELECT SUM([t1].[CalendarID])
FROM [BookingRequestCalendar] AS [t1]
WHERE [t1].[BookingRequestID] = [t0].[Id]
) AS [value]
FROM [BookingRequests] AS [t0]
) AS [t2]
针对关于更好的方式的评论,也许可以尝试以下方法?
BookingRequests.Select(br => new {
quantity = br.BookingRequestCalendars.Sum(brc => brc.CalendarID),
b = br
})
.Select(br => new {
quality = br.quantity,
total = br.quantity * br.b.Id
}).Dump();
它生成了以下SQL语句:
SELECT [t2].[value] AS [quality], [t2].[value] * [t2].[Id] AS [br]
FROM (
SELECT (
SELECT SUM([t1].[CalendarID])
FROM [BookingRequestCalendar] AS [t1]
WHERE [t1].[BookingRequestID] = [t0].[Id]
) AS [value], [t0].[Id]
FROM [BookingRequests] AS [t0]
) AS [t2]
即,您将总和作为变量返回,再加上整个行。好处是您不必重复其他属性(只需填写第二个选择)-缺点是
br.b.Id
看起来不太整洁。这真的是一种偏好问题。我不确定是否有真正优雅的解决方案。
另一个选择是编写视图,并从该视图查询。它可能在代码中看起来更漂亮,但可能不值得这样做的努力。