I think the title speaks for itself. I want to create a LINQ query with aggregations on multiple columns where no grouping is applied. This is just something that can be done very simple in plain SQL.
1 SELECT 2 MAX(HorsePower), 3 AVG(Weight) 4 FROM 5 Car
But when you want to create a construction that's similar in LINQ you will get an compilation error, because of a wrong syntax.
1 var result = from car in sut.Cars 2 select new 3 { 4 MaxHorsePower = car.Max(p => p.HorsePower), 5 AverageWeight = car.Average(p => p.Weight) 6 };
The trouble is that this doesn't work. You can do an aggregation on an complete set, like the following. This will result in just two queries.
1 var result2 = (from car in sut.Cars select car.HorsePower).Max(); 2 var result3 = (from car in sut.Cars select car.Weight).Average();
You can actually do multiple aggregations at once when using groups. The sad thing is, we don't always have something to group on. But that can be solved.
1 var result = (from car in 2 (from car in sut.Cars 3 select new 4 { 5 car.HorsePower, 6 car.Weight, 7 Dummy = string.Empty 8 }) 9 group car by new {car.Dummy} 10 into dummyGroup 11 select new 12 { 13 MaxHorsePower = dummyGroup.Max(p => p.HorsePower), 14 AverageWeight = dummyGroup.Average(p => p.Weight) 15 }).Single();
This solution adds a dummy column called 'Dummy' with the same value for every record. Because this value is the same for every record we can safely group on it and expect to have only one group. This solution makes sure we have one query for the database, but with the use of a workaround.
I would say yes this works, but for readability I would not suggest the use of this workaround for the trivial problem above. By using two queries we have a very manageable solution that's readable from code. The workaround needs at least some comments before we can totally understand it as someone new to the workaround.
But it in the end we can also say, we now know how we can fake the grouping. Use it with care, as I mentioned because of the manageability of your code.
http://mark.mymonster.nl/2008/10/22/linq-to-sql-aggregations-on-multiple-columns-without-any-grouping
