/DB

WHERE Operators

Complete reference for the predicate operators the WHERE clause builder translates to parameterized SQL.

updated 5 Jun 20264 min readv0.3.2View as Markdown

Overview

Predicates are C# lambda expressions translated to parameterized SQL at build time. Supply a predicate directly to Query() or pass it to .Where(). The builder holds a single predicate, so .Where() replaces any predicate set earlier; combine conditions inside one lambda with && / ||. Every value is captured as a SQL parameter, never string-interpolated into the query text.

Operator Reference

Comparison Operators

C# expression Generated SQL
x.Name == "value" "name" = $1
x.Name != "value" "name" != $1
x.Count > 5 "count" > $1
x.Count >= 5 "count" >= $1
x.Count < 5 "count" < $1
x.Count <= 5 "count" <= $1 
// comparison examples
User.Query(x => x.Score >= 100)
User.Query(x => x.Age < 18)
User.Query(x => x.Name != "system")

NULL Checks

C# expression Generated SQL
x.Name == null "name" IS NULL
x.Name != null "name" IS NOT NULL 
// null checks
User.Query(x => x.DeletedAt == null)       // active (not soft-deleted)
User.Query(x => x.VerifiedAt != null)      // email verified

Logical Operators

C# expression Generated SQL
cond1 && cond2 ... AND ...
cond1 || cond2 ... OR ...

Logical operators compose freely and nest to any depth.

// AND
User.Query(x => x.Status == "active" && x.Score >= 10)

// OR
User.Query(x => x.Role == "admin" || x.Role == "moderator")

// nested AND / OR
User.Query(x => (x.Role == "admin" || x.Role == "moderator") && x.DeletedAt == null)

String Methods

String matching translates to SQL LIKE patterns. The method names map directly to their wildcard positions.

C# expression Generated SQL
x.Name.Contains("sub") "name" LIKE '%sub%'
x.Name.StartsWith("pre") "name" LIKE 'pre%'
x.Name.EndsWith("suf") "name" LIKE '%suf' 
// string matching
User.Query(x => x.Email.EndsWith("@example.com"))
User.Query(x => x.Username.StartsWith("admin_"))
User.Query(x => x.Bio.Contains("developer"))
NOTE
LIKE is case-sensitive in PostgreSQL for non-citext columns. For case-insensitive matching, store data normalized, use a citext column, or use the ToLower() form below.

HasFlag for FlaggedEnum Properties

HasFlag is supported on properties annotated for junction-table flag storage. It translates to an EXISTS subquery against the junction table.

C# expression Generated SQL
x.Roles.HasFlag(Role.Admin) EXISTS (SELECT 1 FROM junction WHERE owner_id = x.id AND role_id = 8) 
// check for a specific flag/role
User.Query(x => x.Roles.HasFlag(Role.Admin))

The junction table name and ID values come from your schema attributes. Multiple HasFlag calls combine with && or ||.

Closed-Over Variables

Any local variable referenced inside a predicate lambda is captured as a SQL parameter, never embedded as a literal in the query string.

// closed-over variable
int minScore = 5;
string status = "active";

List<User> users = await User.Query(x => x.Score > minScore && x.Status == status)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: WHERE "score" > $1 AND "status" = $2
// Parameters: $1 = 5, $2 = 'active'

This applies equally to properties on objects, results of method calls, and any expression that does not reference the lambda parameter itself.

// object property captured as parameter
var filter = new FilterOptions { MinScore = 50 };
User.Query(x => x.Score >= filter.MinScore)

Combining conditions

The builder keeps a single WHERE predicate: a predicate passed to Query() and any later .Where() call do not stack; the last one wins. Combine conditions inside one lambda with && / ||.

// combine in a single predicate
List<User> results = await User.Query(x => x.Status == "active" && x.Score > 50 && x.DeletedAt == null)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: WHERE "status" = $1 AND "score" > $2 AND "deleted_at" IS NULL

Extended Operators

The translator also handles these patterns.

Pattern Translates to
x => x.IsActive / x => !x.IsActive boolean column predicate
x.Age.HasValue / !x.Age.HasValue IS NOT NULL / IS NULL
x.Age.Value > 5 column comparison
x.Name.ToLower().Contains("ab") "name" ILIKE @p ESCAPE '\' (case-insensitive)
x.Name.Equals("ab") "name" = @p
string.IsNullOrEmpty(x.Name) ("name" IS NULL OR "name" = '')
x.Qty + 1 > 10 arithmetic + - * / %
(x.Age ?? 0) > 5 COALESCE("age", @p) > @p

LIKE escaping: Contains, StartsWith, and EndsWith escape %, _, and \ and append ESCAPE '\'. A value such as "50%" matches literally rather than as a wildcard.

Limitations

The translator covers the operators above. These SQL features have no direct C# expression equivalent in the current version:

  • BETWEEN: use x.Score >= lo && x.Score <= hi.
  • IN list: use chained || comparisons (x.Status == "a" || x.Status == "b"), or a raw SQL procedure for large lists.
  • Sub-selects in WHERE: use a JOIN or procedure mapping for correlated subqueries.
WARNING
Unsupported expressions (nested navigation x.A.B, subqueries, aggregates, or unknown methods) throw NotSupportedException at translation time rather than silently producing invalid SQL.
TIP
For predicates the operators above cannot express, use Procedure Mapping with a raw .sql file.

See Also