Filtering (WHERE)

Build SQL WHERE predicates from C# operators, null tests, conditionals and parameterised value lists.

Prerequisites: Querying and projections · Entities and metadata

Overview

Where takes a boolean expression and returns a new EntityBuilder<TEntity>; like every builder method it is immutable, so the original is unchanged. Repeating Where combines the predicates with and:

public EntityBuilder<TEntity> Where(Expression<Func<TEntity, bool>> condition)

The lambda is translated into the WHERE clause and nothing is executed until a terminal such as ToListAsync is called. The same expression tree is also what the in-memory provider compiles and runs, so a query that works against a real database can be exercised in memory.

Two kinds of values reach the database differently:

A captured local (a variable from the enclosing scope) and Parameter become command parameters, so the plan can be reused across executions with different values.
A literal constant written directly in the lambda is rendered inline in the SQL text.

Comparison operators

C#	SQL	Notes
`x.Id == 1`	`id = 1`	`=`; null-aware (see below)
`x.Id != 1`	`id != 1`	`!=`
`x.Id > 1`	`(id > 1)`	`>`; comparison operands are parenthesised
`x.Id >= 1`	`(id >= 1)`	`>=`
`x.Id < 1`	`(id < 1)`	`<`
`x.Id <= 1`	`(id <= 1)`	`<=`

var rows = await dataContext.From<SimpleEntity>()
    .Where(x => x.Id >= 9)
    .Select(x => new { x.Id })
    .ToListAsync();

select id
 from simple_entity where (id >= 9)

Null handling

Null literals

Comparing a nullable column with the null literal — a bare null or a nullable cast such as (int?)null — renders is null / is not null instead of = / !=:

var rows = await dataContext.From<ComplexEntity>()
    .Where(x => x.String == null)
    .Select(x => new { x.Id })
    .ToListAsync();

select id
 from complex_entity where somestring is null

!= null emits is not null. The rewrite is driven by the comparison operand being the null literal itself, including a nullable cast such as (int?)null (and the compiler-produced nullable default): both operators render is null / is not null in WHERE, in aggregate predicates such as Count, and in a projected comparison.

Captured nullable values

== and != between a nullable value-type or string column and a captured nullable/string variable follow C# null-equality in both operand orders: null equals null, a null never equals a non-null value, and two non-null values use the provider's ordinary comparison. The relational operators <, >, <= and >= keep plain SQL comparison and are not rewritten.

int? captured = 42;

var rows = await dataContext.From<ComplexEntity>()
    .Where(x => x.Int == captured)
    .Select(x => new { x.Id })
    .ToListAsync();

select id
 from complex_entity
 where ((nullableint is null and $captured is null)
     or (nullableint is not null and $captured is not null and nullableint = $captured))

null == null is true and null != null is false; when exactly one side is null, == is false and != is true. A non-null comparison keeps the provider's own =/!= semantics — for strings that is the column's collation, and nextorm makes no CLR string collation promise. The rewrite is driven by the expression shape, not by the runtime value: the same expression renders the same SQL whether the captured variable currently holds null or a value.

Explicit null-safe comparison (`IS [NOT] DISTINCT FROM`)

The rewrites above are automatic and shape-limited. When a comparison must be two-valued for any operands — two columns included — opt in explicitly with SqlFunctions.Sql.is_not_distinct_from and SqlFunctions.Sql.is_distinct_from, both of which return a non-nullable bool:

int? captured = 42;

var rows = await dataContext.From<ComplexEntity>()
    .Where(x => SqlFunctions.Sql.is_not_distinct_from(x.Int, captured))
    .Select(x => new { x.Id })
    .ToListAsync();

Both NULL operands compare equal, exactly one NULL compares unequal, and two non-null operands use the provider's ordinary comparison; is_distinct_from is the complement. Ordinary == / != stay exactly as documented above — the helper is an explicit opt-in and never changes their SQL. See Null-safe comparison for the truth table, the per-provider rendering and the typing rules.

Presence (`HasValue`)

