Method-Based Query Syntax Examples: Grouping

 

The examples in this topic show you how to use the GroupBy method to query the AdventureWorks Sales Model using method-based query syntax. The AdventureWorks Sales Model that is used in these examples is built from the Contact, Address, Product, SalesOrderHeader, and SalesOrderDetail tables in the AdventureWorks sample database.

The examples in this topic use the following using/Imports statements:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Globalization;
using System.Data.EntityClient;
using System.Data.SqlClient;
using System.Data.Common;

The following example uses the GroupBy method to return Address objects that are grouped by postal code. The results are projected into an anonymous type.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var query = context.Addresses
        .GroupBy( address => address.PostalCode);                     

    foreach (IGrouping<string, Address> addressGroup in query)
    {
        Console.WriteLine("Postal Code: {0}", addressGroup.Key);
        foreach (Address address in addressGroup)
        {
            Console.WriteLine("\t" + address.AddressLine1 +
                address.AddressLine2);
        }
    }
}

The following example uses the GroupBy method to return Contact objects that are grouped by the first letter of the contact's last name. The results are also sorted by the first letter of the last name and projected into an anonymous type.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var query = context.Contacts
        .GroupBy(c => c.LastName.Substring(0,1))
        .OrderBy(c => c.Key);

    foreach (IGrouping<string, Contact> group in query)
    {
        Console.WriteLine("Last names that start with the letter '{0}':",
            group.Key);
        foreach (Contact contact in group)
        {
            Console.WriteLine(contact.LastName);
        }

    }
}

The following example uses the GroupBy method to return SalesOrderHeader objects that are grouped by customer ID. The number of sales for each customer is also returned.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var query = context.SalesOrderHeaders
        .GroupBy(order => order.CustomerID);

    foreach (IGrouping<int, SalesOrderHeader> group in query)
    {
        Console.WriteLine("Customer ID: {0}", group.Key);
        Console.WriteLine("Order count: {0}", group.Count());

        foreach (SalesOrderHeader sale in group)
        {
            Console.WriteLine("   Sale ID: {0}", sale.SalesOrderID);
        }
        Console.WriteLine("");                    
    }
}
Show: