Skip to main content

SqlQueryBuilder

SqlQueryBuilder<T> is the main entry point for building complete SELECT queries. It is a sealed generic class — T is the entity type whose properties are available as typed columns.

var result = new SqlQueryBuilder<Order>(new SqlServerDialect())
    .From("Orders", schema: "dbo")
    .Select(x => x.Id, x => x.Total)
    .Where(x => x.Total > 100)
    .OrderBy(x => x.CreatedAt, ascending: false)
    .Page(1, 20)
    .Build();

Reference​

SELECT​

Select — multiple columns​

Signature

SqlQueryBuilder<T> Select(params Expression<Func<T, object>>[] columns)

Description Adds one or more typed columns to the SELECT list. If never called, the query defaults to SELECT *.

Example

builder.Select(x => x.Id, x => x.Name);
// → SELECT [Id], [Name] FROM ...

Select — single column with alias​

Signature

SqlQueryBuilder<T> Select(Expression<Func<T, object>> column, string? alias)

Description Adds a single typed column, optionally aliased.

Example

builder.Select(x => x.Name, "UserName");
// → SELECT [Name] AS [UserName] FROM ...

SelectAll​

Signature

SqlQueryBuilder<T> SelectAll()

Description Resets the column list to SELECT *, clearing any previously added columns.

SelectRaw​

Signature

SqlQueryBuilder<T> SelectRaw(string rawSql)

Description Appends a raw SQL fragment to the SELECT list. Use for expressions that cannot be expressed through typed columns.

Example

builder.SelectRaw("GETDATE() AS [Now]");
// → SELECT GETDATE() AS [Now] FROM ...

SelectRawIf​

Signature

SqlQueryBuilder<T> SelectRawIf(bool condition, string rawSql)

Description Adds a raw SELECT expression only when condition is true.

SelectIf​

Signature

SqlQueryBuilder<T> SelectIf(bool condition, Expression<Func<T, object>> column, string? alias = null)

Description Adds a typed column only when condition is true.

SelectCount — star​

Signature

SqlQueryBuilder<T> SelectCount(string? alias = null)

Description Adds COUNT(*) to the SELECT list.

Example

builder.SelectCount("total");
// → SELECT COUNT(*) AS [total] FROM ...

SelectCount — column​

Signature

SqlQueryBuilder<T> SelectCount(Expression<Func<T, object>> column, string? alias = null)

Description Adds COUNT([col]) to the SELECT list.

SelectCountDistinct​

Signature

SqlQueryBuilder<T> SelectCountDistinct(Expression<Func<T, object>> column, string? alias = null)

Description Adds COUNT(DISTINCT [col]) to the SELECT list.

SelectSum​

Signature

SqlQueryBuilder<T> SelectSum(Expression<Func<T, object>> column, string? alias = null)

Description Adds SUM([col]) to the SELECT list.

SelectAvg​

Signature

SqlQueryBuilder<T> SelectAvg(Expression<Func<T, object>> column, string? alias = null)

Description Adds AVG([col]) to the SELECT list.

SelectMin​

Signature

SqlQueryBuilder<T> SelectMin(Expression<Func<T, object>> column, string? alias = null)

Description Adds MIN([col]) to the SELECT list.

SelectMax​

Signature

SqlQueryBuilder<T> SelectMax(Expression<Func<T, object>> column, string? alias = null)

Description Adds MAX([col]) to the SELECT list.

SelectCase​

Signature

SqlQueryBuilder<T> SelectCase(CaseWhenBuilder caseWhen)

Description Adds a CASE WHEN ... THEN ... ELSE ... END expression to the SELECT list, built via CaseWhenBuilder.

Example

var caseExpr = new CaseWhenBuilder()
    .When("[Status] = 1", "Active")
    .When("[Status] = 2", "Inactive")
    .Else("Unknown")
    .As("StatusLabel");

builder.SelectCase(caseExpr);
// → SELECT CASE WHEN [Status] = 1 THEN 'Active' WHEN [Status] = 2 THEN 'Inactive' ELSE 'Unknown' END AS [StatusLabel]

SelectCoalesce​

Signature

SqlQueryBuilder<T> SelectCoalesce(
    Expression<Func<T, object>> column, string fallbackSql, string alias)

Description Adds COALESCE([col], fallbackSql) AS [alias] to the SELECT list.

Example

