How to produce many agregations over the set of rows

Question

I'm having difficuly mapping a simple T-SQL query such as this

select min(price) as MinPrice, max(price) as MaxPrice, avg(price) as AvgPrice
from titles

to a Linq Expression such as this:

var answer = from t in Titles
select new { MinPrice=Min(t.Price), MaxPrice=Max(t.Price), AvgPrice=Avg(t.Price)};

Obviously this doesn't work since Min doesn't exisit in this context. I also understand that some sort of Group clause is required, but since I don't have a group by in the original T-Sql statement I'm not sure how a group by in this linq query would apply.

I'm using EF 4, but in this case, I doubt that should matter in this example.

Answer 1

Not sure it works with EF, but you could try something like that :

var query =
    from p in products
    group p by 0 into g
    select new
    {
        Min = g.Min(p => p.Price),
        Max = g.Max(p => p.Price),
        Avg = g.Average(p => p.Price)
    };
var result = query.First();

---

EDIT: just tried in LINQPad with Linq to SQL, it works... so it probably works in EF too

---

The query above generates the following SQL:

SELECT TOP (1) [t2].[value] AS [Min], [t2].[value2] AS [Max], [t2].[value3] AS [Avg]
FROM (
    SELECT MIN([t1].[Price]) AS [value], MAX([t1].[Price]) AS [value2], AVG([t1].[Price]) AS [value3]
    FROM (
        SELECT @p0 AS [value], [t0].[Price]
        FROM [Product] AS [t0]
        ) AS [t1]
    GROUP BY [t1].[value]
    ) AS [t2]

(the SELECT TOP(1) part is only there because of the call to First)

This is apparently not optimal, but I assume SQL Server is clever enough to optimize it to something simpler...

Answer 2

var prices = titles.Select(t => t.Price);
var answer = new { min = prices.Min(), max = prices.Max(), avg = prices.Average(); }