none
Several summary operations in one database roundtrip RRS feed

  • Question

  • Most examples of Linq queries using total summaries is one query returning a single aggregated operation.

    How can you have several  summary operations resulting in only one roundtrip to database.

    For example I want to count the number of orderdetails (using some conditions) and also make a sum of the amount of row.

    Soemthing like an SQL query:

    Select count(*), sum(price*amount) from Orderdetails where ...

    Not really what I am trying to do, what I want is a bit more complex than this example, but just to get some idea what I need to do. To get those summaries is easy but want to know how I can do it with just one roundtrip to database.
    Monday, December 22, 2008 5:48 PM

All replies

  • Have you tried something like this?

    from o in context.Orders select
    new {
        o.Id
        OrderPrice = o.OrderDetails.Sum(od => od.Price * od.Amount),
        OrderDetailsCount = o.OrderDetails.Count()
    }


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, December 22, 2008 10:50 PM
    Moderator
  • That is not really want I want to do. Your example would make a summary for each order and not a total summary (i.e one result row) for all orderdetails.

    I haven't seen any examples how to have multiple aggregations in one query except when you are doing a group by. One thing I have tried is to make a two part query where the first query is creating a dummy column (making it only return a single row when grouping) which I can use for grouping.

    For example something like this:

    var orderQuery = (from o in context.Orders select new
    {
        dummy=0,
        OrderPrice = o.OrderDetails.Sum(od => od.Price * od.Amount),
        OrderDetailsCount = o.OrderDetails.Count()
    });

    var orderSummary = (from o in orderQuery group o by o.dummy into g select new
    {
         totalSum=g.sum(o2 => o2.Orderprice),
         totalCount=g.sum(o2 => o2.OrderDetailsCount)
    }).FirstOrDefault();

    This would result in a single sql query (however probably not as efficient as doing the direct sql query in my first post). I guess there must be a simpler way to do it than this.

    Monday, January 5, 2009 11:37 AM