实体框架中的Linq投影：这个投影是否低效？

Question

实体框架中的Linq投影：这个投影是否低效？

3

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

- sports

1

如果我是你，我会运行两个版本的查询并计时。如果你得到一个结果，其中一个明显优于另一个，我会感到惊讶。如果你做得好，那很好，但如果你不行，那么我会选择在审查代码时最容易理解的查询。更好的代码管理应该比小的效率更重要。 - Enigmativity

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Rob · Accepted Answer

它会执行两次。我做了一个模拟的例子，对 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看起来不太整洁。这真的是一种偏好问题。我不确定是否有真正优雅的解决方案。

另一个选择是编写视图，并从该视图查询。它可能在代码中看起来更漂亮，但可能不值得这样做的努力。