builder.SelectCoalesce(x => x.Department, "'N/A'", "Dept");
// → SELECT COALESCE([Department], 'N/A') AS [Dept] FROM ...

SelectCast​

Signature

SqlQueryBuilder<T> SelectCast(
    Expression<Func<T, object>> column, string typeName, string alias)

Description Adds CAST([col] AS typeName) AS [alias] to the SELECT list.

Example

builder.SelectCast(x => x.Age, "FLOAT", "AgeFloat");
// → SELECT CAST([Age] AS FLOAT) AS [AgeFloat] FROM ...

SelectConcat​

Signature

SqlQueryBuilder<T> SelectConcat(string alias, params Expression<Func<T, object>>[] columns)

Description Adds a dialect-aware string concatenation expression. The operator differs per dialect:

  • SQL Server: [col1] + [col2]
  • PostgreSQL / SQLite: "col1" || "col2"
  • MySQL: CONCAT(`col1`, `col2`)

Example

builder.SelectConcat("FullName", x => x.FirstName, x => x.LastName);
// SQL Server → SELECT [FirstName] + [LastName] AS [FullName]
// PostgreSQL → SELECT "FirstName" || "LastName" AS "FullName"

Window Functions​

SelectRowNumber​

Signature

SqlQueryBuilder<T> SelectRowNumber(
    Expression<Func<T, object>>? partitionBy,
    Expression<Func<T, object>> orderBy,
    bool ascending = true,
    string? alias = "RowNum")

// Multi-partition overload
SqlQueryBuilder<T> SelectRowNumber(
    IReadOnlyList<Expression<Func<T, object>>> partitionBy,
    Expression<Func<T, object>> orderBy,
    bool ascending = true,
    string? alias = "RowNum")

Description Adds ROW_NUMBER() OVER (PARTITION BY [col] ORDER BY [col] ASC|DESC) AS [alias]. Pass null as partitionBy to omit the PARTITION BY clause.

Example

builder.SelectRowNumber(x => x.Department, x => x.Salary, ascending: false, alias: "SalaryRank");
// → SELECT ROW_NUMBER() OVER (PARTITION BY [Department] ORDER BY [Salary] DESC) AS [SalaryRank]

SelectRank​

Signature

SqlQueryBuilder<T> SelectRank(
    Expression<Func<T, object>>? partitionBy,
    Expression<Func<T, object>> orderBy,
    bool ascending = true,
    string? alias = "Rank")

Description Adds RANK() OVER (...). Same gap behavior as standard SQL RANK.

SelectDenseRank​

Signature

SqlQueryBuilder<T> SelectDenseRank(
    Expression<Func<T, object>>? partitionBy,
    Expression<Func<T, object>> orderBy,
    bool ascending = true,
    string? alias = "DenseRank")

Description Adds DENSE_RANK() OVER (...). No gaps in ranking values.

SelectLag​

Signature

SqlQueryBuilder<T> SelectLag(
    Expression<Func<T, object>> column,
    int offset,
    Expression<Func<T, object>>? partitionBy,
    Expression<Func<T, object>> orderBy,
    bool ascending = true,
    string? alias = null)

Description Adds LAG([col], offset) OVER ([PARTITION BY ...] ORDER BY ...). Retrieves the value from a previous row within the partition.

Example

builder.SelectLag(x => x.Price, 1, null, x => x.CreatedAt, alias: "PrevPrice");
// → SELECT LAG([Price], 1) OVER (ORDER BY [CreatedAt] ASC) AS [PrevPrice]

SelectLead​

Signature

SqlQueryBuilder<T> SelectLead(
    Expression<Func<T, object>> column,
    int offset,
    Expression<Func<T, object>>? partitionBy,
    Expression<Func<T, object>> orderBy,
    bool ascending = true,
    string? alias = null)

Description Adds LEAD([col], offset) OVER (...). Retrieves the value from a subsequent row within the partition.

SelectFirstValue​

Signature

SqlQueryBuilder<T> SelectFirstValue(
    Expression<Func<T, object>> column,
    Expression<Func<T, object>>? partitionBy,
    Expression<Func<T, object>> orderBy,
    bool ascending = true,
    string? alias = null)

Description Adds FIRST_VALUE([col]) OVER (...). Retrieves the first value in the ordered window frame.

SelectLastValue​

Signature