HasValue / !HasValue on a mapped Nullable<T> column translates to is not null / is null, standalone or as a child of &&/||:

var rows = await dataContext.From<ComplexEntity>()
    .Where(x => x.Int.HasValue)
    .Select(x => new { x.Id })
    .ToListAsync();

select id
 from complex_entity where nullableint is not null

!HasValue renders is null. Nullable<T>.Value is not translated this way and stays outside the rewrite.

Where the null-safe rewrite applies

The total forms are supported in three positions:

predicates in WHERE, including the body of a correlated Exists;
boolean SELECT projections;
the ON expression of a JOIN.

The rest of the statement is unchanged. A boolean projection materialises as a non-nullable bool; SQL Server renders cast(case when ... then 1 else 0 end as bit) through its usual projection legalisation, while the other providers emit the predicate directly. In JOIN ON only the ON expression changes: the join kind, cardinality and outer-row preservation are not affected. On ClickHouse the ON shapes that would render a bare IS NULL are refused while the query is prepared instead — see Limitations.

Parameters and plan reuse

The captured variable stays one command parameter and is never inlined. For the same query and the same captured variable, null → value → null keeps byte-identical SQL and the same cached plan, and every execution sees the current value and returns fresh results. The parameter type comes from the mapped column (or its converter), not from the runtime null, so a SQL Server parameter stays Int/String instead of an untyped DBNull.

Not covered

The rewrite is deliberately limited to a DB column compared with a captured nullable/string variable. The following are not automatically null-safe:

a nullable column compared with another nullable column — in WHERE, a SELECT projection or JOIN ON;
ORDER BY, GROUP BY, window PARTITION BY/ORDER BY and ClickHouse LIMIT BY;
Nullable<T>.Value;
a hand-built DefaultExpression.

Ordinary == / != between those operands keep SQL three-valued logic; when a two-valued result is required, use the explicit null-safe comparison helper.

Combining predicates

&& and || map to and and or and are parenthesised as a group:

var rows = await dataContext.From<ComplexEntity>()
    .Where(x => x.Boolean == true && x.Id > 1)
    .Select(x => new { x.Id })
    .ToListAsync();

select id
 from complex_entity where (b = 1 and (id > 1))

On PostgreSQL the boolean literal is true (b = true); on SQLite and SQL Server it is 1 (b = 1).

Negation

! maps to not (...) and negates the whole operand:

var ids = await dataContext.From<ComplexEntity>()
    .Where(x => !x.Boolean!.Value)
    .Select(x => x.Id)
    .ToListAsync();

select id
 from complex_entity where not (b)

The Output: tables below show the rows returned by each example against the integration-test seed data (tests/nextorm.integration.tests/Providers/TestProviders/SqliteTestProvider.cs).

Output:

Id
2
3

.Where(x => !(x.Boolean!.Value && x.Id > 1L))

select id
 from complex_entity where not ((b and (id > 1)))

When a negation is projected rather than filtered, SQL Server has to materialise it as a bit value (cast(case when not (b) then 1 else 0 end as bit)); SQLite and PostgreSQL keep the boolean scalar (not (b)).

Arithmetic and bitwise operators

Arithmetic operators are rendered verbatim and parenthesised: +, -, *, / and %.

var rows = await dataContext.From("simple_entity")
    .Where(tbl => tbl.GetInt64("id") + 2 == 1)
    .Select(tbl => new { Id = tbl.GetInt64("id") })
    .ToListAsync();

select id
 from simple_entity where (id + 2) = 1

Unary minus is -(x) and the ones-complement is ~(x). Integral bitwise operators map as follows:

C#	SQL	Notes
`x.Id & 1`	`(id & 1)`	bitwise AND
`x.Id \\| 1`	`(id \\| 1)`	bitwise OR
`x.Id << 1`	`(id << 1)`	left shift; not valid T-SQL
`x.Id >> 1`	`(id >> 1)`	right shift; not valid T-SQL
`x.Id ^ 1`	-	XOR is not supported and throws `NotSupportedException`

