Common table expressions

Hierarchies, org charts, category trees and bills of materials are common in enterprise data, but the SQL for them varies by engine. Managed CTE lets you describe the base case and the recursive case as C# expressions. The pipeline generates the correct WITH clause for the database in use: WITH RECURSIVE on PostgreSQL, MySQL and SQLite, plain WITH on SQL Server, and the right dialect elsewhere (column alias rules, join style in the recursive term and keyword differences are handled for you).

No SQL strings. Conditions are compiler-checked and refactor-safe.

Standard CTE#

A named, reusable subquery that you then filter or join like any typed query:

using Kiwify.Kiwi.ORM.Cte.Extensions;

var highEarners = db
    .WithCte<Employee, Employee>("HighEarners")
    .FromTable<Employee>()
    .Where(e => e.Salary > 50000 && e.Department.ToUpper() == "ENGINEERING")
    .Build()
    .Execute();

Recursive CTE#

Everyone who reports to a manager, at any depth:

var orgChart = db
    .WithRecursiveCte<Employee, Employee, Employee>("OrgChart")
    .BaseCase(e => e.ManagerId == null)
    .RecursiveCase(join => join
        .On(r => r.ManagerId, cte => cte.EmployeeId)
        .Where(r => r.IsActive))
    .MaxRecursion(10)
    .Build()
    .Execute();

The type arguments are the entity, the anchor type and the recursive type. MaxRecursion bounds the depth.

Shaping the result#

Beyond Execute(), a built query supports:

CTE-driven data changes#

A CTE can also drive an INSERT, UPDATE or DELETE. For example, deactivate or re-parent a whole subtree in one statement:

// insert the rows a CTE selects into another table
cte.BuildInsert<EmployeeArchive>().Into(...).ExecuteAll();

// update or delete the rows a CTE selects
cte.BuildUpdate<Employee>().Set(...).Execute();
cte.BuildDelete<Employee>().Execute();

Update and delete also have ExecuteWithJoin() forms. They work with recursive CTEs too.

Raw SQL fragments you embed are validated before execution.

Typical uses#

• Org charts and management reporting chains
• Category trees and navigation menus
• Bills of materials and part explosions
• Threaded comments and nested replies
• Subtree deactivation or re-parenting in one statement

Engines#

CTE providers exist for every supported engine. See Supported databases for what has been executed against a live server, and Performance for the CTE benchmark.