Multiple SQL aggregate functions in a single Linq-to-Entities query

Question

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

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.

Answer 1

Suppose you have the following SQL statement:

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

Try this in 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: --

  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:

  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.

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:

  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)

Answer 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:

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

Answer 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 :)

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:

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

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