var rows = await dataContext.From<SimpleEntity>()
    .Where(x => (x.Id & 1) == 1)
    .Select(x => new { x.Id })
    .ToListAsync();

select id
 from simple_entity where (id & 1) = 1

The shift operators are emitted literally. SQLite and PostgreSQL accept << / >>; T-SQL does not, so a query using them fails on SQL Server.

COALESCE (`??`)

?? becomes the provider's coalesce function:

var rows = await dataContext.From<ComplexEntity>()
    .Select(x => new { x.Id, V = x.String ?? "" })
    .ToListAsync();

Provider	SQL
SQLite	`select id, ifnull(somestring, '') as 'V' from complex_entity`
SQL Server	`select id, isnull(somestring,'') as [V] from complex_entity`
PostgreSQL	`select id, coalesce(somestring, '') as "V" from complex_entity`

Conditional (`?:`) and switch

The ternary operator becomes an ANSI CASE WHEN:

var rows = await dataContext.From<ComplexEntity>()
    .Select(x => new { x.Id, Size = x.Id > 1 ? "big" : "small" })
    .ToListAsync();

select id, case when (id > 1) then 'big' else 'small' end as 'Size'
 from complex_entity

A conditional can appear inside a predicate:

var count = await dataContext.From<ComplexEntity>()
    .Where(x => (x.Int == null ? 0 : x.Int) == 1)
    .CountAsync();

select count(*)
 from complex_entity where case when nullableint is null then 0 else nullableint end = 1

A C# switch expression over constant patterns becomes a searched CASE:

var rows = await dataContext.From<ComplexEntity>()
    .Select(e => new { e.Id, Label = e.Id switch { 1 => "one", 2 => "two", _ => "other" } })
    .ToListAsync();

select id, case when id = 1 then 'one' when id = 2 then 'two' else 'other' end as 'Label'
 from complex_entity