SqlQueryBuilder<T> SelectLastValue(
    Expression<Func<T, object>> column,
    Expression<Func<T, object>>? partitionBy,
    Expression<Func<T, object>> orderBy,
    bool ascending = true,
    string? alias = null)

Description Adds LAST_VALUE([col]) OVER (...). Retrieves the last value in the ordered window frame.

SelectSumOver​

Signature

SqlQueryBuilder<T> SelectSumOver(
    Expression<Func<T, object>> column,
    Expression<Func<T, object>>? partitionBy,
    Expression<Func<T, object>>? orderBy,
    bool ascending = true,
    string? alias = null)

Description Adds SUM([col]) OVER ([PARTITION BY ...] [ORDER BY ...]). Useful for running totals. The alias defaults to Running{ColumnName}.

Example

builder.SelectSumOver(x => x.Amount, x => x.CustomerId, x => x.CreatedAt, alias: "RunningTotal");
// → SELECT SUM([Amount]) OVER (PARTITION BY [CustomerId] ORDER BY [CreatedAt] ASC) AS [RunningTotal]

SelectAvgOver​

Signature

SqlQueryBuilder<T> SelectAvgOver(
    Expression<Func<T, object>> column,
    Expression<Func<T, object>>? partitionBy,
    Expression<Func<T, object>>? orderBy,
    bool ascending = true,
    string? alias = null)

Description Adds AVG([col]) OVER (...).

SelectCountOver​

Signature

SqlQueryBuilder<T> SelectCountOver(
    Expression<Func<T, object>> column,
    Expression<Func<T, object>>? partitionBy,
    Expression<Func<T, object>>? orderBy,
    bool ascending = true,
    string? alias = null)

Description Adds COUNT([col]) OVER (...).

SelectMinOver​

Signature

SqlQueryBuilder<T> SelectMinOver(
    Expression<Func<T, object>> column,
    Expression<Func<T, object>>? partitionBy,
    Expression<Func<T, object>>? orderBy,
    bool ascending = true,
    string? alias = null)

Description Adds MIN([col]) OVER (...).

SelectMaxOver​

Signature

SqlQueryBuilder<T> SelectMaxOver(
    Expression<Func<T, object>> column,
    Expression<Func<T, object>>? partitionBy,
    Expression<Func<T, object>>? orderBy,
    bool ascending = true,
    string? alias = null)

Description Adds MAX([col]) OVER (...).

SelectWindowRaw​

Signature

SqlQueryBuilder<T> SelectWindowRaw(string windowExpression, string alias)

Description Adds a raw window function expression to SELECT. Use when the typed overloads don't cover your case.

Example

builder.SelectWindowRaw("NTILE(4) OVER (ORDER BY [Score] DESC)", "Quartile");
// → SELECT NTILE(4) OVER (ORDER BY [Score] DESC) AS [Quartile]

FROM​

From — table name​

Signature

SqlQueryBuilder<T> From(string tableName, string? schema = null)

Description Sets the FROM table. If not called, defaults to typeof(T).Name. Schema is optional.

Example

builder.From("Users", schema: "dbo");
// SQL Server → FROM [dbo].[Users]
// PostgreSQL → FROM "dbo"."Users"

From — subquery​

Signature

SqlQueryBuilder<T> From(SqlQueryResult subquery, string alias)

Description Uses a prebuilt SqlQueryResult as the FROM source: FROM (subquery) alias.

Example

var inner = new SqlQueryBuilder<User>(dialect)
    .From("Users")
    .Where(x => x.IsActive)
    .Build();

new SqlQueryBuilder<User>(dialect)
    .From(inner, "active")
    .Select(x => x.Name)
    .Build();
// → SELECT "Name" FROM (SELECT * FROM "Users" WHERE "IsActive" = true) active

JOINs​

All join methods accept a table name (quoted by the dialect), an optional alias, and a raw ON condition string.

InnerJoin​

Signature

SqlQueryBuilder<T> InnerJoin(string table, string? alias, string on)

Example

builder.From("Users").InnerJoin("Orders", "o", "[Users].[Id] = [o].[UserId]");
// → FROM [Users] INNER JOIN [Orders] o ON [Users].[Id] = [o].[UserId]

LeftJoin​

Signature

SqlQueryBuilder<T> LeftJoin(string table, string? alias, string on)

Description Adds a LEFT JOIN clause.

RightJoin​

Signature

SqlQueryBuilder<T> RightJoin(string table, string? alias, string on)

