/DB

WHERE Operators

Complete reference for predicate operators supported by the Socigy.OpenSource.DB v0.1.82 WHERE clause builder.

updated 3 May 20264 min readv0.1.82

Overview

Predicates are expressed as C# lambda expressions and are translated to parameterized SQL at build time. You can supply a predicate directly to Query(), pass it to .Where(), or chain both together. All values are captured as SQL parameters — 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 
// C# — 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 
// C# — 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 can be nested to any depth.

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

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

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

String Methods

String matching is translated to SQL LIKE patterns. The method names map directly to their LIKE 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' 
// C# — 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. If you need case-insensitive matching, store your data normalized or use a case-insensitive column type.

HasFlag — FlaggedEnum Properties

HasFlag is supported on properties annotated for junction-table flag storage. It translates to an EXISTS subquery that checks 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) 
// C# — check for a specific flag/role
User.Query(x => x.Roles.HasFlag(Role.Admin))

The exact junction table name and ID values are determined by your schema attributes. Multiple HasFlag calls can be combined with && or ||.

Closed-Over Variables

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

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

var 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 other expression that does not reference the lambda parameter itself.

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

Chainable WHERE Calls

You can pass a predicate to Query() and then add further conditions with .Where(). All conditions are combined with AND.

// C# — stacked conditions
var results = await User.Query(x => x.Status == "active")
    .Where(x => x.Score > 50)
    .Where(x => x.DeletedAt == null)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: WHERE "status" = $1 AND "score" > $2 AND "deleted_at" IS NULL

Limitations

The predicate translator covers the operators listed above. The following SQL features do not have direct C# expression equivalents in the current version:

  • BETWEEN — use x.Score >= lo && x.Score <= hi instead.
  • IN list — use chained || comparisons (x.Status == "a" || x.Status == "b") or use a raw SQL procedure for large lists.
  • ILIKE (case-insensitive LIKE) — normalize data or use a citext column type.
  • Sub-selects in WHERE — use JOIN or procedure mapping for correlated subqueries.
TIP
For complex predicates that cannot be expressed with the above operators, consider Procedure Mapping with a raw .sql file.

See Also