A switch whose comparison is a method call (for example a string equality overload, which the C# compiler uses for some string patterns) is not supported and throws NotSupportedException.

Captured parameters and `Parameter`

A captured local is extracted as a named command parameter:

var threshold = 5L;
var rows = await dataContext.From<ComplexEntity>()
    .Where(x => x.Id > threshold)
    .Select(x => new { x.Id })
    .ToListAsync();

Provider	SQL
SQLite	`select id from complex_entity where (id > $threshold)`
SQL Server	`select id from complex_entity where (id > @threshold)`
PostgreSQL	`select id from complex_entity where (id > @threshold)`

Parameter declares a runtime parameter whose value is supplied to the terminal, which is useful when the same query shape is prepared or cached and executed repeatedly:

var rows = await dataContext.From<SimpleEntity>()
    .Where(x => x.Id == SqlFunctions.Parameter<int>(0))
    .Select(x => new { x.Id })
    .ToListAsync(42);

-- SQLite
select id
 from simple_entity where id = $norm_p0

Runtime parameters are named norm_p{index}; the value 42 is bound to norm_p0 by the terminal. See Query reuse: cache vs Prepare for the lifetime rules.

A captured local is registered once per statement, no matter how many times it appears or which source it lives in. A local referenced twice in a WHERE over a join projection shares a single placeholder, and a local that lives only inside a joined derived subquery is bound on the enclosing command:

var v = 5;
var derived = dataContext.From<ComplexEntity>()
    .Where(c => c.Int == v)
    .Select(c => new { c.Id });

var rows = await dataContext.From<SimpleEntity>()
    .Join(derived, (s, d) => s.Id == d.Id)
    .Where(p => p.Item1.Id != v || p.Item2.Id != v)   // one parameter, `@v`, reused
    .Select(p => new { p.Item1.Id })
    .ToListAsync();

-- SQLite
select t1.id
 from simple_entity as 't1'
 join (select id
 from complex_entity where nullableint = $v) as 't2' on cast(t1.id as bigint) = t2.id where (t1.id != $v or t2.id != cast($v as bigint))

The three references to v — in the subquery and twice in WHERE — yield one $v parameter, bound once.

There is no need to alias a captured local into several variables to give each reference its own parameter.

`IN` and `Contains`

SqlFunctions.Sql.@in takes a column plus a QueryCommand<T>, an IEnumerable<T> or a params T[]:

var values = new long[] { 1, 3, 10 };
var ids = await dataContext.From<ComplexEntity>()
    .Where(e => SqlFunctions.Sql.@in(e.Id, values))
    .Select(e => e.Id)
    .ToListAsync();

-- SQLite
select id
 from complex_entity where id in ($p0, $p1, $p2)

Output:

Id
1
3

Contains on a captured List<T> or T[] produces the same IN predicate:

var values = new List<long> { 1, 3 };
var ids = await dataContext.From<ComplexEntity>()
    .Where(e => values.Contains(e.Id))
    .Select(e => e.Id)
    .ToListAsync();

select id
 from complex_entity where id in ($p0, $p1)

Output:

Id
1
3

Special cases:

Case	SQL
Empty collection	`where 1 = 0` (no parameters)
One element	`where id in ($p0)`
Collection with `null` on a nullable column	`where (nullableint in ($p0) or nullableint is null)`
Collection that is only `null`	`where nullableint is null`

A captured list or array is captured by reference by the expression tree, but its values are built into the prepared command. When the collection is mutated or reassigned between executions, nextorm detects the changed shape and rebuilds the command, so a second ToList sees the new values rather than stale results.

`GLOBAL IN` (ClickHouse)

ClickHouse's distributed predicate column GLOBAL IN (subquery | values) is exposed through SqlFunctions.ClickHouse.global_in, which takes the same left-hand column and right-hand side as SqlFunctions.Sql.@in:

var values = new int[] { 1, 3 };
var ids = dataContext.From<ComplexEntity>()
    .Where(e => SqlFunctions.ClickHouse.global_in(e.Id, values))
    .Select(e => e.Id)
    .ToList();

select id
 from complex_entity where global in (@p0, @p1)

Negate it with C# ! to render GLOBAL NOT IN. The predicate requires SupportsGlobalPredicates and is available only on ClickHouse; every other provider and the in-memory context throw NotSupportedException. See Provider-specific SQL for the full catalogue.

Captured collection lookup (`dict[column]`)

A captured Dictionary, List or array indexed by a query expression (dict[s.TenantRegistryId]) is translated to a portable CASE over the collection's entries, so every relational provider emits the same predicate. A collection declared as an interface (IReadOnlyList<T>, IReadOnlyDictionary<TKey,TValue>, IList<T>, IDictionary<TKey,TValue> and the immutable collections) is recognised the same way. The in-memory context needs no translation: it evaluates the indexer as ordinary C#.

var synchronised = new Dictionary<int, DateTime> { [1] = firstSync, [2] = secondSync };
var due = await dataContext.From<Schedule>()
    .Where(s => synchronised[s.TenantRegistryId] > s.ScheduleDate)
    .Select(s => s.Id)
    .ToListAsync();

-- SQLite
select id
 from schedule
 where (case when tenant_registry_id = $p0 then $p1 when tenant_registry_id = $p2 then $p3 end > schedule_date)

The collection is read on the client, so every key and value is bound as a parameter (the parameter count follows the number of entries, and that shape is part of the plan key, so a grown or reassigned collection rebuilds the command instead of reusing a stale plan).

Case	Behaviour
Key is a query expression	`case when key = @k then @v … end`
Key is a constant (`dict[5]`)	evaluated on the client and bound as a single parameter
Key absent at runtime	the `CASE` has no matching branch and yields `NULL` (C# would throw)
Empty collection	`NotSupportedException`
Lookup outside `Where`/pre-where	`NotSupportedException`
Interface-typed collection (`IReadOnlyList<T>`, `IReadOnlyDictionary<TKey,TValue>`, …)	recognised like the concrete types
Server-side array/JSON element access, custom indexer	`NotSupportedException` (no portable SQL form)

A lookup whose key references a query column needs the collection's shape to be part of the plan key: the number of CASE branches — and the bound parameters that go with them — follows the number of entries, and the expression tree itself does not carry that count. The engine folds the shape in while preparing the condition (the Where/Having clause and the pre-where filter), so an indexed lookup is supported there. Used anywhere else (a projection, OrderBy, GroupBy, …) on a cacheable command, the shape would not be in the plan key, so the command is rejected with NotSupportedException instead of risking a stale cached plan that binds the wrong number of parameters. Two cases stay allowed everywhere:

a constant key (dict[5]) folds to a single parameter, so it has no shape dependency;
a command prepared without caching (GetPreparedQueryCommand(..., storeInCache: false)) has no plan to protect, so the lookup is simply evaluated.

If you need a lookup value in a projection, move the condition into Where, prepare the command without caching, or evaluate the lookup on the client after the query.

Pattern and subquery predicates

SqlFunctions.Sql.like, SqlFunctions.Sql.exists, SqlFunctions.Sql.any and SqlFunctions.Sql.all are the remaining predicate helpers:

Expression	SQL
`x.String.Contains("df")`	`somestring like '%df%'`
`x.String.StartsWith("xx")`	`somestring like 'xx%'`
`x.String.EndsWith("sd")`	`somestring like '%sd'`
`x.String.Contains("a%b_c")`	`somestring like '%a\%b\_c%' escape '\'`
`SqlFunctions.Sql.like(x.String, "%a%")`	`somestring like '%a%'`
`SqlFunctions.Sql.like(x.String, "%a!%", "!")`	`somestring like '%a!%' escape '!'`
`SqlFunctions.Sql.exists(query)`	`exists(<query>)`
`x.Id == SqlFunctions.Sql.any(query)`	`id = any(<query>)`
`x.Id == SqlFunctions.Sql.all(query)`	`id = all(<query>)`
`SqlFunctions.Postgres.any(x, array)`	`x = any(@array)` (PostgreSQL)
`x == SqlFunctions.Postgres.any(array)`	`x = any(@array)` (PostgreSQL)
`SqlFunctions.Sql.contains(x.String, "foo")`	`contains(somestring, 'foo')` (SQL Server)
`SqlFunctions.Sql.freetext(x.String, "foo")`	`freetext(somestring, 'foo')` (SQL Server)

SqlFunctions.Sql.contains/SqlFunctions.Sql.freetext are full-text predicates (SupportsFullText, rendered by MakeFullText); the column must be full-text indexed. SQL Server renders contains/freetext (materialised as a bit when projected), PostgreSQL to_tsvector(col) @@ plainto_tsquery(search) (or websearch_to_tsquery for freetext), and MySQL/MariaDB match(col) against(search in boolean mode) > 0 (natural-language mode for freetext).

A scalar subquery is also valid in WHERE: a single-row terminal on the right-hand side (First, Single, their *OrDefault forms, or an aggregate such as Count) renders as a parenthesised select and may reference the outer row (correlated). An aggregate terminal (Count(), Sum(x), Min/Max/Avg) may also stand on the left of the comparison, for example Where(p => children.Where(c => c.ParentId == p.Id).Sum(c => c.Amount) > 6). These forms, together with the full set of subquery positions and the in-memory limits, are covered in Subqueries.

For a captured pattern the wildcards are concatenated around the parameter at build time, for example somestring like '%' || $needle || '%' on SQLite. any and all are not supported by the SQLite engine and fail when the statement runs. Over an array they require PostgreSQL, where the whole array is bound as one parameter; see Arrays.

Function and operator mapping

C# construct	SQL
`==` / `!=`	`=` / `!=`; `is` / `is not` against a `null` literal (including `(int?)null`); captured nullable/string values follow C# null-equality (see Null handling)
`SqlFunctions.Sql.is_not_distinct_from` / `is_distinct_from`	two-valued null-safe comparison — native `IS [NOT] DISTINCT FROM` / `IS` / `<=>`, emulated on SQL Server and ClickHouse (see Null-safe comparison)
`>`, `>=`, `<`, `<=`	`>`, `>=`, `<`, `<=` (parenthesised); not null-safe
`&&` / `\\|\\|`	`and` / `or`
`!`	`not (...)`; in a boolean `SELECT` projection SQL Server casts the predicate to `bit`
`+ - * / %`	`+ - * / %`; string `+` is `\\|\\|` on SQLite/PostgreSQL and `+` on SQL Server
`&` / `\\|`	`&` / `\\|`
`<<` / `>>`	`<<` / `>>`
`^`	not supported (`NotSupportedException`)
`~x`	`~(x)`
`-x`	`-(x)`
`??`	`ifnull` (SQLite), `isnull` (SQL Server), `coalesce` (PostgreSQL)
`?:`	`case when ... then ... else ... end`
`switch`	searched `case when ... then ... end`
`SqlFunctions.Sql.@in` / `Contains`	`in (...)`
`SqlFunctions.Sql.like` / `Contains` / `StartsWith` / `EndsWith`	`like`
`SqlFunctions.Sql.contains` / `SqlFunctions.Sql.freetext`	`contains` / `freetext` (SQL Server); `@@` match (PostgreSQL); `match ... against` (MySQL/MariaDB)

Provider differences

Provider	Behaviour
SQLite	Parameters use `$` (`$norm_p0`); `??` is `ifnull`; boolean literals are `1` / `0`; `any` / `all` are not available.
SQL Server	Parameters use `@`; `??` is `isnull`; boolean literals are `1` / `0`; a projected boolean predicate is wrapped in `cast(case ... as bit)`; shifts are not valid T-SQL.
PostgreSQL	Parameters use `@`; `??` is `coalesce`; boolean literals are `true` / `false`; boolean scalars need no cast.
MySQL	Parameters use `@`; `??` is `coalesce`; boolean literals are `1` / `0`; `any` / `all` are not available; the integer ones-complement renders as `(-(x) - 1)`.
MariaDB	Same as MySQL: `@` parameters, `coalesce`, `1` / `0` booleans, no `any` / `all`.
ClickHouse	Parameters use `@` (the driver rewrites them to `{name:Type}`); `??` is `coalesce`; boolean literals are `true` / `false`; `any` / `all` are not available; `global_in` adds the distributed `GLOBAL IN` predicate.
In-memory	Predicates are compiled as .NET delegates; there is no parameter prefix or SQL rendering.

Table of Contents

Filtering (WHERE)

Overview

Comparison operators

Null handling

Null literals

Captured nullable values

Explicit null-safe comparison (`IS [NOT] DISTINCT FROM`)

Presence (`HasValue`)

Where the null-safe rewrite applies

Parameters and plan reuse

Not covered

Combining predicates

Negation

Arithmetic and bitwise operators

COALESCE (`??`)

Conditional (`?:`) and switch

Captured parameters and `Parameter`

`IN` and `Contains`

`GLOBAL IN` (ClickHouse)

Captured collection lookup (`dict[column]`)

Pattern and subquery predicates

Function and operator mapping

Provider differences

See also

Table of Contents

Filtering (WHERE)

Overview

Comparison operators

Null handling

Null literals

Captured nullable values

Explicit null-safe comparison (IS [NOT] DISTINCT FROM)

Presence (HasValue)

Where the null-safe rewrite applies

Parameters and plan reuse

Not covered

Combining predicates

Negation

Arithmetic and bitwise operators

COALESCE (??)

Conditional (?:) and switch

Captured parameters and Parameter

IN and Contains

GLOBAL IN (ClickHouse)

Captured collection lookup (dict[column])

Pattern and subquery predicates

Function and operator mapping

Provider differences

See also

Explicit null-safe comparison (`IS [NOT] DISTINCT FROM`)

Presence (`HasValue`)

COALESCE (`??`)

Conditional (`?:`) and switch

Captured parameters and `Parameter`

`IN` and `Contains`

`GLOBAL IN` (ClickHouse)

Captured collection lookup (`dict[column]`)