Description Adds a RIGHT JOIN clause.

FullOuterJoin​

Signature

SqlQueryBuilder<T> FullOuterJoin(string table, string? alias, string on)

Description Adds a FULL OUTER JOIN clause.

CrossJoin​

Signature

SqlQueryBuilder<T> CrossJoin(string table, string? alias = null)

Description Adds a CROSS JOIN clause. No ON condition is required.

InnerJoinSubquery​

Signature

SqlQueryBuilder<T> InnerJoinSubquery(SqlQueryResult subquery, string alias, string on)

Description Adds an INNER JOIN against a derived table (subquery). Parameters from the subquery are automatically remapped to avoid collisions.

Example

var sub = new SqlQueryBuilder<Order>(dialect)
    .From("Orders")
    .Select(x => x.UserId)
    .SelectSum(x => x.Total, "TotalSpent")
    .GroupBy(x => x.UserId)
    .Build();

builder.From("Users").InnerJoinSubquery(sub, "totals", "[Users].[Id] = [totals].[UserId]");
// → FROM [Users] INNER JOIN (SELECT [UserId], SUM([Total]) AS [TotalSpent] FROM [Orders] GROUP BY [UserId]) [totals] ON [Users].[Id] = [totals].[UserId]

LeftJoinSubquery​

Signature

SqlQueryBuilder<T> LeftJoinSubquery(SqlQueryResult subquery, string alias, string on)

Description Adds a LEFT JOIN against a derived table (subquery).

RightJoinSubquery​

Signature

SqlQueryBuilder<T> RightJoinSubquery(SqlQueryResult subquery, string alias, string on)

Description Adds a RIGHT JOIN against a derived table (subquery).

FullOuterJoinSubquery​

Signature

SqlQueryBuilder<T> FullOuterJoinSubquery(SqlQueryResult subquery, string alias, string on)

Description Adds a FULL OUTER JOIN against a derived table (subquery).

WHERE​

Multiple WHERE calls are ANDed together. The ValiFlow<T> / Expression overload and the SqlWhereBuilder overload use different parameter name prefixes (p vs pw) and can coexist safely.

Where — ValiFlow​

Signature

SqlQueryBuilder<T> Where(ValiFlow<T> filter)

Description Sets the WHERE clause from a ValiFlow<T> filter. Parameters use the p prefix.

Example

var filter = new ValiFlow<User>().EqualTo(x => x.IsActive, true).GreaterThan(x => x.Age, 18);
builder.From("Users").Where(filter);
// → WHERE [IsActive] = @p0 AND [Age] > @p1

Where — Expression​

Signature

SqlQueryBuilder<T> Where(Expression<Func<T, bool>> predicate)

Description Sets the WHERE clause from a lambda expression. Parameters use the p prefix.

Note: Calling both Where(ValiFlow<T>) and Where(Expression<...>) is not supported — the second call overwrites the first. Use only one typed predicate; combine with Where(SqlWhereBuilder) for composite conditions.

Where — SqlWhereBuilder (instance)​

Signature

SqlQueryBuilder<T> Where(SqlWhereBuilder<T> whereBuilder)

Description Sets the WHERE clause from a pre-configured SqlWhereBuilder<T>. Parameters use the pw prefix. Can be combined with the typed predicate overload.

Where — SqlWhereBuilder (inline)​

Signature

SqlQueryBuilder<T> Where(Action<SqlWhereBuilder<T>> configure)

Description Configures the WHERE clause inline using a SqlWhereBuilder<T> action.

Example

builder.Where(w => w.EqualTo(x => x.IsActive, true).GreaterThan(x => x.Age, 18));
// → WHERE [IsActive] = @pw0 AND [Age] > @pw1

WhereRaw​

Signature

SqlQueryBuilder<T> WhereRaw(string rawSql)

Description Appends a raw SQL fragment to the WHERE clause (ANDed with other conditions). Parameters in the raw fragment are the caller's responsibility.

Example

builder.WhereRaw("[CreatedAt] > DATEADD(day, -30, GETDATE())");
// → WHERE [CreatedAt] > DATEADD(day, -30, GETDATE())

WhereExists​

Signature

SqlQueryBuilder<T> WhereExists(SqlQueryResult subquery)

Description Appends EXISTS (subquery) to the WHERE clause. Subquery parameters are automatically merged and renamed.

