New Posts New Posts RSS Feed: Best practice for aggregate functions
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Best practice for aggregate functions
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
AuerRo View Drop Down
Newbie
Newbie
Avatar

Joined: 14-Mar-2011
Location: Tyrol, Austria
Posts: 21 		Post Options Post Options   Quote AuerRo Quote  Post ReplyReply Direct Link To This Post Topic: Best practice for aggregate functions
    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
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 731 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
AuerRo View Drop Down
Newbie
Newbie
Avatar

Joined: 14-Mar-2011
Location: Tyrol, Austria
Posts: 21 		Post Options Post Options   Quote AuerRo Quote  Post ReplyReply Direct Link To This Post 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!
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down