Projecting with Aggregates in LINQ to Entities

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,
            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);

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.

kick it on

Tweet Post Share Update RSS

Hi, I'm John Papa. I author this blog, create courses for Pluralsight and am a Google Developer Expert and Microsoft Regional Director. I travel speaking at events and train technology thought leaders