Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

May 4, 2010

Grouping and Summing with Lambda Expressions

Filed under: C#,Lambda Expressions,LINQ,VB.NET @ 12:43 am

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!

10 Comments

  1.   Rostov — May 6, 2010 @ 9:07 am    Reply

    Nice. :)

  2.   Marauz — November 25, 2010 @ 11:16 pm    Reply

    it is a little, cool computational poem ;very usefully

  3.   Ricardo — April 26, 2011 @ 11:34 am    Reply

    Thanks a lot, it helped me.

  4.   alex — May 13, 2011 @ 1:00 pm    Reply

    Great guide! Got me out of the starting pit! Keep up the good work!

  5.   Richard — November 17, 2011 @ 3:49 pm    Reply

    Not quite sure how to take this anonymous variable and convert to a List…..Is there a way to strongly type the variable like IEnumberable mylist =

  6.   DeborahK — November 21, 2011 @ 12:49 am    Reply

    Hi Richard,

    Does this help?

    http://msmvps.com/blogs/deborahk/archive/2009/08/17/defining-lists-of-anonymous-types.aspx

    Deborah

  7.   Frank Lyford — December 14, 2011 @ 10:39 am    Reply

    Hi Deborah — first rate documentation, very clear and understandable, thorough and well-expressed, excellent examples, rare among (us) software developer types. I wondered if you have (or can point me to) a count/group-by example along the same lines — grouping on two fields, for example: count event-types, grouped on event-date and event-type. Thank you

  8.   Jim — February 15, 2012 @ 2:48 am    Reply

    This helped me a lot! :D

    I didn’t know that i could do like this (e => new{bla.bla, bla.bla, bla.bla})

    Thanks a lot :D

  9.   rui — November 12, 2012 @ 7:50 am    Reply

    fantastic

  10.   Balamurugan.s — May 2, 2013 @ 10:11 am    Reply

    i know only the c# linq lamda expression. i am wondering to get the item in vb.net. but you are nice man to help me.thank you..

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2014 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs