.NET 3.5+

LINQ Grouped Joins

by Richard Carr, published at http://www.blackwasp.co.uk/LinqGroupJoins.aspx

The ninth part of the LINQ to Objects tutorial looks at grouped joins. These allow two collections to be combined in a join operation based upon matching key values. The results are then grouped into keyed collections that may be aggregated.

Grouped Joins

In earlier articles in this series we have examined grouping and joining operations. Grouping in LINQ breaks a set of data into a number of smaller sets, each sharing a key value. Joining allows the information from two or more collections to be combined into a single list. In this article we will look at grouped joins. A grouped join provides similar functionality to grouping and joining. An outer list and an inner list are joined into a single entity and then grouped so that each outer element is paired with the list of matching inner items.

To demonstrate grouped joins we need some sample classes and data. These are the same as those in the previous article. One class represents stock items and a second describes the categories that may be applied to those items.

First, add the StockItem class to a new Console Application project:

public class StockItem
{
    public string Name { get; set; }
    public string Category { get; set; }
    public double Price { get; set; }

    public StockItem(string name, string category, double price)
    {
        Name = name;
        Category = category;
        Price = price;
    }
}

You can now add the StockCategory class:

public class StockCategory
{
    public string Name { get; set; }
    public string MajorCategory { get; set; }

    public StockCategory(string name, string majorCategory)
    {
        Name = name;
        MajorCategory = majorCategory;
    }
}

To create an example list of stock items, add the following code:

var stock = new List<StockItem>
{
    new StockItem("Apple", "Fruit", 0.30),
    new StockItem("Banana", "Fruit", 0.35),
    new StockItem("Orange", "Fruit", 0.29),
    new StockItem("Cabbage", "Vegetable", 0.49),
    new StockItem("Carrot", "Vegetable", 0.29),
    new StockItem("Lettuce", "Vegetable", 0.30),
    new StockItem("Milk", "Dairy", 1.12)
};

To complete the test data add the following sample category data:

var categories = new List<StockCategory>
{
    new StockCategory("Dairy", "Chilled"),
    new StockCategory("Fruit", "Fresh"),
    new StockCategory("Vegetable", "Fresh")
};

GroupJoin Standard Query Operator

We will begin the examples by performing a simple grouped join of categories and stock items. The categories will be the outer collection in the example so that one object will be included in the results for each category. Each result will include the major and minor category details and the list of stock items that are linked to that category.

The syntax of the GroupJoin method is similar to that of the Join standard query operator. The extension method is executed against the outer list and the inner list is provided as the first parameter. The second parameter is a Func delegate that returns the key for the outer collection and the third parameter is used to provide a similar delegate for the inner collection. The fourth parameter is the result selector, usually expressed as a lambda expression with two parameters, that generates each result object. The lambda's parameters accept a single outer item and an enumerable collection of matching inner items

To see the GroupJoin operator in action, try executing the following sample code. This links the categories to the stock items by matching the Name and Category properties respectively. The final lambda expression builds the results using anonymous types. In each case the minor and major category names are extracted from the category and the full list of stock items from the second parameter is included. The results that are shown below the sample code are simplified to highlight the structure of the returned information.

var joined = categories.GroupJoin(stock, c => c.Name, s => s.Category,
    (category, stockItems) => new
    {
        MinorCategory = category.Name,
        MajorCategory = category.MajorCategory,
        StockItems = stockItems
    });

/* RESULTS

Chilled/Dairy
    Milk/1.12
Fresh/Fruit
    Apple/0.3
    Banana/0.35
    Orange/0.29
Fresh/Vegetable
    Cabbage/0.49
    Carrot/0.29
    Lettuce/0.3
    
*/

Aggregating Data

In many cases the reason for performing a grouped join is to aggregate the information. This is easy to achieve by modifying the result selector delegate. In the following example the selection of the inner list for each category has been omitted. Instead, two new properties are added to the anonymous type, each the result of an aggregation operation. The results of the grouped join include a single object for each category, with each item hold the major and minor category names and the count and total price of the matching stock items.

var joined = categories.GroupJoin(stock, c => c.Name, s => s.Category,
    (category, stockItems) => new
    {
        MinorCategory = category.Name,
        MajorCategory = category.MajorCategory,
        NumberOfItems = stockItems.Count(),
        TotalPrice = stockItems.Sum(s => s.Price)
    });

/* RESULTS

Chilled/Dairy/1/1.12
Fresh/Fruit/3/0.94
Fresh/Vegetable/3/1.08

*/