Deborah's Developer MindScape

There are often cases where you need to group on multiple properties and potentially sum on others. This post details how to group and sum on multiple properties using lambda expressions.

This example uses an Invoice class, since that provides many opportunities for summing. First, here is a basic Invoice class.

In C#:

class Invoice
{
    public DateTime InvoiceDate { get; set; }
    public int InvoiceType { get; set; }
    public decimal InvoiceAmount { get; set; }
    public int NumberOfItems { get; set; }
}

In VB:

Public Class Invoice
    Public Property InvoiceDate As DateTime
    Public Property InvoiceType As Integer
    Public Property InvoiceAmount As Decimal
    Public Property NumberOfItems As Integer
End Class

Normally, you would populate the Invoice class from data in a database or other data store. But to keep this example simple, the values for the invoices are hard-coded.

In C#:

List<Invoice> invoiceList = new List<Invoice>();

invoiceList = new List<Invoice>
        {new Invoice()
              {
                InvoiceDate=new DateTime(2010,4,30),
                InvoiceType = 1,
                InvoiceAmount = 150,
                NumberOfItems = 8},
        new Invoice()
              {
                InvoiceDate=new DateTime(2010,4,29),
                InvoiceType = 2,
                InvoiceAmount = 215,
                NumberOfItems = 7},
        new Invoice()
              {
                InvoiceDate=new DateTime(2010,4,30),
                InvoiceType = 1,
                InvoiceAmount = 50,
                NumberOfItems = 2},
        new Invoice()
              {
                InvoiceDate=new DateTime(2010,4,29),
                InvoiceType = 2,
                InvoiceAmount = 550,
                NumberOfItems = 5}};

In VB:

Dim invoiceList As List(Of Invoice)

invoiceList = New List(Of Invoice) From
            {New Invoice With
                  {
                    .InvoiceDate = New DateTime(2010, 4, 30),
                    .InvoiceType = 1,
                    .InvoiceAmount = 150,
                    .NumberOfItems = 8},
            New Invoice With
                  {
                    .InvoiceDate = New DateTime(2010, 4, 29),
                    .InvoiceType = 2,
                    .InvoiceAmount = 215,
                    .NumberOfItems = 7},
            New Invoice With
                  {
                    .InvoiceDate = New DateTime(2010, 4, 30),
                    .InvoiceType = 1,
                    .InvoiceAmount = 50,
                    .NumberOfItems = 2},
            New Invoice With
                  {
                    .InvoiceDate = New DateTime(2010, 4, 29),
                    .InvoiceType = 2,
                    .InvoiceAmount = 550,
                    .NumberOfItems = 5}}

This code creates two invoices of type 1 that are dated 4/30/2010 and two invoices of type 2 that are dated 4/29/2010.

Now for the fun part. This example groups on both the InvoiceDate and the InvoiceType properties. The totals accumulate the invoice amount and the number of items. This allows the code to provide totals based both on the date and type.

In C#:

var query = invoiceList
            .GroupBy(g => new { g.InvoiceDate,
                                g.InvoiceType })
            .Select(group => new {
                       InvoiceDate = group.Key.InvoiceDate,
                       InvoiceType = group.Key.InvoiceType,
                       TotalAmount = group.Sum(a=>a.InvoiceAmount),
                       TotalCount = group.Sum(c=>c.NumberOfItems)});

In VB:

Dim query = invoiceList.
           GroupBy(Function(g) New With {Key g.InvoiceDate, 
                                         Key g.InvoiceType}).
           Select(Function(group) New With {
              .InvoiceDate = group.Key.InvoiceDate,
              .InvoiceType = group.Key.InvoiceType,
              .TotalAmount = group.Sum(Function(a) a.InvoiceAmount),
              .TotalCount = group.Sum(Function(c) c.NumberOfItems)})

This code starts with a GroupBy clause with a Lambda expression. The Lambda expression (indicated with the => syntax in C# and the Function keyword in VB) uses the new keyword to create a new anonymous type with two properties: InvoiceDate and InvoiceType. This technique allows the code to group on both properties. More properties can be included here if you need to group on more than two properties.

Notice the Key modifier on the two properties in the Lambda expression for the VB example. This ensures that the anonymous type is immutable, providing read-only values. This is not necessary in the C# code because C# only supports immutable anonymous types.

The next part of the above code is a Select clause with another Lambda expression. The Lambda expression uses the new keyword to create an anonymous type with four properties: the invoice date and type from the grouping and the two totals. Each total uses a Sum clause with a Lambda expression defining the property to sum.

The group.Key syntax provides the key values from the grouping, which in this case are the properties from our first anonymous type.

The group.Sum syntax provides a sum on a particular property in the original list.

To view the results of the query, the code can display the items.

In C#:

foreach (var item in query)
{
    Console.WriteLine("Invoice Date: {0} ({1}) TotalAmount: {2} TotalCount: {3}",
                        item.InvoiceDate.ToShortDateString(),
                        item.InvoiceType,
                        item.TotalAmount,
                        item.TotalCount);
}

In VB:

For Each item In query
    Console.WriteLine("Invoice Date: {0} ({1}) TotalAmount: {2} TotalCount: {3}",
                        item.InvoiceDate.ToShortDateString(),
                        item.InvoiceType,
                        item.TotalAmount,
                        item.TotalCount)
Next

The result is as follows:

Invoice Date: 4/30/2010 (1) TotalAmount: 200 TotalCount: 10
Invoice Date: 4/29/2010 (2) TotalAmount: 765 TotalCount: 12

Use this technique any time you need to group or sum information in your business objects.

Enjoy!