Linq to Sql query with multiple aggregations

Question

Hi,

Given a simple schema e.g. PurchaseOrders { OrderId, Total, LineItemCount }, I want to generate a simple query for some simple stats like below:

select sum(lineitemcount) as totalitems, sum(total) as totalsales
from purchaseorders

However in Linq to Sql I am struggling to get this into one query.

At the moment I have this:

decimal totalSales = PurchaseOrders.Sum(po => po.Total)
decimal totalItems = PurchaseOrders.Sum(po => po.LineItemcount)

Is there a way to do this as one query?

Answer 1

Here is one way that should work. I'm not sure of the data types so I assumed nullable integers.

Sorry about the formatting. I haven't quite figured this out yet :). But I think you will get the idea.

from t in ( from t in PurchaseOrders select new { t.LineItemCount, t.Total, Dummy = "x" } ) group t by new { t.Dummy } into g select new { TotalItems = (System.Int32?)g.Sum(p => p.LineItemCount), TotalSales = (System.Int32?)g.Sum(p => p.Total) }

Answer 2

Maybe you can try Aggregate:

var totOrder= PurchaseOrders.Aggregate((preOrder, thisOrder)=>SumTwoOrder(preOrder, thisOrder));
var totalSales = totOrder.Total;
var totalItems=totOrder.LineItemCount;

This is how you can define the SumTwoOrder method:

   public PurchaseOrder SumTwoOrder(PurchaseOrder prev, PurchaseOrder thisOrder)
  {
    return new PurchaseORder(prev.Total+thisOrder.Total, prev.LineItemCount+thisOrder.LineItemCount);
  }

Answer 3

Closest I can work out is to give it a fake group-by clause. It works, and outputs as you'd expect, but the generated SQL actually winds up passing in a parameter of "1" and grouping on it which is a tad suboptimal. Here's the syntax for what I've got though:

PurchaseOrders
    .GroupBy(po => 1)
    .Select(pogroup => new {
           TotalSales = pogroup.Sum(po => po.Total),
           TotalItems = pogroup.Sum(po => po.LineItemCount)
        });