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) }
    ).Single();

// 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], 
               [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%20Yakunin's gravatar image

Alex Yakunin
29714412

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

Subscription:

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

Tags:

×573

Asked: Jul 12 '10 at 19:15

Seen: 2,522 times

Last updated: Jul 12 '10 at 19:15

powered by OSQA