在单个Linq-to-Entities查询中使用多个SQL聚合函数

Question

在SQL中，您可以通过以下方式在单个数据库查询中表达多个聚合：

SELECT MIN(p.x), MAX(p.x), MIN(p.y), MAX(p.y)
FROM   Places p JOIN Logs l on p.Id = l.PlaceId
WHERE  l.OwnerId = @OwnerId

是否可以使用Linq-to-Entities实现相同的功能？我找到了一个类似的问题，它表明在Linq-to-SQL中不可能实现，但我希望不必使用四次往返数据库来完成上述操作。

- Drew Noakes

---

+1 对于有趣的问题，我自己还没有研究过，但如果它不起作用，那就太奇怪了。你不能使用 let 语法吗？ - Tomas Jansson

@Thomas，就我所知，使用“let”无法分配一个集合，而只能为集合中的单个值分配一个值。我已经使用EF几个月了，今天撞到这个障碍感到很惊讶。我真的不认为这是可能的！ - Drew Noakes

@hunter的删除答案是可以的。 你试过了吗？ 此外， let确实可以与集合一起使用。 你试过了吗？ 我有生产代码可证明两者都可以。 - Craig Stuntz

@Craig，实际上@hunter的已删除答案无法达到原始问题所要求的目标，即进行单个DB调用。在使用SQL Profiler运行它时，会发现进行了四次独立的DB调用，这是合理的，因为编译器生成的代码会四次调用IQueryable。你能否举一个不涉及子查询的使用let与集合的例子？ - Drew Noakes

@Drew，好的，没问题。当然，这是一个很好的解决方案。实际上，这正是我在去年十二月份给你的第二个查询的想法。 - Craig Stuntz

Answer 1

假设您有以下SQL语句：

  select sum(A), max(B), avg(C) from TBL group by D

在C#中尝试以下代码：

  from t in table
  group t by D
  into g
  select new {
     s = g.Sum(x => x.A),
     m = g.Max(x => x.B),
     a = g.Average(x => x.C)
  }

--或者在VB中：--

  from t in TBL
  group t by key = D
  into g = group
  select s = g.Sum(function(x) x.A),
       m = g.Max(function(x) x.B),
       a = g.Average(function(x) x.C)

显然，如果是VB语言，代码应该是这样的：

  aggregate t in TBL into s = Sum(t.A), m = Max(t.B), a = Average(t.C)

虽然这两种语法都能得到相同的结果，但第一种语法成本更低，因为它只需要执行一次 SQL 查询语句，而第二种语法需要为每个聚合函数执行多个 SQL 查询语句，即需要多次运行。第一种语法使用单个（相当复杂但高效）的 SQL 语句，只需对数据库进行一次查询即可完成。

附注：如果您没有用于分组的键（即需要覆盖整个数据集的单个行），请使用常量，例如：

  from t in TBL
  group t by key = 0
  into g = group
  select s = g.Sum(function(x) x.A),
       m = g.Max(function(x) x.B),
       a = g.Average(function(x) x.C)

- Costas

---

这似乎非常有效。感谢您用比使用Entity SQL更优雅和强大的解决方案来解决这个问题。我发现使用“group t by 1 into g”对我很有帮助。再次感谢！ - Drew Noakes

提出的解决方案是使用分组，尽管在问题中没有提到分组。如果没有涉及分组，我只想执行这个SQL：select sum(A), max(B), avg(C) from TBL，该怎么办？ - Goran

@Goran 这在 PS 中已经覆盖了——你通过一个常量进行分组。(这感觉有点奇怪，但似乎是标准答案。我也见过它在原生 SQL 查询中的应用。) - starwed

不幸的是，即使使用更有效的选项，我仍然看到它生成过于复杂的SQL，使用SELECT sum(x) FROM (SELECT sum(y) FROM ...)嵌套子查询，每个子查询一次只聚合一个列，而不是所有需要聚合的列都在单个分组中。看起来仍然没有选项可以给你像自己编写的那样干净简单的SQL。 - Jacob Stamm

Answer 2

我没有你的数据库，但是在LINQPad中使用“默认”的Northwind.mdb EDMX模型（在运行新模型向导后没有更改），可以作为一个查询运行：

var one = from c in Customers 
          where c.PostalCode == "12209"
          select new
          {
              Id = c.Orders.Max(o => o.OrderID),
              Country = c.Orders.Min(o => o.ShipCountry)
          };          

one.Dump();

根据您的评论进行更新：

var two = from c in Customers 
      where c.PostalCode == "12209"
      from o in c.Orders
      group o by o.Customer.PostalCode into g
      select new
      {
          PostalCode = g.Key,
          Id = g.Max(o => o.OrderID),
          Country = g.Min(o => o.ShipCountry)
      };          

two.Dump();

- Craig Stuntz

---

嗨@Craig。我机器上没有Northwind，但是通过观察，我认为这个查询将为每个具有该邮政编码的客户生成一个条目。我想做的是相当于确定具有该邮政编码的任何客户的最大OrderId。这可能吗？我可能过于简化了我的原始问题。我会更新它以更接近我的实际模式。 - Drew Noakes

好的，看到更新的答案了。这个可能可以简化，但我选择遵循第一个查询的模式。 - Craig Stuntz

如果你有兴趣的话，我找到了一个可行的解决方案，虽然它不是最美观的。 - Drew Noakes

Answer 3

不幸的是，答案似乎是否。

如果有人能证明相反，我很乐意授予他们被接受的答案。

---

编辑

我找到了一种使用Entity SQL的方法来实现这个。虽然我不确定这是最好的方法，但既然它似乎是唯一的方法，那么它可能是最好的选择 :)

var cmdText = "SELECT MIN(p.x), MAX(p.x), MIN(p.y), MAX(p.y) " +
              "FROM Places AS p JOIN Logs AS l ON p.Id = l.PlaceId " +
              "WHERE l.OwnerId==123";
var results = CreateQuery<DbDataRecord>(cmdText)
var row = results.First();
var minX = (double)row[0];
var maxX = (double)row[1];
var minY = (double)row[2];
var maxY = (double)row[3];

上面的代码并不是我正在处理的代码。为了更简单的情况，没有使用连接，这里展示生成的SQL，只显示进行了一次数据库访问：

SELECT 
1 AS [C1], 
[GroupBy1].[A1] AS [C2], 
[GroupBy1].[A2] AS [C3], 
[GroupBy1].[A3] AS [C4], 
[GroupBy1].[A4] AS [C5]
FROM ( SELECT 
    MIN([Extent1].[X1]) AS [A1], 
    MAX([Extent1].[X1]) AS [A2], 
    MAX([Extent1].[Y1]) AS [A3], 
    MIN([Extent1].[Y1]) AS [A4]
    FROM [dbo].[Edges] AS [Extent1]
    WHERE [Extent1].[PlaceId] = 123
)  AS [GroupBy1]

如果有人找到了更优雅的解决方案，我会授予他们被接受的答案。

---

编辑2

感谢Costas发现了一种使用纯Linq的绝佳解决方案来解决这个问题。

- Drew Noakes