Set operations

Overview

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

---

Syntax

Call .Union(), .UnionAll(), .Intersect(), or .Except() on a query builder, passing a second query as an ICompiledQuery:

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 use the same parameter values in both queries.

---

Compiled query interface

Set operations rely on the ICompiledQuery interface, which the generated query builders implement. Any generated Query() builder can participate as either the left or right operand:

// Both operands are ICompiledQuery — either can be on either side
var result = 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 on the set-operation builder; sort in C# after collecting results if ordering is required.