林克对实体的投影:这种投影效率低吗

本文关键字：投影 效率 实体 林克 | 更新日期: 2023-09-27 18:29:44

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

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

相关文章：