c# - Linq 到实体投影 : is this projection inefficient?

Question

我不知道这是否会被 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
 });

Answer 1

它会做两次。我做了一个总结 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]

至于性能影响，我不能告诉你。也许数据库会为您优化它。我会对这段代码进行一些分析，看看是否值得优化查询

您的第二个查询看起来好一点:

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 看起来不太整洁.这真的是一个偏好问题。我不确定是否有一个非常优雅的解决方案。

另一种选择是编写一个 View ，然后查询该 View 。它在代码中可能看起来更好 - 但可能不值得付出努力