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

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

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

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

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 。它可能在代码中看起来更好 - 但可能不值得付出努力

关于c# - Linq 到实体投影 : is this projection inefficient?，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/26834944/