Example

var sub = new SqlQueryBuilder<Order>(dialect)
    .From("Orders")
    .WhereRaw("[Orders].[UserId] = [Users].[Id]")
    .Build();

builder.From("Users").WhereExists(sub);
// → WHERE EXISTS (SELECT * FROM [Orders] WHERE [Orders].[UserId] = [Users].[Id])

WhereNotExists​

Signature

SqlQueryBuilder<T> WhereNotExists(SqlQueryResult subquery)

Description Appends NOT EXISTS (subquery) to the WHERE clause.

WhereInSubquery​

Signature

SqlQueryBuilder<T> WhereInSubquery<TValue>(Expression<Func<T, TValue>> column, SqlQueryResult subquery)

Description Appends [col] IN (subquery) to the WHERE clause. Subquery parameters are automatically remapped.

Example

var sub = new SqlQueryBuilder<Order>(dialect)
    .From("Orders")
    .Select(x => x.UserId)
    .Build();

builder.From("Users").WhereInSubquery(x => x.Id, sub);
// → WHERE [Id] IN (SELECT [UserId] FROM [Orders])

WhereNotInSubquery​

Signature

SqlQueryBuilder<T> WhereNotInSubquery<TValue>(Expression<Func<T, TValue>> column, SqlQueryResult subquery)

Description Appends [col] NOT IN (subquery) to the WHERE clause.

WhereIf — builder overload​

Signature

SqlQueryBuilder<T> WhereIf(bool condition, Action<SqlWhereBuilder<T>> configure)

Description Applies a SqlWhereBuilder WHERE clause only when condition is true.

WhereIf — expression overload​

Signature

SqlQueryBuilder<T> WhereIf(bool condition, Expression<Func<T, bool>> predicate)

Description Applies a lambda WHERE predicate only when condition is true.

GROUP BY / HAVING​

GroupBy​

Signature

SqlQueryBuilder<T> GroupBy(params Expression<Func<T, object>>[] columns)

Description Adds GROUP BY columns.

Example

builder.GroupBy(x => x.Department, x => x.Status);
// → GROUP BY [Department], [Status]

GroupByRaw​

Signature

SqlQueryBuilder<T> GroupByRaw(string rawSql)

Description Adds a raw SQL fragment to the GROUP BY clause. Useful for multi-table queries where table-qualified column names are needed.

Example

builder.GroupByRaw("[Products].[Id], [Products].[Name]");

GroupByIf​

Signature

SqlQueryBuilder<T> GroupByIf(bool condition, Expression<Func<T, object>> column)

Description Adds a GROUP BY column only when condition is true.

Having — raw string​

Signature

SqlQueryBuilder<T> Having(string rawHavingSql)

Description Sets a raw HAVING clause. Used after GroupBy.

Example

builder.GroupBy(x => x.Department).Having("COUNT(*) > 5");
// → GROUP BY [Department] HAVING COUNT(*) > 5

Having — SqlHavingBuilder (instance)​

Signature

SqlQueryBuilder<T> Having(SqlHavingBuilder<T> havingBuilder)

Description Sets the HAVING clause from a SqlHavingBuilder<T>. Can be combined with a raw HAVING string — both are ANDed.

Having — SqlHavingBuilder (inline)​

Signature

SqlQueryBuilder<T> Having(Action<SqlHavingBuilder<T>> configure)

Description Configures the HAVING clause inline.

Example

builder.Having(h => h.CountGreaterThan(5).SumGreaterThan(x => x.Amount, 1000m));

HavingIf — builder overload​

Signature

SqlQueryBuilder<T> HavingIf(bool condition, Action<SqlHavingBuilder<T>> configure)

Description Applies a fluent HAVING clause only when condition is true.

HavingIf — raw string overload​

Signature

SqlQueryBuilder<T> HavingIf(bool condition, string rawHavingSql)

Description Applies a raw HAVING clause only when condition is true.

ORDER BY​

OrderBy​

Signature

SqlQueryBuilder<T> OrderBy(Expression<Func<T, object>> column, bool ascending = true)

Description Adds a primary ORDER BY column.

Example

builder.OrderBy(x => x.CreatedAt, ascending: false);
// → ORDER BY [CreatedAt] DESC

OrderBy — with NullsOrder​

Signature

SqlQueryBuilder<T> OrderBy(Expression<Func<T, object>> column, bool ascending, NullsOrder nulls)

