我不知道LINQ是否会"考虑"这一点。
dbContext.Bills.Select(b => new
{
code = b.Code,
date = b.Date,
weight = b.Package.Weight,
quantity = b.BillRows.Sum(r => (int)r.Quantity) ?? 0,
total = b.Package.Weight * (b.BillRows.Sum(r => (int)r.Quantity) ?? 0),
});
正如你所看到的,投影有两倍于这段代码:
b.BillRows.Sum(r => (int)r.Quantity) ?? 0
问题:LINQ会重用第一个结果吗?还是?它会计算两倍的总和吗?
(*)整个投影都被转换为SQL,所以这里没有在内存中计算任何内容。
我知道我可以做以下事情(但…有点丑陋/不合法)
dbContext.Bills.Select(b => new
{
code = b.Code,
date = b.Date,
weight = b.Package.Weight,
quantity = b.BillRows.Sum(r => (int)r.Quantity) ?? 0,
total = 0,
}).Select(b => new
{
code = b.code,
date = b.date,
weight = b.weight,
quantity = b.quantity,
total = b.weight * b.quantity
});
它会做两次。我做了一个模拟例子,总结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 {
// code = br.b.code,
// date = br.b.date,
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
看起来不太整洁。这确实是一个偏好问题。我不确定是否有一个真正优雅的解决方案。
另一种选择是编写一个视图,然后查询该视图。它在代码中可能看起来更好,但可能不值得付出任何努力