The SelfJoin Extension

Coveo for Sitecore 4.1 (November 2018)

The SelfJoin extension consists of an extension method that was defined by Sitecore; it’s not part of the LINQ extensions. The purpose of this extension is to join results from two queryable instances and return the results from the outer queryable only.

Here is the signature of the extension method.

public static IQueryable<TOuter> SelfJoin<TOuter, TInner, TKey>(this IQueryable<TOuter> outer,
                                                                IEnumerable<TInner> inner,
                                                                Expression<Func<TOuter, TKey>> outerKeySelector,
                                                                Expression<Func<TInner, TKey>> innerKeySelector)

The method takes four arguments. The first one is implicit and corresponds to the outer queryable instance from which the extension method is called. The remaining three arguments are:

  1. The inner enumerable instance: in practice, the underlying queryable instance will be used.
  2. The outer key selector: it’s a lambda expression that indicates which object property should be used as the outer key.
  3. The inner key selector: it’s a lambda expression that indicates which object property should be used as the inner key.

The index fields that correspond to the outer and inner keys must be marked as facet in the Coveo search index. If the fields aren’t facet, the SelfJoin will return no result.


If you want to have a list of employees with their respective managers, and each employee is linked to its manager by its item ID, your business objects would probably look like this:

public abstract class Person
    public Sitecore.Data.ID Id { get; set; }
    public string Name { get; set; }

public class Manager : Person

public class Employee : Person
    public Sitecore.Data.ID ManagerId { get; set; }

The Join query would look like this:

using (var context = ContentSearchManager.GetSearchIndex("sitecore_master_index").CreateSearchContext()) {
    // The queryables for the managers and the employees.
    // You can add specific filters to those queryables.
    var managersQueryable = context.GetQueryable<Manager>();
    var employeesQueryable = context.GetQueryable<Employee>();
    // This call joins the managers and the employees together using the IDs
    // and then returns managers.
    var results = managersQueryable.SelfJoin(employeesQueryable,
                                             manager => manager.Id,
                                             employee => employee.ManagerId);

The results enumerable would return the managers that are linked to one employee or more. The results would be typed as Manager like the outer queryable that was used.

Additional Information

In the Coveo implementation, the SelfJoin extension uses Nested Queries in the background. When calling this extension, a single query will be executed by the search index.