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

.net entity-framework linq-to-entities

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.

1
22
5/23/2017 11:45:49 AM

Accepted Answer

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)
25
1/21/2011 1:15:57 PM

Popular Answer

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow