Set operations

Combine two queries with UNION, UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT, or EXCEPT ALL.

updated 5 Jun 20261 min readv0.3.2View as Markdown

Overview

Set operations combine the result sets of two compatible Query() builders. Both queries must return the same model type T.

Syntax

Call .Union(), .UnionAll(), .Intersect(), .IntersectAll(), .Except(), or .ExceptAll() on a query builder, passing a second TableQueryBuilder as the right operand:

var activeQuery  = User.Query(x => x.IsActive == true);
var premiumQuery = User.Query(x => x.IsPremium == true);

await foreach (var user in activeQuery
    .Union(premiumQuery)
    .WithConnection(conn)
    .ExecuteAsync())
{
    Console.WriteLine(user.Username);
}

Available operations

Method	SQL
`.Union(other)`	`UNION` (deduplicated)
`.UnionAll(other)`	`UNION ALL` (includes duplicates)
`.Intersect(other)`	`INTERSECT` (deduplicated)
`.IntersectAll(other)`	`INTERSECT ALL` (includes duplicates)
`.Except(other)`	`EXCEPT` (deduplicated)
`.ExceptAll(other)`	`EXCEPT ALL` (includes duplicates)

UNION ALL example

var recentQuery = Order.Query(x => x.CreatedAt > DateTime.UtcNow.AddDays(-7));
var pendingQuery = Order.Query(x => x.Status == "pending");

await foreach (var order in recentQuery
    .UnionAll(pendingQuery)
    .WithConnection(conn)
    .ExecuteAsync())
{
    Console.WriteLine($"{order.Id}: {order.Status}");
}

Parameter handling

Both sub-queries share a single DbCommand. Parameters from the left and right queries are offset automatically, so there are no conflicts. You can safely reuse the same parameter values in both queries.

Compiled query interface

Every generated Query() builder implements ICompiledQuery internally, which is how its compiled SQL and parameters are merged into the combined command. Any builder can serve as either the left or right operand:

// either query can be on either side
await foreach (var row in leftQuery.Intersect(rightQuery).WithConnection(conn).ExecuteAsync())
{
    // ...
}

Limitations

Both operands must return the same model type T.
Set operations do not currently support chaining a JOIN builder on either operand.
The set-operation builder supports Limit() and Offset() on the combined result. ORDER BY is not available. Sort in C# after collecting results if ordering is required.

← previousJoins

next →INSERT