Advanced Usage

Advanced Usage

Aliased joins

public Sql Join(string table, string on, JoinType type = JoinType.Inner, string? alias = null)

Pass alias to get table alias in the FROM/JOIN clause instead of the raw table name — handy once a query joins the same table more than once, or the on condition needs a short prefix:

var sql = new Sql(DatabaseType.PostgreSQL)
    .Table("orders")
    .Join("customers", "orders.customer_id = c.id", JoinType.Left, alias: "c")
    .Build();
// SELECT * FROM "orders" LEFT JOIN "customers" c ON orders.customer_id = c.id;

JoinType also has Full and Cross beyond Inner/Left/Right.

Mixing AND and OR

.Or() only affects the next condition — every other condition still joins with AND by default, so you can build mixed chains:

var sql = new Sql(DatabaseType.PostgreSQL)
    .Table("users")
    .Is("active", true)
    .Is("role", "admin")
    .Or().Is("role", "owner")
    .Build();
// SELECT * FROM "users" WHERE "active" = 1 AND "role" = 'admin' OR "role" = 'owner';

Note there's no grouping/parentheses support — if you need (a AND b) OR c with explicit grouping, build that condition yourself and pass it to .When(...).

Range and set conditions

Beyond .Is()/.IsNot(), the builder has dedicated methods for ranges and sets, all properly escaped:

var sql = new Sql(DatabaseType.PostgreSQL)
    .Table("products")
    .Between("price", 10, 100)
    .In("category", "books", "games")
    .Build();
// SELECT * FROM "products" WHERE "price" BETWEEN 10 AND 100 AND "category" IN ('books', 'games');

.NotIn(), .IsNull() and .IsNotNull() follow the same pattern.

Cross-dialect paging

.Limit()/.Offset() translate differently per DatabaseType. For every dialect except SqlServer they become LIMIT n OFFSET n (offset-only becomes LIMIT -1 OFFSET n). For SqlServer they become OFFSET n ROWS FETCH NEXT n ROWS ONLY — and since SQL Server requires an ORDER BY for that syntax, the builder automatically inserts ORDER BY (SELECT NULL) if you never called .OrderBy():

new Sql(DatabaseType.SqlServer).Table("users").Offset(20).Limit(10).Build();
// SELECT * FROM [users] ORDER BY (SELECT NULL) OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Raw conditions are not escaped

.When(rawCondition) writes its argument directly into the WHERE clause — unlike every named condition method, it does no escaping. Only pass trusted, hand-written SQL fragments to it (constants, computed comparisons like age > 18), never untrusted user input.