.NET 3.5+

LINQ Joins

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

The eighth part of the LINQ to Objects tutorial investigates the Join standard query operator and the equivalent use of the join clause. These allow two or more sets of data to be combined according to key information in each collection.

Using Alternative Comparers

When the outer and inner keys are compared, the two items must match exactly for a result to be generated. If there are minor differences, for example if the keys are strings and one is upper case whilst the other is lower case, the items will not be joined. This can be overcome by using an alternative comparer. Any existing or custom comparer that implements the IEqualityComparer<T> interface may be used.

To demonstrate, change the name of the "Vegetable" category to "vegetable". When you re-run the program you will see that the stock items in the "Vegetable" category are no longer present in the results. However, if we use a case-insensitive comparer, passed as the final argument to the Join method, these will reappear as seen in the sample below. Note that because the category name is being projected from the category object, the MinorCategory value for vegetables is returned in lower case.

var joined = stock.Join(categories, s => s.Category, c => c.Name,
    (stockItem, category) => new
    {
        Name = stockItem.Name,
        Price = stockItem.Price,
        MinorCategory = category.Name,
        MajorCategory = category.MajorCategory
    }, StringComparer.OrdinalIgnoreCase);

/* RESULTS

{ Name = Apple, Price = 0.3, MinorCategory = Fruit, MajorCategory = Fresh }
{ Name = Banana, Price = 0.35, MinorCategory = Fruit, MajorCategory = Fresh }
{ Name = Orange, Price = 0.29, MinorCategory = Fruit, MajorCategory = Fresh }
{ Name = Cabbage, Price = 0.49, MinorCategory = vegetable, MajorCategory = Fresh }
{ Name = Carrot, Price = 0.29, MinorCategory = vegetable, MajorCategory = Fresh }
{ Name = Lettuce, Price = 0.3, MinorCategory = vegetable, MajorCategory = Fresh }
{ Name = Milk, Price = 1.12, MinorCategory = Dairy, MajorCategory = Chilled }

*/

NB: Change the name of the category back to "Vegetable" for the remaining example.

Joins with Query Expression Syntax

In this final section of the article we will recreate the first example using query expression syntax. The second example has no direct equivalent as it uses an alternative comparer, which is unsupported with query syntax. To perform a join requires three new clauses, named join, on and equal.

The join clause is positioned after the from clause and the initial range variable and data source. The clause is followed by a second range variable and data source that define the second collection to be joined. The "on" clause appears next before the key selector for the first data source. Finally, the equals clause and the key selector for the second collection are added. Projection is handled as normal using a select clause.

The following sample code joins the two collections using these clauses and projects the results into an anonymous type.

var joined = from s in stock
    join c in categories
    on s.Category equals c.Name
    select new
    {
        Name = s.Name,
        Price = s.Price,
        MinorCategory = c.Name,
        MajorCategory = c.MajorCategory
    };

/* RESULTS

{ Name = Apple, Price = 0.3, MinorCategory = Fruit, MajorCategory = Fresh }
{ Name = Banana, Price = 0.35, MinorCategory = Fruit, MajorCategory = Fresh }
{ Name = Orange, Price = 0.29, MinorCategory = Fruit, MajorCategory = Fresh }
{ Name = Cabbage, Price = 0.49, MinorCategory = Vegetable, MajorCategory = Fresh }
{ Name = Carrot, Price = 0.29, MinorCategory = Vegetable, MajorCategory = Fresh }
{ Name = Lettuce, Price = 0.3, MinorCategory = Vegetable, MajorCategory = Fresh }
{ Name = Milk, Price = 1.12, MinorCategory = Dairy, MajorCategory = Chilled }

*/