Joining Results Using LINQ

Joining Results Using LINQ

This is for:

Developer System Administrator

Coveo for Sitecore 4.1 (November 2018)

Sitecore 7.5 added support for new LINQ operators: SelfJoin, Join, and GroupJoin. Before diving into the particularities of these operators, here are the definitions of some terms that are used throughout this section.

Supported Join Operators

Terminology

Queryable

The term queryable is used to refer to an instance of the System.Linq.IQueryable<T> interface. This interface is used by the Search Provider to compose the query that’s executed by the search index.

Enumerable

The term “enumerable” refers to the System.Collections.Generic.IEnumerable<T> interface that’s implemented by most collections in the .NET framework. LINQ defines many extension methods that make common collection operations easier and more coherent among collections.

Outer Versus Inner

The term “outer” is used to define to which list items should be joined to. With a SQL database, you would choose an “outer” table and then join its rows with rows from another table (the “inner” table). The idea is the same, but instead of joining database tables, you’re joining documents that are stored in a search index.

Outer / Inner Queryable

It represents the query that filters each instance of System.Linq.IQueryable<T> that are joined together. It means that you can apply filtering clauses to the IQueryable<T> instance before calling the join operator.

Basic LINQ Join Example

The purpose of this example is to illustrate the basics of a join operation using LINQ. It doesn’t take Sitecore or Coveo for Sitecore into account.

void Main()
{
    // This is the "inner" list.
    List<MasterItem> masterItems = new List<MasterItem> {
        new MasterItem { Id = 1 },
        new MasterItem { Id = 2 },
        new MasterItem { Id = 3 }
    };

    // This is the "outer" list.
    List<DetailItem> detailItems = new List<DetailItem> {
        new DetailItem { Id = 10, MasterId = 1 },
        new DetailItem { Id = 11, MasterId = 1 },
        new DetailItem { Id = 30, MasterId = 3 }
    };
 
    var joinedItems = detailItems.Join(masterItems,
                                       detail => detail.MasterId, // "Outer" key expression
                                       master => master.Id,       // "Inner" key expression
                                       (detail, master) => String.Format("Master: {0}, Detail: {1}", master.Name, detail.Description));

    // Display the joined results in the console
    foreach (string joinedItem in joinedItems) {
        Console.WriteLine(joinedItem);
    }
}

private class MasterItem
{
    public int Id { get; set; }
    public string Name
    {
        get
        {
            return String.Format("Name of master item {0}", Id);
        }
    }
}

private class DetailItem
{
    public int Id { get; set; }
    public int MasterId { get; set; }
    public string Description
    {
        get
        {
            return String.Format("Description of detail item {0}", Id);
        }
    }
}

This program simply joins the MasterItem and DetailItem instances together using the value of the Id and MasterId properties to match the results. At the end, all joined results are displayed in the application console, resulting in this output.

Master: Name of master item 1, Detail: Description of detail item 10
Master: Name of master item 1, Detail: Description of detail item 11
Master: Name of master item 3, Detail: Description of detail item 30

Notice that the master item 2 doesn’t appear in the list, as there are no detail items referring to it. In the same way, the master item 1 appears twice because two detail items are referring to it.