Common Table Expression (CTE)

To get familiar with CTE, you can check documentation for Transact SQL: WITH common_table_expression

When CTEs are useful

Reusing the same SQL part in complex query
Recursive table processing

Defining simple CTE

CTE in LINQ To DB implements IQueryable and any IQueryable can be converted to CTE with the extension method AsCte("optional_name").

var employeeSubordinatesReport  =
   from e in db.Employee
   select new
   {
      e.EmployeeID,
      e.LastName,
      e.FirstName,
      NumberOfSubordinates = db.Employee
          .Where(e2 => e2.ReportsTo == e.ReportsTo)
          .Count(),
      e.ReportsTo
   };

// define  CTE named EmployeeSubordinatesReport
// employeeSubordinatesReport sub-query used as CTE body 
var employeeSubordinatesReportCte = employeeSubordinatesReport
                                     .AsCte("EmployeeSubordinatesReport");

The variable employeeSubordinatesReportCte can now be reused in other parts of linq query.

var result =
   from employee in employeeSubordinatesReportCte
   from manager in employeeSubordinatesReportCte
                      .LeftJoin(manager => employee.ReportsTo == manager.EmployeeID)
   select new
   {
      employee.LastName,
      employee.FirstName,
      employee.NumberOfSubordinates,
      ManagerLastName = manager.LastName,
      ManagerFirstName = manager.FirstName,
      ManagerNumberOfSubordinates = manager.NumberOfSubordinates
   };

You are not limited in the number of CTEs, defined in a query, and they may reference each other. LINQ To DB will put them in the correct order and generate SQL with one limitation - there should be no circular references between CTEs.

WITH [EmployeeSubordinatesReport]
(
   [ReportsTo],
   [EmployeeID],
   [LastName],
   [FirstName],
   [NumberOfSubordinates]
)
AS
(
   SELECT
      [t2].[ReportsTo],
      [t2].[EmployeeID],
      [t2].[LastName],
      [t2].[FirstName],
      (
         SELECT
            Count(*)
         FROM
            [Employees] [t1]
         WHERE
            [t1].[ReportsTo] IS NULL AND [t2].[ReportsTo] IS NULL OR
            [t1].[ReportsTo] = [t2].[ReportsTo]
      ) as [c1]
   FROM
      [Employees] [t2]
)
SELECT
   [t3].[LastName] as [LastName1],
   [t3].[FirstName] as [FirstName1],
   [t3].[NumberOfSubordinates],
   [manager].[LastName] as [LastName2],
   [manager].[FirstName] as [FirstName2],
   [manager].[NumberOfSubordinates] as [NumberOfSubordinates1]
FROM
   [EmployeeSubordinatesReport] [t3]
      LEFT JOIN [EmployeeSubordinatesReport] [manager]
           ON [t3].[ReportsTo] = [manager].[EmployeeID]

Defining recursive CTE

Recursive CTEs are special because they are allowed to reference themselves! Because of this special ability, you can use recursive CTEs to solve problems other queries cannot. As an example, recursive CTEs are really good at working with hierarchical data such as org charts for bill of materials. (Further reading: Recursive CTEs Explained).

CTEs have limitations that are not handled by LINQ To DB, so you have to be aware of them before start of usage - Guidelines for Defining and Using Recursive Common Table Expressions

Since in C# language we can not use a variable's reference in its own initialization expression, we have created a function that helps in defining recursive queries: GetCte<TCteProjection>(cte => ...). TCteProjection is a required generic parameter that is needed for resolving the type of the lambda parameter.

The following example shows how to define a CTE to calculate the employee level in the hierarchy:

// defining class for representing Recursive CTE
class EmployeeHierarchyCTE
{
   public int EmployeeID;
   public string LastName;
   public string FirstName;
   public int? ReportsTo;
   public int HierarchyLevel;
}

using (var db = new NorthwindDB(context))
{
   var employeeHierarchyCte = db.GetCte<EmployeeHierarchyCTE>(employeeHierarchy =>
   {
      return
         (
            from e in db.Employee
            where e.ReportsTo == null
            select new EmployeeHierarchyCTE
            {
               EmployeeID = e.EmployeeID,
               LastName = e.LastName,
               FirstName = e.FirstName,
               ReportsTo = e.ReportsTo,
               HierarchyLevel = 1
            }
         )
         .Concat
         (
            from e in db.Employee
            from eh in employeeHierarchy
                        .InnerJoin(eh => e.ReportsTo == eh.EmployeeID)
            select new EmployeeHierarchyCTE
            {
               EmployeeID = e.EmployeeID,
               LastName = e.LastName,
               FirstName = e.FirstName,
               ReportsTo = e.ReportsTo,
               HierarchyLevel = eh.HierarchyLevel + 1
            }
         );
   });

   var result =
      from eh in employeeHierarchyCte
      orderby eh.HierarchyLevel, eh.LastName, eh.FirstName
      select eh;

   var data = result.ToArray();
}

Resulting SQL:

WITH [employeeHierarchy]
(
   [EmployeeID],
   [LastName],
   [FirstName],
   [ReportsTo],
   [HierarchyLevel]
)
AS
(
   SELECT
      [t1].[EmployeeID],
      [t1].[LastName],
      [t1].[FirstName],
      [t1].[ReportsTo],
      1 as [c1]
   FROM
      [Employees] [t1]
   WHERE
      [t1].[ReportsTo] IS NULL
   UNION ALL
   SELECT
      [t2].[EmployeeID],
      [t2].[LastName],
      [t2].[FirstName],
      [t2].[ReportsTo],
      [eh].[HierarchyLevel] + 1 as [c1]
   FROM
      [Employees] [t2]
         INNER JOIN [employeeHierarchy] [eh] ON [t2].[ReportsTo] = [eh].[EmployeeID]
)

SELECT
   [t3].[EmployeeID] as [EmployeeID2],
   [t3].[LastName] as [LastName2],
   [t3].[FirstName] as [FirstName2],
   [t3].[ReportsTo] as [ReportsTo2],
   [t3].[HierarchyLevel]
FROM
   [employeeHierarchy] [t3]
ORDER BY
   [t3].[HierarchyLevel],
   [t3].[LastName],
   [t3].[FirstName]

Database engines that support CTE

Database Engine	Minimal version
Firebird	2.1
MS SQL	2008
MySQL	8.0.1
Oracle	11g Release 2
PostgreSQL	8.4
SQLite	3.8.3
IBM DB2	8
IBM Informix	14.10
ClickHouse

Known limitations