How to: Group Elements in a Sequence (LINQ to SQL)

The GroupBy operator groups the elements of a sequence. The following examples use the Northwind database.

Example

The following example partitions Products by CategoryID.

Dim prodQuery = From prod In db.Products _
    Group prod By prod.CategoryID Into grouping = Group

For Each grp In prodQuery
    Console.WriteLine(vbNewLine & "CategoryID Key = {0}:", _
        grp.CategoryID)
    For Each listing In grp.grouping
        Console.WriteLine(vbTab & listing.ProductName)
    Next
Next

IQueryable<IGrouping<Int32?, Product>> prodQuery =
    from prod in db.Products
    group prod by prod.CategoryID into grouping
    select grouping;

foreach (IGrouping<Int32?, Product> grp in prodQuery)
{
    Console.WriteLine("\nCategoryID Key = {0}:", grp.Key);
    foreach (Product listing in grp)
    {
        Console.WriteLine("\t{0}", listing.ProductName);
    }
}

The following example uses Max to find the maximum unit price for each CategoryID.

Dim query = From p In db.Products _
    Group p By p.CategoryID Into g = Group _
    Select CategoryID, MaxPrice = g.Max(Function(p) p.UnitPrice)

var q =
    from p in db.Products
    group p by p.CategoryID into g
    select new
    {
        g.Key,
        MaxPrice = g.Max(p => p.UnitPrice)
    };

The following example uses Average to find the average UnitPrice for each CategoryID.

Dim q2 = From p In db.Products _
    Group p By p.CategoryID Into g = Group _
    Select CategoryID, AveragePrice = g.Average(Function(p) _
        p.UnitPrice)

var q2 =
    from p in db.Products
    group p by p.CategoryID into g
    select new
    {
        g.Key,
        AveragePrice = g.Average(p => p.UnitPrice)
    };

The following example uses Sum to find the total UnitPrice for each CategoryID.

Dim priceQuery = From prod In db.Products _
    Group prod By prod.CategoryID Into grouping = Group _
    Select CategoryID, TotalPrice = grouping.Sum(Function(p) _
        p.UnitPrice)

For Each grp In priceQuery
    Console.WriteLine("Category = {0}, Total price = {1}", _
        grp.CategoryID, grp.TotalPrice)
Next

            var priceQuery =
                from prod in db.Products
                group prod by prod.CategoryID into grouping
                select new
                {
                    grouping.Key,
                    TotalPrice = grouping.Sum(p => p.UnitPrice)
                };

            foreach (var grp in priceQuery)
            {
                Console.WriteLine("Category = {0}, Total price = {1}",
                    grp.Key, grp.TotalPrice);
            }

The following example uses Count to find the number of discontinued Products in each CategoryID.

Dim disconQuery = From prod In db.Products _
    Group prod By prod.CategoryID Into grouping = Group _
    Select CategoryID, NumProducts = grouping.Count(Function(p) _
        p.Discontinued)

For Each prodObj In disconQuery
    Console.WriteLine("CategoryID = {0}, Discontinued# = {1}", _
        prodObj.CategoryID, prodObj.NumProducts)
Next

var disconQuery =
    from prod in db.Products
    group prod by prod.CategoryID into grouping
    select new
    {
        grouping.Key,
        NumProducts = grouping.Count(p => p.Discontinued)
    };

foreach (var prodObj in disconQuery)
{
    Console.WriteLine("CategoryID = {0}, Discontinued# = {1}",
        prodObj.Key, prodObj.NumProducts);
}

The following example uses a following where clause to find all categories that have at least 10 products.

Dim prodCountQuery = From prod In db.Products _
    Group prod By prod.CategoryID Into grouping = Group _
    Where grouping.Count >= 10 _
    Select CategoryID, ProductCount = grouping.Count

For Each prodCount In prodCountQuery
    Console.WriteLine("CategoryID = {0}, Product count = {1}", _
        prodCount.CategoryID, prodCount.ProductCount)
Next

var prodCountQuery =
    from prod in db.Products
    group prod by prod.CategoryID into grouping
    where grouping.Count() >= 10
    select new
    {
        grouping.Key,
        ProductCount = grouping.Count()
    };

foreach (var prodCount in prodCountQuery)
{
    Console.WriteLine("CategoryID = {0}, Product count = {1}",
        prodCount.Key, prodCount.ProductCount);
}

The following example groups products by CategoryID and SupplierID.

Dim prodQuery = From prod In db.Products _
    Group prod By Key = New With {prod.CategoryID, prod.SupplierID} _
        Into grouping = Group

For Each grp In prodQuery
    Console.WriteLine(vbNewLine & "CategoryID {0}, SupplierID {1}", _
        grp.Key.CategoryID, grp.Key.SupplierID)
    For Each listing In grp.grouping
        Console.WriteLine(vbTab & listing.ProductName)
    Next
Next

var prodQuery =
    from prod in db.Products
    group prod by new
    {
        prod.CategoryID,
        prod.SupplierID
    }
    into grouping
    select new { grouping.Key, grouping };

foreach (var grp in prodQuery)
{
    Console.WriteLine("\nCategoryID {0}, SupplierID {1}",
        grp.Key.CategoryID, grp.Key.SupplierID);
    foreach (var listing in grp.grouping)
    {
        Console.WriteLine("\t{0}", listing.ProductName);
    }
}

The following example returns two sequences of products. The first sequence contains products with unit price less than or equal to 10. The second sequence contains products with unit price greater than 10.

Dim priceQuery = From prod In db.Products _
    Group prod By Key = New With {.Criterion = prod.UnitPrice > 10} _
        Into grouping = Group Select Key, grouping

For Each prodObj In priceQuery
    If prodObj.Key.Criterion = False Then
        Console.WriteLine("Prices 10 or less:")
    Else
        Console.WriteLine("\nPrices greater than 10")
        For Each listing In prodObj.grouping
            Console.WriteLine("{0}, {1}", listing.ProductName, _
                listing.UnitPrice)
        Next
    End If
Next

var priceQuery =
    from prod in db.Products
    group prod by new
    {
        Criterion = prod.UnitPrice > 10
    }
    into grouping
    select grouping;

foreach (var prodObj in priceQuery)
{
    if (prodObj.Key.Criterion == false)
        Console.WriteLine("Prices 10 or less:");
    else
        Console.WriteLine("\nPrices greater than 10");
    foreach (var listing in prodObj)
    {
        Console.WriteLine("{0}, {1}", listing.ProductName,
            listing.UnitPrice);
    }
}

The GroupBy operator can take only a single key argument. If you need to group by more than one key, you must create an anonymous type, as in the following example:

Dim custRegionQuery = From cust In db.Customers _
    Group cust.ContactName By Key = New With _
        {cust.City, cust.Region} Into grouping = Group

For Each grp In custRegionQuery
    Console.WriteLine(vbNewLine & "Location Key: {0}", grp.Key)
    For Each listing In grp.grouping
        Console.WriteLine(vbTab & "{0}", listing)
    Next
Next

var custRegionQuery =
    from cust in db.Customers
    group cust.ContactName by new { City = cust.City, Region = cust.Region };

foreach (var grp in custRegionQuery)
{
    Console.WriteLine("\nLocation Key: {0}", grp.Key);
    foreach (var listing in grp)
    {
        Console.WriteLine("\t{0}", listing);
    }
}

See Also

Concepts

Downloading Sample Databases (LINQ to SQL)

Other Resources

Query Examples (LINQ to SQL)