0 Original problem: calculate a set of aggregates over the same result set. Let's illustrate this in SQL: ``````SELECT COUNT_BIG(*), SUM([a].[Revision]) FROM [dbo].[DocEntity] [a] WHERE [a].[SysName] LIKE '%a%' `````` Tried the following solutions in LINQ: ``````var oneRow = Query.All(t => t.Id==1); // Always returns a single object var entities = from e in Query.All() where e.SysName.Contains("a") select e; // Way 1 (author's): var result1 = ( from ignored in oneRow select new { Count = entities.Count(), Sum = entities.Sum(e => (long) e.Revision) } ).Single(); // Way 2 (with future queries): var futureCount = Query.ExecuteFutureScalar(() => entities.LongCount()); var futureSum = Query.ExecuteFutureScalar(() => entities.Sum(e => (long) e.Revision)); var result2 = new { Count = futureCount.Value, Sum = futureSum.Value }; `````` But both these ways imply there are two different "Stream Aggregate" operations in query plan, because we aggregate over two different queries/subqueries. Obviously, calculating two aggregates over the same result set (i.e. having a single "Stream Aggregate" calculating both of them) would be more attractive. Final solution: use a query with fake .GroupBy: ``````var query = from product in Query.All() where product.ProductName.Contains("a") select new {Product = product, FakeKey = 0} into i group i.Product.UnitPrice by i.FakeKey into g select new { Count = g.Count(), Price = g.Sum() }; var result = query.AsEnumerable().Single(); `````` SQL for it: ``````SELECT Count_big(*) AS [column1], SUM([a].[UnitPrice]) AS [column2] FROM (SELECT [b].[ProductId], [b].[TypeId], [b].[ProductName], [b].[Seller], [b].[Category.Id], [b].[ProductType], [b].[UnitPrice], [b].[UnitsInStock], [b].[UnitsOnOrder], [b].[ReorderLevel], [b].[QuantityPerUnit], 0 AS [column] FROM [dbo].[Products] [b] WHERE ( [b].[ProductName] LIKE '%a%' )) [a] GROUP BY [a].[column]; `````` And its plan: [attachment=0:2vjn80az]Plan.PNG[/attachment:2vjn80az] So this is an ideal solution we need here. This thread was imported from our support forum. The original discussion may contain more detailed answer. asked Jul 12 '10 at 19:15 Alex Yakunin 2971●4●4●12
Be the first one to answer this question!
 toggle preview community wiki