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<Metadata.Type>(t => t.Id==1); // Always returns a single object

var entities = 
  from e in Query.All<DocEntity>()
  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) }

// Way 2 (with future queries):
var futureCount = Query.ExecuteFutureScalar<long>(() => entities.LongCount());
var futureSum   = Query.ExecuteFutureScalar<long>(() => 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<Product>()
    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], 
               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%20Yakunin's gravatar image

Alex Yakunin

Be the first one to answer this question!
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview


Once you sign in you will be able to subscribe for any updates here



Asked: Jul 12 '10 at 19:15

Seen: 4,339 times

Last updated: Jul 12 '10 at 19:15

powered by OSQA