Description Adds ORDER BY with optional NULLS FIRST / NULLS LAST. The nulls parameter is silently ignored on dialects that don't support it (SQL Server, MySQL).

Example

builder.OrderBy(x => x.DeletedAt, ascending: true, NullsOrder.Last);
// PostgreSQL → ORDER BY "DeletedAt" ASC NULLS LAST
// SQL Server → ORDER BY [DeletedAt] ASC  (nulls clause omitted)

ThenBy​

Signature

SqlQueryBuilder<T> ThenBy(Expression<Func<T, object>> column, bool ascending = true)

Description Adds a secondary ORDER BY column. Functionally equivalent to a second OrderBy call.

OrderByIf​

Signature

SqlQueryBuilder<T> OrderByIf(bool condition, Expression<Func<T, object>> column, bool ascending = true)

Description Applies ORDER BY only when condition is true.

OrderByRaw​

Signature

SqlQueryBuilder<T> OrderByRaw(string rawSql)

Description Appends a raw ORDER BY expression verbatim. Useful for multi-table or computed sort expressions.

Example

builder.OrderByRaw("[Products].[Price] DESC, [Name] ASC");

Pagination​

Take​

Signature

SqlQueryBuilder<T> Take(int count)

Description Limits the number of rows returned. Maps to TOP N (SQL Server without OFFSET) or LIMIT N.

Example

builder.From("Users").Take(10);
// SQL Server → SELECT TOP 10 * FROM [Users]
// PostgreSQL → SELECT * FROM "Users" LIMIT 10

Skip​

Signature

SqlQueryBuilder<T> Skip(int count)

Description Skips the specified number of rows. Maps to OFFSET in all dialects.

Example

builder.From("Users").OrderBy(x => x.Id).Skip(20).Take(10);
// SQL Server → SELECT * FROM [Users] ORDER BY [Id] ASC OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
// PostgreSQL → SELECT * FROM "Users" ORDER BY "Id" ASC LIMIT 10 OFFSET 20

Note: SQL Server requires an ORDER BY clause when using OFFSET.

Page​

Signature

SqlQueryBuilder<T> Page(int pageNumber, int pageSize)

Description Sets 1-based page pagination. Page(2, 20) is equivalent to Skip(20).Take(20).

Example

builder.From("Users").OrderBy(x => x.Id).Page(3, 25);
// SQL Server → OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY

DISTINCT / WithHint​

Distinct​

Signature

SqlQueryBuilder<T> Distinct()

Description Adds DISTINCT to the SELECT clause.

Example

builder.From("Orders").Select(x => x.CustomerId).Distinct();
// → SELECT DISTINCT [CustomerId] FROM [Orders]

WithHint​

Signature

SqlQueryBuilder<T> WithHint(string hint)

Description Appends a table hint after FROM. Primarily used with SQL Server for hints like NOLOCK.

Example

builder.From("Users").WithHint("NOLOCK");
// SQL Server → FROM [Users] WITH (NOLOCK)

Note: This generates the hint for all dialects. Use only with SQL Server — other dialects will include an invalid WITH (...) fragment.

Set Operations​

Union​

Signature

SqlQueryBuilder<T> Union(SqlQueryResult other)

Description Appends a UNION (deduplicating) with a prebuilt query.

Example

var active = new SqlQueryBuilder<User>(dialect).From("ActiveUsers").Build();
var inactive = new SqlQueryBuilder<User>(dialect).From("InactiveUsers").Build();
active.Union(inactive);  // not fluent — use on the builder before Build()

// Fluent:
builder.From("ActiveUsers").Union(
    new SqlQueryBuilder<User>(dialect).From("InactiveUsers").Build());
// → SELECT * FROM [ActiveUsers] UNION SELECT * FROM [InactiveUsers]

UnionAll​

Signature

SqlQueryBuilder<T> UnionAll(SqlQueryResult other)

Description Appends a UNION ALL (including duplicates) with a prebuilt query.

Except​

Signature

SqlQueryBuilder<T> Except(SqlQueryResult other)

Description Appends EXCEPT — returns rows in the main query that are not in the other query.

Intersect​

Signature

SqlQueryBuilder<T> Intersect(SqlQueryResult other)

Description Appends INTERSECT — returns only rows that appear in both queries.

CTEs​

WithCte — SqlQueryResult​

