Print Page | Close Window

Best practice for aggregate functions

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=2577
Printed Date: 29-Jul-2026 at 8:34pm


Topic: Best practice for aggregate functions
Posted By: AuerRo
Subject: Best practice for aggregate functions
Date Posted: 22-Mar-2011 at 7:19am
Hi, one more question, this time I'd like to know more about the possibility for aggregating query results.

In my SL-LOB-Application, I'd like to query my database for some aggregate values. Eg: I'd like to have the following values out of my "AccountTransactions"-Table: The count of (all or a queried amount of) entities stored, the sum of (all or a queried amount of) payments, the avg of (all or a queried amount of) payments.... The entities should not be loaded to the client, the query should be executed server-side, only the result (or a created result-entity) should be submitted.

Hopefully this might be simple. Is there a "best practice" to achieve that?

Thanks for any help in advance! Roland


Replies:
Posted By: sbelini
Date Posted: 22-Mar-2011 at 11:45pm

Hi Roland,

 
You can accomplish this by using projection:
 
var aggregateResultsQuery = mgr.Orders.Where(o => o.EmployeeID == 3) // base query 
  .GroupBy(o => true)
  .Select(orders => new {
    Count = orders.Sum(order => 1),
    TotalPayments = orders.Sum(order => order.Freight),
    AveragePayments = orders.Average(order => order.Freight)
  });
 
aggregateResultsQuery.ExecuteAsync(args => {
  if (args.CompletedSuccessfully) {
    var results = args.Results;
 
    // orders should be empty as no entities were loaded in the cache
    var orders = mgr.Orders.With(QueryStrategy.CacheOnly).ToList();
  }
});
 
Regards,
    Silvio

Posted By: AuerRo
Date Posted: 24-Mar-2011 at 6:32am
Thank you, this works great, in addition with dynamic composition of queries it opens great possibilities with charting!


Print Page | Close Window