John Papa

Projecting with Aggregates in LINQ to Entities

John Papa -

I'm all for quick and easy examples especially when it comes to LINQ, so here goes one that I get asked a lot. This is a quick example that shows how to grab a few properties from a parent entity and use an aggregate function on the parent entity's children entities. Specifically, this example grabs the OrderID and OrderDate from the Orders entity and then calculates the order total for each order. The sum is calculated by summing the UnitPrice * Quantity in the OrderDetails entity.

// Code below creates a projection containing LCD
var qry = from o in context.Orders
        where o.Customers.CustomerID == customerID
        group o by o.OrderID into g
        join o2 in context.Orders on g.Key equals o2.OrderID
        select new
        {
            OrderID = g.Key,
            o2.OrderDate,
            OrderTotal = o2.OrderDetails.Sum(d => d.UnitPrice * d.Quantity)
        };
foreach (var orderInfo in qry)
{
    var msg = string.Format("OrderID={0}, OrderDate={1:d}, OrderTotal={2}",
                orderInfo.OrderID, orderInfo.OrderDate, orderInfo.OrderTotal);
    Conso
le.WriteLine(msg);
}

Notice the grouping of the orders first. Then the Orders are joined back to the result so we can get the OrderDate property. Then the projection pulls out the Key (the OrderID), the OrderDate, and calculates the OrderTotal using an aggregate and a lambda expression.

Hopefully this will shed some light on this type of projection with LINQ for some.