The Join Extension

Coveo for Sitecore 4.1 (November 2018)

The Join extension is defined among other LINQ extensions. It allows two different queryable instances to be taken, and joins the results in a single enumerable. The joined results may use a different type than the types used by the two queryables. The result selector can also return an anonymous type. Here is the signature of the extension method.

public static IEnumerable<TResult> Join<TOuter, TInner, TKey, TResult>(this IEnumerable<TOuter> outer,
                                                                       IEnumerable<TInner> inner,
                                                                       Func<TOuter, TKey> outerKeySelector,
                                                                       Func<TInner, TKey> innerKeySelector,
                                                                       Func<TOuter, TInner, TResult> resultSelector)

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

  1. The inner enumerable instance: in practice, the underlying queryable instance is 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.
  4. The result selector: it’s a lambda expression that defines which result object is returned by the extension. The expression can return an anonymous type.

The index fields that correspond to the outer and inner keys must be marked as facets in the Coveo search index. If the fields aren’t facets, the extension returns no result.


If you have a list of employees with their respective managers, and each employee is linked with 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 will 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 the names of each employee and his manager.
    var results = employeesQueryable.Join(managersQueryable,
                                          employee => employee.ManagerId,
                                          manager => manager.Id,
                                          (employee, manager) => new {
                                              EmployeeName = employee.Name,
                                              ManagerName = manager.Name

The results enumerable uses an anonymous type to return the names of the employees and the managers.

Additional Information

The Coveo implementation of the Join method uses Nested Queries in the background. When calling the extension, a single query is executed by the search index.

Choosing the Outer and Inner Queryables

When dealing with simple lists or even with Lucene, it doesn’t matter which one is the outer or the inner queryable. In the end, the items are simply joined together. With Coveo, you can swap the outer and inner queryables as long as you’re joining one-to-one results. If you want to join one-to-many results, you must make sure that you’re using the outer queryable to represent the “many” and the inner queryable to represent the one.

In the example of managers and employees, an employee only has one manager and a manager manages many employees. To retrieve all employees with their manager, you would need to write code like this:

var results = employees.Join(managers,
                             employee => employee.ManagerId,
                             manager => manager.Id,
                             (employee, manager) => new {
                                 ManagerName = manager.Name,
                                 EmployeeName = employee.Name

If you swap the outer and inner queryables, you would get every manager that manages at least one employee. However, each manager would appear only once, even if they manage many employees.