单个Linq-to-Entities查询中的多个SQL聚合函数

In SQL you can express multiple aggregates in a single database query like this:

在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

Is it possible to do an equivalent thing using Linq-to-Entities? I found a similar question that suggests it's not possible for Linq-to-SQL but I would like to think I won't have to do the above using four round trips to the DB.

是否可以使用Linq-to-Entities做同等的事情？我发现了一个类似的问题，表明Linq-to-SQL不可能，但我想我不需要四次往返数据库进行上述操作。

3 个解决方案

#1

Suppose you have the following SQL statement:

假设您有以下SQL语句：

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

Try this in C#:

在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)
  }

-- or in VB: --

- 或在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)

The obvious, which in VB would be:

显而易见的，在VB中将是：

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

though it will give the same results, it has a higher cost as it issues multiple SQL select statements, one for each aggregate function, i.e. it will run in multiple passes. The first syntax, gives a single (fairly complex, but efficient) SQL statement which does a single pass against the database.

虽然它会给出相同的结果，但它会产生更高的成本，因为它会发出多个SQL select语句，每个聚合函数一个，即它将在多个传递中运行。第一种语法提供了一个（相当复杂但有效）的SQL语句，它对数据库进行单次传递。

PS. If you don't have a key by which to group by (i.e. you need a single row as the result, covering the whole data set), use a constant as in:

PS。如果您没有要分组的键（即，您需要一行作为结果，覆盖整个数据集），请使用常量，如下所示：

  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)

#2

I don't have your DB, but this (using a "default" EDMX model of Northwind.mdb -- no changes after running the new model wizard) runs as one query in LINQPad:

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

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();

Updated, per your comment:

根据您的评论更新：

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();

#3

~~Unfortunately the answer appears to be no.~~

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

~~If someone can prove otherwise I'll be happy to grant them the accepted answer.~~

如果有人可以证明不然，我会很乐意给他们接受的答案。

EDIT

编辑

I found a way to do this using Entity SQL. I'm not sure it's the greatest way, but as it seems to be the only way then it may be the greatest by default :)

我找到了一种使用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];

The above isn't exactly the code I am working with. For a simpler case without a join, here's the generated SQL, showing that only one trip to the DB is made:

以上并不完全是我正在使用的代码。对于没有连接的更简单的情况，这里是生成的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]

If someone finds a more elegant solution to this problem, I'll grant them the accepted answer.

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

EDIT 2

编辑2

Thanks to Costas who found a great solution to this problem that uses pure Linq.

感谢Costas找到了使用纯Linq的这个问题的一个很好的解决方案。

#1