Method-Based Query Syntax Examples: Grouping (LINQ to Entities)

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:

Option Explicit On
Option Strict On
Imports L2EExamplesVB.AdventureWorksModel
Imports System.Data.Objects
Imports System.Globalization

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using AdventureWorksModel;
using System.Globalization;

For more information, see How to: Create a LINQ to Entities Project in Visual Studio.

Example

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 AWEntities As New AdventureWorksEntities
    Dim query = AWEntities.Address _
        .GroupBy(Function(Address) Address.PostalCode) _
        .Select(Function(Address) Address)

    For Each addressGroup As IGrouping(Of String, Address) In query
        Console.WriteLine("Postal Code: {0}", addressGroup.Key)
        For Each address As Address In addressGroup

            Console.WriteLine("   " + address.AddressLine1 + address.AddressLine2)
        Next
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    var query = AWEntities.Address
        .GroupBy( address => address.PostalCode)
        .Select( address => address);                     
        
    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);
        }
    }
}

Example

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 AWEntities As New AdventureWorksEntities

    Dim query = AWEntities.Contact _
    .GroupBy(Function(c) c.LastName.Substring(0, 1)) _
    .OrderBy(Function(c) c.Key) _
    .Select(Function(c) c)

    For Each group As IGrouping(Of String, Contact) In query
        Console.WriteLine("Last names that start with the letter '{0}':", group.Key)

        For Each contact As Contact In group

            Console.WriteLine(contact.LastName)
        Next
    Next

End Using

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

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

Example

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 AWEntities As New AdventureWorksEntities

    Dim query = AWEntities.SalesOrderHeader _
        .GroupBy(Function(order) order.CustomerID)

    ' Iterate over each IGrouping
    For Each group In query

        Console.WriteLine("Customer ID: {0}", group.Key)
        Console.WriteLine("Order Count: {0}", group.Count)

        For Each sale In group
            Console.WriteLine("   Sale ID: {0}", sale.SalesOrderID)
        Next

        Console.WriteLine("")

    Next

End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    var query = AWEntities.SalesOrderHeader
        .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("");                    
    }
}

See Also

Concepts

Method-Based Query Syntax Examples (LINQ to Entities)