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