Signature

SqlQueryBuilder<T> WithCte(string name, SqlQueryResult cteQuery)

Description Prepends a Common Table Expression: WITH name AS (cteQuery). Multiple calls add multiple CTEs.

Example

var activeCte = new SqlQueryBuilder<User>(dialect)
    .From("Users")
    .Where(w => w.EqualTo(x => x.IsActive, true))
    .Build();

new SqlQueryBuilder<User>(dialect)
    .WithCte("ActiveUsers", activeCte)
    .From("ActiveUsers")
    .Build();
// → WITH "ActiveUsers" AS (SELECT * FROM "Users" WHERE ...) SELECT * FROM "ActiveUsers"

WithCte — inline​

Signature

SqlQueryBuilder<T> WithCte(string name, Action<SqlQueryBuilder<T>> configure)

Description Prepends a CTE defined inline. Creates a nested SqlQueryBuilder<T> internally.

WithRecursive​

Signature

SqlQueryBuilder<T> WithRecursive(string name, SqlQueryResult anchor, SqlQueryResult recursive)

Description Adds a recursive CTE. Generates: WITH [RECURSIVE] name AS (anchor UNION ALL recursive). The RECURSIVE keyword is added automatically for dialects that require it (PostgreSQL, SQLite, MySQL).

Example

var anchor = new SqlQueryBuilder<Category>(dialect).From("Categories").Where(x => x.ParentId == null).Build();
var rec    = new SqlQueryBuilder<Category>(dialect).From("Categories").WhereRaw("[Categories].[ParentId] = [tree].[Id]").Build();

builder.WithRecursive("tree", anchor, rec).From("tree").Build();
// PostgreSQL → WITH RECURSIVE "tree" AS (... UNION ALL ...) SELECT * FROM "tree"
// SQL Server → WITH [tree] AS (... UNION ALL ...) SELECT * FROM [tree]

Row Locking​

ForUpdate​

Signature

SqlQueryBuilder<T> ForUpdate()

Description Appends FOR UPDATE at the end of the query for exclusive row locking. No-op on dialects that don't support row locking (SQL Server — use WithHint("UPDLOCK") instead; SQLite — silently ignored).

ForShare​

Signature

SqlQueryBuilder<T> ForShare()

Description Appends FOR SHARE at the end of the query for shared row locking. No-op on dialects that don't support it.

Tag / Build / ToPreviewSql​

Tag — simple​

Signature

SqlQueryBuilder<T> Tag(string description)

Description Labels the query. When Build() is called, the description is prepended as a SQL comment (-- description). Useful for log tracing.

Example

builder.From("Users").Tag("Get active users").Build();
// SQL: -- Get active users
//      SELECT * FROM [Users]

Tag — with logger​

Signature

SqlQueryBuilder<T> Tag(string description, Action<string>? logger)

Description Same as above, but also invokes logger with "[SQL] {description}" when Build() is called. If logger is null, the tag is embedded only as a SQL comment.

Example

builder.Tag("Get active users", msg => Console.WriteLine(msg));
// Console output: [SQL] Get active users

ToPreviewSql​

Signature

string ToPreviewSql()

Description Returns a preview of the SQL mid-chain without finalizing the builder. Useful in a debugger watch window. Returns a fallback message if the builder state is incomplete.

Build​

Signature

SqlQueryResult Build()

Description Assembles and returns the final SqlQueryResult. This call is terminal — the builder can be reused but the result is immutable.

Advanced Examples​

1) JOIN + WHERE + ORDER​

var q = new SqlQueryBuilder<Order>()
    .SelectAll()
    .From("Orders o")
    .Join("Users u", "o.UserId = u.Id")
    .Where(o => o.Status == "Open")
    .OrderByDesc(o => o.CreatedAt)
    .Take(100);

2) GROUP BY + HAVING​

var q = new SqlQueryBuilder<Order>()
    .SelectCount()
    .SelectRaw("o.CustomerId")
    .From("Orders o")
    .GroupByRaw("o.CustomerId")
    .HavingRaw("COUNT(*) > 5");

3) CTE + Window​

var q = new SqlQueryBuilder<Order>()
    .WithCte("recent", "SELECT * FROM Orders WHERE CreatedAt > @p0")
    .SelectRaw("Id, ROW_NUMBER() OVER (ORDER BY CreatedAt DESC) AS rn")
    .From("recent");