Table of Contents

Limitations and out-of-scope features

nextorm is a query builder with a small explicit DML surface: it generates SELECT statements (and parameterised INSERT ... VALUES, INSERT ... SELECT, key upserts, MERGE, DELETE and UPDATE) and materialises their results, and deliberately leaves change tracking and SaveChanges to the caller.

Prerequisites: Provider overview · API reference

Overview

This page lists the capabilities that are not part of nextorm, with a one-line reason for each.

Limitations

Area with limitations Rationale
DML — remaining restrictions ClickHouse rejects key upsert, and full MERGE (WHEN MATCHED/WHEN NOT MATCHED/WHEN NOT MATCHED BY SOURCE branches and MERGE ... RETURNING/OUTPUT) exists only on SQL Server and PostgreSQL 15+ — the other providers reject both. Under an active global query filter the ON CONFLICT / ON DUPLICATE KEY key upserts refuse with NotSupportedException (fail closed; IgnoreFilters() restores them): a source key colliding with a hidden target row still surfaces the provider's native unique-constraint error from the insert arm — for whichever unique index it hits, not only the merge key (an existence oracle; a composite key including the filter column mitigates the primary/merge key only) — while an update-only or delete-only merge is a silent no-op, and the source-value pre-check stays a separate TOCTOU read (QueryFilterValidator) that validates source values, not target rows (see Write-target isolation). A multi-table DELETE based on an INNER join (From<T>().Join(...).Delete()) is rejected by SQLite and ClickHouse, a multi-table UPDATE based on an INNER join (From<T>().Join(...).CreateUpdateJoinBuilder()) by ClickHouse, MariaDB's UPDATE ... RETURNING is available only at an explicitly configured 13.0+ (SupportsUpdateReturning), and a ClickHouse ALTER TABLE ... UPDATE reports no affected-row count. The temporary form of CREATE TABLE ... AS SELECT (ToTempTable) is available on PostgreSQL, SQLite, MySQL and MariaDB only: SQL Server has no CREATE TEMPORARY TABLE ... AS SELECT (use a session-scoped ToTable("#name")) and ClickHouse has no temporary AS SELECT. ClickHouse rejects a batch with NotSupportedException, a DML batch step may not request returned rows (Returning() produces a different builder type), and batch execution is not routed through the query interceptors: their events carry a DbCommand, while a DbBatch command is a DbBatchCommand. There is no change tracking and no SaveChanges: writes are explicit commands.
Server-version-gated syntax nextorm never probes the live server, so a version-dependent capability is available only when its version is explicitly configured. PostgreSQL's aggregate FILTER (WHERE ...) needs 9.4+: a dialect pinned below that rejects it with NotSupportedException, while an unset version silently assumes 9.4+ and enables it. MariaDB's UPDATE ... RETURNING is enabled only at an explicit 13.0+ (SupportsUpdateReturning); unset or older rejects it with NotSupportedException. MariaDB's months_between (12.2.1), to_date (12.3), to_number (12.2 family, enforced 12.2.1), gen_random_uuid → uuid_v4() and uuidv7 → uuid_v7() (both 11.7) are gated the same way: an unversioned context refuses them at translation/preparation instead of emitting SQL the server rejects, and no platform equivalent (TIMESTAMPDIFF, STR_TO_DATE, a decimal cast, UUID()) is substituted. PostgreSQL's array_reverse/array_sort/uuidv7 are a targeted exception to the permissive unset default: array_reverse exists in no current release and array_sort/uuidv7 arrived only in 18, so all three are refused at preparation (The PostgreSQL function 'array_reverse' is not supported by this provider., the same message naming array_sort, and The uuidv7 function is not supported by this provider.) when the configured version is below 18 or unset; no NULL, unchanged array or v4 UUID is substituted, and no other unset-version gate changes. PostgreSQL enforces one immutable server version per concrete context type for the process lifetime, because FILTER affects the cached SELECT SQL; a different version for the same concrete type throws InvalidOperationException, so a process that needs several PostgreSQL versions declares a distinct context subclass per version. See PostgreSQL provider and MariaDB provider.
Recursive CTE — SQL Server requires UNION ALL A WithRecursive declaration whose body is a distinct UNION (anchor.Union(step)) is rejected by SQL Server ("does not contain a top-level UNION ALL operator"), so nextorm fails closed while the query is prepared — before execution and before the plan cache — with an actionable NotSupportedException. Migrate to UnionAll or the typed AsRecursiveCte API. See CTE.
Prepare() / plan cache for DML Every mutation renders and executes one command per call — mutating builders are deliberately not prepared or plan-cached. Optimisation in nextorm targets read-only queries only (explicit Prepare, the implicit plan cache, benchmarks).
Bulk insert — remaining boundaries BulkInsertMode defaults to Auto: it prefers the native path only when every supplied option and preflight prerequisite is compatible, and otherwise takes the portable INSERT ... VALUES path only when that path honours the request; Native refuses by name instead of falling back (SQLite has no native path) and Portable refuses a native-only option by name instead of ignoring it. BulkInsertOptions.ServerName is not available — a bulk insert never reroutes to another server — and DatabaseName is dialect-scoped (PostgreSQL addresses only the connection's current database; the other providers render it per their own qualification). Of the bulk-copy flags TableLock/FireTriggers stay SQL Server-native only, CheckConstraints=false is honoured only where a scoped control exists (SQL Server native, MariaDB, SQLite outside an active transaction), and KeepNulls=false is accepted only for a default-equivalent shape (every written column nullable with a NULL/absent SQL default) — every other request is refused, never accepted and left inert. Returning* always writes through the portable path and is refused on MySQL, MariaDB and ClickHouse. Timeout is one whole-invocation TimeSpan budget on every path, never reset per batch; an expired budget is a TimeoutException and a caller cancellation always wins. Creating or dropping a temporary table and any whole-load, rollback, throughput or per-row-commit guarantee remain out of scope. No blanket "every limit is supported" claim is made for any provider. See Bulk insert.
Implicit navigation queries / joins from navigation properties There is no convention over foreign keys and no implicit join inference: a relationship must be declared explicitly (HasMany/HasOne/HasOneToOne/HasManyThrough/[Relationship], see Relationships), and the fluent helpers declare a single key column — a composite-key relationship must come from relationship metadata that lists the components. IRelationshipMetadata.Inverse is resolved lazily on its first read and cached, so register both sides before reading it. Adapter LINQ composition, a bare reference-adapter projection and predicate overloads fail closed, and a collection navigation exposes only four direct terminals (Any/Count/LongCount/the Count property); Count/the Count property are a checked int (the 64-bit count is narrowed at materialisation and throws OverflowException out of range), while LongCount is 64-bit end to end. A collection reached through a reference is rejected by ClickHouse, which cannot correlate a subquery on a joined source and throws NotSupportedException (an accepted provider limitation, not a defect). Navigation over a temp-table/TVP source is not available (fail-closed): a lazy temp-table read source is an untyped TableAlias, so a navigation member cannot be read over it — there is no base-table fallback and no exception. A table-valued parameter (TVP) has no query root: it is parameter-only, bound through ProcedureParameter.Table<T> on a raw command or stored procedure, and there is no From<T> TVP source. LoadWith nests to level 2 and deeper; unexpressible nested shapes, a conflicting nested mode, a cyclic graph and the projection arity cap fail closed with NotSupportedException (see Eager loading). JoinInto child sources support Where only; other query modifiers throw NotSupportedException before execution. JoinInto rejects with NotSupportedException an undeclared relationship (unless the explicit-key overload is used), a derived (As) or joined projection source as the builder it is declared on, a join kind other than Inner/Left, mismatched explicit key types and more than seven child collections; mixing JoinInto with any other join on the same builder is rejected (declare it on a plain entity source only), a ClickHouse j => j.WithStrictness(Any) option truncates the loaded collection to one child, and relationship metadata fails closed on an unmapped foreign key, a keyless principal or an FK/principal-key type mismatch. Deduplication needs a mapped key: parent dedup and child identity fall back to reference identity when the respective side declares no key, and on a SQL provider every row is a fresh instance, so repeated/cartesian rows then survive — give the parent and child a mapped key ([Key]/Key()/the Id convention) or use the explicit-key overload with a keyed child.
Collection-valued projection slots — v1 boundaries A projection may return the per-parent collections of a declared relationship (Select(p => new { p.Id, p.Comments }), or the p.Comments.AsEntityBuilder<Comment>() … ToList()/ToArray() adapter with Where/OrderBy/OrderByDescending/ThenBy/ThenByDescending/Limit/Select). The projection is computed in memory over fully materialized entities: SQL selects the entity rows, so there is no SQL column pruning and no pushed-down DTO column, and the adapter's Where/Limit never reach the statement (a Limit pages each parent, not the whole result). Everything outside that operator set — GroupBy, Distinct, Union/Except/Intersect, joins, Offset — is refused by name before any SQL, as are explicit grouping composed with the projection, an outer terminal other than ToList/ToArray, the in-memory provider, and a destination outside List<T>, T[], IEnumerable<T>, ICollection<T>, IList<T>, IReadOnlyCollection<T>, IReadOnlyList<T> (no substitution, 0 statements and no plan entry). M:N + Limit pages each parent over its surviving junction occurrences (a repeated junction row occupies its own slot, a dangling link is excluded from the count and from the page) and is trimmed after the occurrences are loaded — it costs the same statements and selects the same columns as an unpaged many-to-many slot. A projected reference with more than one distinct identity for one parent throws InvalidOperationException at materialization before any row is projected, while repeated physical rows of one identity collapse; a missing reference is null and absent children an empty collection at every level. On ClickHouse two projected sources that expose the same property name in one statement are rejected by the server (MULTIPLE_EXPRESSIONS_FOR_ALIAS) — give them distinct projected names. See Collection-valued projection slots.
Reference navigation inside a correlated predicate — supported; correlated eager loading — not A declared reference navigation used inside a correlated EXISTS/IN body or a correlated scalar subquery is supported: the hop is joined on the outer command and the nested command reads the joined alias, emitted as a portable LEFT JOIN plus a correlated subquery (no APPLY/LATERAL on SQLite or ClickHouse), at hop 1 and hop 2+, for scalar comparison, bare presence and scalar projection. This is a predicate/scalar form only — it adds no collection capability and no new join form, and ClickHouse keeps rejecting a collection reached through a reference. Correlated eager loading is a different surface and stays unsupported: EagerLoadMode.Correlated is reserved and rejected with NotSupportedException during preparation — before SQL generation and before a plan entry — so no per-parent correlated graph is loaded. See Implicit navigation queries.
One-to-one foreign-key uniqueness (declaration-time trust) The uniqueness of a one-to-one child foreign key is declaration-time trust — the core neither inspects the database schema or the data nor emits a uniqueness constraint, so a non-unique foreign key is accepted when the relationship is declared (HasOneToOne or a local JoinOptions.OneToOne, see Relationships). The only guard is a per-parent runtime backstop in JoinInto: two distinct non-null child identities for one parent throw InvalidOperationException at materialization, while repeated identical rows and keyless/null-identity repeats are tolerated (the first child wins). Schema/unique-index validation and an optional metadata-build rejection are out of scope (a separately agreed feature).
Selecting a joined entity as a whole After a join the accumulated projection is positional (Item1..Item8, one item per source in join order), so the sides carry no semantic names until you project into a named type; a wildcard SELECT * is never generated, comparing whole entities in a predicate and casting an item to an unrelated result type are not supported. See Joins.
Bare derived-source materialization depends on the projection's expression shape A subquery/derived source (From(query)/From(builder)) is read through an explicit outer Select; a projection-less read (From(inner).ToList()) is accepted only when the inner select list is already a set of resolved column references (a mapped column, a renamed projected member, a computed scalar addressed by its output name). A projection that needs re-translation — an anonymous new { x.Id } or a MemberInit/constructor DTO new Dto { Id = x.Id } — is refused during preparation with QueryPreparationException ("…without an explicit outer Select…"). This is an expression-shape restriction, not an anonymous-type requirement, and DTO projection support on a direct read does not imply bare derived-read support: add the outer Select (for example Select(x => x.Id)). This rewrite works with member-init DTO and positional-record projections; the tested constructor-only class projection has a known pre-existing failure (Cannot find inner column Id), so use one of those supported forms instead. See Subqueries and Query sources.
Named join aliases and the root alias The named path is expression-only and SQL-providers-only: the in-memory provider fails closed with NotSupportedException on any alias (a join alias or a root alias, mixed chains included), and a generated alias member read outside an expression tree throws NotSupportedException by design. JoinInto (the single-query relationship loader) has no alias surface and never takes an Alias.<Name> argument. The source-less (type-only) form carries an alias too — .Join<TJoin>((left, right) => …, Alias.X[, options]) on the root EntityBuilder<T> and on the JoinedEntityBuilder receivers arities 2–7 (resulting slots 2–8) — and it is the only join alias form that accepts join options; CrossJoin/CrossApply/OuterApply take no condition and therefore stay source-explicit-only, a raw-table source is not resolvable by type, and MySQL/MariaDB still refuse FullJoin in every form with NotSupportedException (Full join). The flat typed chain stops at eight slots (Projection<T1..T8>) — a scope bound, not a compile-time rejection, because the inherited members of the generic base still compile; project the accumulated join into a named type with As<T> to continue past eight tables. .WithAlias is root-only (after a join or a second root alias it is a NORMGEN008 compile error and a runtime fail-closed guard), and a chain whose root or an intermediate joined step projects an anonymous type is rejected with NORMGEN009 — the last joined step may stay anonymous. See Joins.
Correlated APPLY / LATERAL — provider support and the source cap A correlated CrossApply/OuterApply after a join is supported on SQL Server (cross apply / outer apply), PostgreSQL and MySQL 8.0.14+ (cross join lateral / left join lateral … on true) on a flat 3–8 total source chain (2–7 preceding sources plus the applied one), on both the positional JoinedEntityBuilder<T1..Tn> receivers and the generated alias receivers, with EntityBuilder<T> or QueryCommand<T> lambda returns and an optional Action<JoinOptions>; a ninth correlated source is refused with NotSupportedException while the query is prepared — before the applied source is evaluated — instead of nesting the projection. MariaDB 11.4 refuses both kinds before SQL execution: it has no lateral derived table, so its dialect reports SupportsApply == false and the capability guard throws during preparation, before any DbCommand reaches the connection. SQLite and ClickHouse are refused the same way, and the in-memory provider rejects APPLY outright. See Joins.
SelectMany / GroupJoin on SQL providers SelectMany/GroupJoin throw NotSupportedException; the explicit CrossApply/OuterApply join surface covers the correlated equivalent (see Joins).
Correlated scalar terminals (First/Single and their *OrDefault forms) Single/SingleOrDefault over more than one row throw on every provider whose scalar subqueries enforce cardinality (PostgreSQL, SQL Server, MySQL, MariaDB, ClickHouse); SQLite does not enforce it, so a numeric Single/SingleOrDefault over more than one row raises a database error through a rendered case when count(*) > 1 then <numeric overflow> else value end guard, and a non-numeric projection is rejected with NotSupportedException. First/Single on a non-nullable value projection throw when no row matches.
Locking table hints outside SQL Server A locking table hint (WithTableHint("nolock") → WITH (NOLOCK)) is applied on SQL Server only, on the query's FROM table or on a single join through JoinOptions.WithJoinTableHint; the native index hints of MySQL/MariaDB (USE/FORCE/IGNORE INDEX), SQLite (INDEXED BY/NOT INDEXED) and SQL Server (WITH (INDEX(...))) are a separate surface (WithIndex, ISqlDialect.IndexHints). SQLite and ClickHouse reject Hint(...) with NotSupportedException, and the same gates apply to join (JoinOptions.WithJoinHint), subquery (FromOptions.WithSubQueryHint) and tables-in-scope (WithTablesInScopeHint) hints; SQL Server additionally rejects a subquery hint, because T-SQL cannot append one to a subselect (see Query hints).
MariaDB has no source-level row lock (FOR UPDATE OF) A source-level lock (FromOptions.ForUpdate/FromOptions.ForShare, JoinOptions.ForUpdate/JoinOptions.ForShare) requires a dialect whose ILockRenderer.SupportsSourceLocks is true (PostgreSQL, MySQL, SQL Server). MariaDB's grammar has no FOR UPDATE OF/FOR SHARE OF, so a source-level lock is rejected deterministically with NotSupportedException (fail-closed): it is never silently ignored, replaced by a whole-query lock, or emitted as an unsupported OF. SQLite and ClickHouse have no row locking at all and reject both a whole-query and a source-level lock.
SQL Server whole-query lock hints every source A whole-query ForUpdate/ForShare places the with (updlock)/with (holdlock) hint on every physical FROM/JOIN source; a source-level lock keeps the hint on the chosen source.
SQL Server WITH TIES with a positive OFFSET T-SQL accepts WITH TIES only in TOP(n), never in OFFSET … FETCH, so the combination is refused while the query is prepared — before any SQL is rendered, sent or cached — with NotSupportedException (SQL Server does not support WITH TIES with a non-zero OFFSET. Use WithTies() without OFFSET.). There is no emulation and no silently dropped tie expansion: use WithTies() without an OFFSET (the TOP(n) WITH TIES form) or ordinary paging without ties. See Sorting and paging.
Explicit NULL placement where the CASE emulation cannot repeat the key NullsPositionFirst/Last is native on SQLite, PostgreSQL and ClickHouse but emulated on SQL Server, MySQL and MariaDB with CASE WHEN <key> IS NULL … END ASC, <key>, which repeats the ordering key. There an explicit placement on a DISTINCT/DISTINCT ON query — or together with ClickHouse-style LIMIT BY — fails closed with BuildSqlCommandException before execution, because the repeated key is not a select-list expression; the placement is never silently dropped. A provider with native support repeats nothing, so DISTINCT, DISTINCT ON and LIMIT BY keep the native modifier there. Use NullsPosition.Default or a native provider on those shapes. See Sorting and paging.
Upper-case SQL keywords — tuple/row constructors stay provider-canonical With KeywordCase.Upper (DataContextBuilder.UseKeywordCase, EntityBuilder<TEntity>.WithKeywordCase, QueryCommand<T>.WithKeywordCase) every core, translator, dialect and capability keyword is upper-cased, but the row/tuple constructors ROW(...) (PostgreSQL) and tuple(...) (ClickHouse) are treated as function-like constructors and keep the provider's canonical spelling. Identifiers, string literals, function names, type names and raw SQL are never affected.
Statement layout is not configurable Every generated SELECT — the top level and nested forms (a derived-table FROM, a CTE body, the SELECT inside INSERT ... SELECT/CREATE TABLE ... AS SELECT) — starts its FROM and each JOIN on a new line, unconditionally: there is no opt-in flag, no indentation level and no blank lines. Generated SQL uses \n only — never \r — on every provider and every OS. Raw SQL you pass through (WithSql, WithTableExpression, ExecuteRaw, Raw, procedure text), string literals and parameter values are never reflowed: nextorm does not parse or reformat opaque input.
Server-side array/JSON element access (x[i]) Only a captured (closed-over) collection indexed inside the query is supported (captured collection lookup): a constant key folds to a parameter and a column key renders a portable CASE. Element access on a server-side array/JSON column or an array-returning expression (groupArray(x)[0], array_agg(x)[1], json['k']) has no portable SQL form and throws NotSupportedException (a custom indexer likewise) instead of binding the whole collection. The native forms differ (arr[i] on PostgreSQL, arrayElement(arr, i) on ClickHouse, JSON paths elsewhere), so they would need a separate dialect hook.
ClickHouse native JSON column type The native JSON column type is object-rooted: an array or scalar root fails closed, and a bare string read over a JSON column is not supported — nor is the legacy Object('json') alias, which needs a connection-level allow_experimental_object_type that nextorm never sets (and even then reads materialize as Tuple<SByte,String>). A JSON projection must be a named shape (Select(x => new { x.Doc }) or a DTO), because a bare top-level scalar Select(x => x.Doc) is not handled by the core projection classifier. See JSON support.
MySQL/MariaDB JSON columns and JsonColumnStorage.Native nextorm does not generate column DDL: a json column must already exist, declared by you. MySQL 8 has a real json type and MariaDB's JSON is a LONGTEXT alias with a json_valid() CHECK (reported as longtext); both are supported through the text storage (Auto/Text), because MySqlConnector cannot bind JsonElement/JsonDocument/JsonNode (MySqlDbType.JSON accepts strings only) and always reads the column back as string. An explicit Native is refused on both engines with a NotSupportedException naming the dialect, before any command runs. MySQL compares a json document as a typed value — a string operand does not equal it — and the Text storage does no equality translation. See JSON support.
SQL Server native json column — scalar roots and the JSON null document SQL Server 2025+ has a distinct json type, but a native json column accepts object and array documents only: a scalar root (string, number, boolean) and the JSON null document are rejected by the server with error 13609, at execution. That is a server limitation, not a library refusal — nextorm adds no client-side rule — so use the text storage (Auto/Text) for such payloads. The native type is reachable only from a context that declares major version 17 or higher, or an Azure/Fabric SQL database engine edition (SqlDatabase, AzureSqlManagedInstance, MicrosoftFabricSqlDatabase); the default unversioned, editionless context and 2016–2022 contexts stay text-only, and Native there throws the dialect-named NotSupportedException before any command runs. nextorm generates no column DDL, so moving a column to json is a migration step. See JSON support.
Azure SQL DB/MI/Fabric — native json requires an explicit edition declaration, and is not live-validated here Azure SQL Database, Azure SQL Managed Instance and Fabric SQL database run the SQL Server engine and support the native json type, but report SERVERPROPERTY('ProductMajorVersion') = 12, so a version argument cannot identify them. Declare the engine edition instead — SqlServerEngineEdition.SqlDatabase (5), AzureSqlManagedInstance (8) or MicrosoftFabricSqlDatabase (12) — and the capability opens without any server probe. Edition 11 ("Azure Synapse serverless SQL pool, or Microsoft Fabric") is not edition-capable; a version-17 declaration still opens the gate through the unchanged version rule. Declaring capability migrates nothing: Text/Auto keep the nvarchar transport. These Azure/Fabric declarations are unit-tested only — no live Azure/Fabric instance was available for this validation — and the statement does not extend to every Fabric workload. Tracked in #345.
untuple (ClickHouse) untuple(tuple) is not mapped: it expands a tuple into several result columns, which has no single scalar CLR shape in a projection. Project the Tuple(...) as System.Tuple<...> and read its elements with tupleElement/System.Tuple<...>.ItemN instead.
Row-value (tuple) surface: remaining restrictions Relational operands (</>/<=/>=) and relational ValueTuple operands are inexpressible in C#/expression trees (a language boundary, not a product limitation). On MySQL/MariaDB/SQLite a row constructor in Select/ORDER BY/GROUP BY or as a function argument, and any server-side .ItemN, throws NotSupportedException at preparation (naming the provider); SQL Server rejects constructors and every tuple IN/Contains form up front with NotSupportedException (SQL Server does not support tuple IN/Contains translation.), including a null collection, a null reference-tuple entry, arity ≥8 and nested tuples, so no evaluation error can surface there. On the supporting providers a nullable tuple element (List<(int, int)?>) throws NotSupportedException, arity ≥8/Rest/nested tuples throw NotSupportedException, and a tuple-typed QueryCommand (subquery) right-hand side is deferred (not supported yet, no future milestone promised). Materialising a raw ROW(...)/row value is PostgreSQL-only: ValueTuple, arity ≥8, a nested or empty ROW, several or mixed record columns, an unregistered named composite and a named composite declared as System.Tuple remain guarded with NotSupportedException (see Raw SQL and Scalar functions). A top-level generic ValueTuple.Create(...) factory call and an explicit new ValueTuple<...>(...) construction are unsupported projections in their own right: both are refused during preparation with a NotSupportedException that names the projection type and the Select position, before any SQL is rendered or a database is touched — project an explicit DTO or anonymous shape instead. Tuple.Create is not a universal substitute (it keeps the provider/context restrictions above), and no nested tuple support is claimed. See Projections.
Binding the element of several ARRAY JOIN expressions at once (ClickHouse) ArrayJoinElement/LeftArrayJoinElement bind the element of exactly one array expression to p.Element: a second call, a call on a joined query, or a call after Where/Having throws InvalidOperationException. When you array-join several arrays and do not need the typed element, use ArrayJoin/LeftArrayJoin; these array-join members are extension methods in NextORM.ClickHouse (using NextORM.ClickHouse;).
ClickHouse aggregate-function state (-State/-Merge, runningAccumulate) uniqState/sumState/countState/… return an opaque AggregateFunction(...) value, which nextorm does not model: ClickHouse.Driver 1.4.0 throws AggregateFunctionException from FrameworkType/Read/Write, so such a column cannot be materialised into or passed as a CLR value (the driver message points at the matching -Merge). uniqMerge(uniqState(x)) is itself illegal (ILLEGAL_AGGREGATION), so a -Merge needs a state produced server-side (a subquery or a state table), and runningAccumulate is deprecated in ClickHouse 25.8 (DEPRECATED_FUNCTION, needs allow_deprecated_error_prone_window_functions).
ClickHouse-only math functions (log2/cbrt/exp2/exp10) SqlFunctions.ClickHouse exposes log2/cbrt/exp2/exp10 (each with a double and a double? overload, rendered log2(x)/cbrt(x)/exp2(x)/exp10(x), Float64/Nullable(Float64), a NULL argument stays NULL). They are ClickHouse-only: PostgreSQL, SQL Server, MySQL, MariaDB, SQLite and the in-memory provider reject a call with NotSupportedException instead of emulating it, and the Math.*/BCL/CommonFunctions routes were deliberately not expanded. See Math functions.
String formatting and collation beyond the documented subset string.Format/interpolation/value.ToString(format) translate only the culture-invariant subset: N/F/D/X numbers (per provider) and the yyyy yy MM dd HH mm ss date tokens with the separators - / . : T and space. Any other specifier, and any culture other than CultureInfo.InvariantCulture, throws NotSupportedException. The ordinal StringComparison overloads accept only Ordinal/OrdinalIgnoreCase (InvariantCulture/CurrentCulture throw); SQLite additionally rejects the case-sensitive ordinal Contains/StartsWith/EndsWith because its LIKE is always case-insensitive regardless of collation (SupportsOrdinalLike). See Ordinal comparison and collation.
Null-safe captured comparison — exact scope ==/!= between a nullable value-type or string column and a captured nullable/string variable follow C# null-equality in both operand orders (null == null true, null != null false, exactly one null makes == false / != true) and are SQL-null-total; </>/<=/>= keep plain comparison. HasValue/!HasValue on a mapped Nullable<T> render is not null/is null. These forms are supported only in predicates (WHERE, correlated Exists), boolean SELECT projections (materialised as non-nullable bool; SQL Server bit) and JOIN ON, where only the ON expression changes. Not rewritten and not null-safe: nullable column ↔ nullable column (in WHERE, SELECT or JOIN ON), ORDER BY, GROUP BY, window PARTITION BY/ORDER BY, ClickHouse LIMIT BY, Nullable<T>.Value, a hand-built DefaultExpression, and the automatic column↔column/distinctness rewrite — an explicit two-valued result comes from SqlFunctions.Sql.is_distinct_from/is_not_distinct_from (see the next row). A string comparison uses the provider's ordinary collation; no CLR collation is promised. On ClickHouse the JOIN ON shapes that render a bare IS NULL — column == null and !nullable.HasValue, in an INNER or a LEFT join — are refused with a named NotSupportedException at query preparation, before any SQL is rendered or sent (verified on ClickHouse 25.8.33.6, the 25.8-alpine fixture image): the message names the server behaviour — the residual is pushed to the left join input instead of preserving the ON semantics. Use a captured-value comparison instead; do not move a LEFT JOIN predicate to WHERE, which is not equivalent (it filters the padded rows instead of matching them). Captured comparisons and HasValue in ON do execute and match, and an inner != renders is not null. ClickHouse materialises a boolean predicate as UInt8 and surfaces it as bool, so a boolean SELECT projection there is a genuine non-nullable bool.
Explicit null-safe comparison (IS [NOT] DISTINCT FROM) — emulation and operand rules SqlFunctions.Sql.is_not_distinct_from/is_distinct_from are the explicit opt-in and return a non-nullable bool: both NULL operands are equal, exactly one NULL is unequal, otherwise the provider's ordinary comparison applies, so ordinary ==/!= keep their SQL NULL semantics and are never rewritten. Only PostgreSQL (IS [NOT] DISTINCT FROM), SQLite (IS/IS NOT) and MySQL/MariaDB (<=> and NOT (<=>)) are native; SQL Server and ClickHouse are emulated as a guarded predicate ((a is not null and b is not null and a = b) or (a is null and b is null), ordered differently on ClickHouse), with the distinct form as its not (...) complement — the SQL Server version floor stays free of the 2022+ IS [NOT] DISTINCT FROM operator by emulating it, while ClickHouse's native spellings are unusable outside a JOIN ON section (server error Code 48 NOT_IMPLEMENTED), so nextorm never emits them. Operand typing is metadata-only: on SQL Server a captured null operand needs a mapped or converter-derived SQL type and an unmapped one is rejected before execution (Null-safe comparison requires a SQL Server type mapping for null operands.), incompatible operand types are rejected with Null-safe comparison requires compatible operand types., and a nullable reference annotation is not a runtime guarantee — a reference declared non-nullable that holds NULL still takes the null-safe path. In memory the truth table is the same (EqualityComparer<T>.Default for non-null operands), but CLR string equality is not a database collation, so cross-collation equality is not promised. See Null-safe comparison.
DateTimeOffset offset is not preserved outside SQL Server Only SQL Server's native datetimeoffset preserves the value's offset: PostgreSQL stores an instant (timestamp with time zone) and materialises a canonical offset-zero value, MySQL/MariaDB (datetime) and ClickHouse (DateTime64(6)) do not keep the offset as part of the value, and SQLite stores a UTC instant as text (millisecond precision). Members with no server translation (.Offset, .UtcDateTime, .LocalDateTime, .Date, .Ticks, .DayOfWeek, .ToOffset(...), .AddTicks(...), .Add(TimeSpan)) fail closed with NotSupportedException. See Date and time.
Bare scalar projections of enum and DateTimeOffset values A relational projection whose result type is an enum without a value converter — Select(it => it.State), Select(it => it.MaybeState) and the generated Min/Max bodies — is refused during preparation with NotSupportedException (Enum scalar projections without a value converter are not supported in a server-side query.), while a converted-enum scalar stays supported. The DateTimeOffset members with no server translation fail closed on every relational projection form, including a bare scalar (Select(v => v.Created.DayOfWeek), .First()), instead of surfacing as a row-materializer constructor error. Both refusals are gated on row mapping (IContextEnvironment.NeedMapping), so the in-memory provider's client-side materialization is unchanged. See Projections and Date and time.
Column-level collation on ClickHouse and DDL ClickHouse has no COLLATE clause and rejects a collated column with NotSupportedException, and nextorm never emits a collation in a column definition (its only DDL is CREATE TABLE ... AS SELECT, which carries no explicit column collation), so a declared CollationAttribute / Collation(...) describes the collation of an existing column and does not create it. See Column collation.
Regular expressions The pattern, replacement and options must be compile-time constants, only RegexOptions.IgnoreCase maps, and the pattern executes on the provider's own engine (POSIX/ICU/PCRE/RE2/.NET), so complex patterns (lookaround, backreferences) and replacement group references are not portable. SQL Server 2022 and earlier have no REGEXP_* functions at all, and the match (REGEXP_LIKE) additionally requires a 2025+ server at database compatibility level 170. See Regular expressions.
Stored procedures and dynamic SQL Assembling SQL at runtime is outside the builder surface, and stored procedures are not supported on SQLite and ClickHouse, which throw NotSupportedException; elsewhere they are invoked as raw EXEC/CALL text through ExecuteRaw, or by name through ExecuteProcedure/ExecuteProcedureAsync (see Raw SQL).
Multiple result sets from a batch The result sets are buffered eagerly — there is no streaming and no random access — and only the single-result BatchQuery<TResult> terminal streams (ToAsyncEnumerable); the positional Read<TResult>() path and the cursor-based per-set Read<T>() traversal cannot be mixed. ClickHouse rejects batches with NotSupportedException. See Executing statements in one batch.
SQL Server OUTPUT ... INTO batch capture — scope and limits BatchBuilder.OutputIntoTableVariable(...) captures an INSERT/UPDATE/DELETE/MERGE returning projection into a same-batch table variable, read back by FromOutput<TResult>/FromOutputTable inside that batch only: the variable has no standalone or composable existence, so a source read outside its owning batch fails closed with NotSupportedException (batch output source), and a capture must be registered earlier on the same builder. The caller's columnDefinitions are trusted SQL embedded verbatim and must match the returning projection by position and count; nextorm infers no column schema, no transaction boundary and no row order (add OrderBy). The capture target is a table variable, not a real table (use the fluent OutputInto(target) for that), and SQL Server's own OUTPUT INTO restrictions still apply — an enabled trigger, a foreign key or a check constraint on the target makes the statement fail with the provider's error, with no nextorm workaround. PostgreSQL keeps its data-modifying CTE route and this API refuses it; only SQL Server advertises SupportsOutputInto, and every other provider throws NotSupportedException. See Batch-scoped capture.
External sources (OPENROWSET/OPENQUERY, linked servers) Out of scope; the connection's own schema is queried. The per-query WithServer/WithDatabase overrides only change the name rendered in FROM (up to SQL Server's four-part server.database.schema.table); opening a linked server or a cross-database connection remains the caller's/DBA's concern.
Per-query source override beyond the provider's levels A dialect renders only the levels it can express: PostgreSQL a schema, SQLite a schema/attached database, MySQL/MariaDB/ClickHouse a database, SQL Server up to the four-part name. WithDatabase/WithServer on a provider that did not opt into the level is rejected with NotSupportedException rather than silently dropped, and WithTableExpression (raw SQL as the table source) cannot be combined with the name qualifiers. See Per-query source overrides.
ClickHouse format/merge/input table functions Not pre-declared on SqlFunctions.ClickHouse: format and merge infer their schema at runtime and input is valid only in INSERT ... SELECT, so none has a caller-declared TRow shape. Use FromSql or a user-declared TvfEntityBuilder<T> factory (Result schema and provider restrictions).
PostgreSQL json_populate_record(set) The json_populate_record/json_populate_recordset variants populate a caller-supplied base record type instead of a free-form column list, so they need a mapped composite per provider and are not exposed. For a schema-less scenario use a [DynamicColumns] store, the raw reader, or jsonb_each* (see Dynamic record schema).
DDL / administrative commands Beyond CREATE [TEMPORARY] TABLE ... AS SELECT, DROP TABLE IF EXISTS (via CreateTableOptions.DropExisting) and TRUNCATE TABLE, no DDL is generated: arbitrary CREATE/ALTER/DROP (indexes, constraints, column changes), OPTIMIZE, RENAME, table variables and other administrative statements are not supported — use a batch Raw(sql) step, ExecuteRaw, or your own commands. See Create table as and Truncating a table.
Inheritance / TPH nextorm maps one CLR type to one table and carries no discriminator, no runtime type switch and no OfType, so the relational shape of a hierarchy — a discriminator column (manual TPH), a table per concrete type, a shared base table, or a JSON/dynamic side payload — is your decision, and a mixed read is a DTO projection over UNION ALL, a view, or per-type queries. See Inheritance and derived entities.
Type tests (is in LINQ) Rejected with NotSupportedException at query preparation instead of being mistranslated into the bare operand's condition. There is no discriminator or runtime type switch; see the inheritance limitation. Use explicit mapped-column predicates.
Adjacent platforms and tooling (not planned) Several neighbouring workstreams are deliberately outside nextorm: schema scaffolding / code generation — mappings are declared in code ([Column]/[Table], the fluent builder, relationships) and nothing is reverse-engineered from a live schema; additional ADO.NET providers — the shipped set is PostgreSQL, SQL Server, SQLite, MySQL, MariaDB and ClickHouse (Oracle, Firebird, DB2, SAP HANA, Informix, Sybase, Redshift, DuckDB, YDB, Access and SQL CE are not planned); F#-specific mapping (option, single-case discriminated unions), the LINQPad driver and a remote/gRPC context; a SQL-inlining switch — deliberately parameterised only; an inline mode is not planned (see cache controls); and a typed compiled-query delegate — Prepare covers compile-once and reuse, but it is a stateful single-threaded command with positional parameters, not a thread-safe typed Func<...>; the implicit plan cache is the per-thread alternative, and the typed concurrent delegate is not planned.
SQLite FTS index maintenance The maintenance command builder (IDataContext.CreateSqliteFts5CommandBuilder(tableName) → AutoMerge/CrisisMerge/Merge/Optimize/Rebuild/IntegrityCheck) is SQLite-only: on every other provider the maintenance surface throws NotSupportedException before any database access, an invalid target — a null, empty, whitespace or NUL-character table name — is rejected, and a contentless FTS5 Rebuild fails natively. FTS3/4 Rank expects a connection-registered rank UDF, and the virtual table itself is created with raw SQL. These are command-surface statements — not scalar/table-valued functions and not part of SqlFunctions.Sqlite.
Unit of work, SaveChanges and change tracking / CDC nextorm does not own a unit of work or track entities, and there is no SaveChanges: writes are explicit commands, concurrency must be checked explicitly (token in WHERE + affected-row count), and a change-tracking layer has to be built on top — see Optimistic concurrency and change tracking. Transactions are supported — including named savepoints through ITransactionManager, where SQL Server's release is a data-preserving no-op — except on ClickHouse, which has no transaction and rejects one.
EF Core integration: SaveChanges bridge and per-context mapping There is no change-tracking sync and no SaveChanges bridge: nextorm DML bypasses EF's change tracker, so rows it wrote are unknown to the tracker, and a bridge is explicitly out of scope. The mapping is registered in nextorm's process-wide metadata cache keyed by Type, so at most one mapping per CLR type exists per process — two DbContexts that map the same type differently cannot both be served: the second, different registration throws InvalidOperationException. The non-relational EF InMemory provider has no DbConnection and is not supported. A bridge-bound context whose imported filter metadata is later dropped (DataContextCache.Clear() or a sliding-expiration eviction) fails closed with InvalidOperationException on the next query or DML build instead of running unfiltered. The ClickHouse no-EF-adapter slice and the MariaDB-specific shared-connection certification remain open units in 1.0.9-b. See EF Core integration.
Command timeout on ClickHouse UseCommandTimeout/WithCommandTimeout set the ADO.NET DbCommand.CommandTimeout on every SQL provider, but ClickHouse.Driver 1.4.0 does not map it to the HTTP request — the connection Timeout (default two minutes) bounds the request instead — so the value has no effect there. A per-query override applies to read queries; DML commands and batches use the context default.
PostgreSQL oid and other uint-backed system types through a uint parameter Npgsql maps oid and other system types backed by uint (for example cid) to CLR uint, so reading one into a uint property works, but nextorm binds a uint parameter as xid, and oid = xid has no operator (SQLSTATE 42883) — comparing or writing such a target through a uint parameter therefore fails. Compare through a bigint cast (Where(e => (long)e.Oid == (long)value)) or run raw SQL with a bigint parameter and an explicit ::oid cast. See PostgreSQL provider.
PostgreSQL types absent from the Npgsql type catalog Composite classification is authoritative: a column is treated as a named composite only when the loaded Npgsql type catalog says so, never from a dotted data type name or an exception shape. On a clean catalog an unmapped non-composite type (for example hstore or ltree) yields the ordinary "None of the result-set columns (...) matches a mapped property" error rather than the named-composite diagnostic, and a genuine composite whose type is not in the loaded catalog degrades to the same ordinary error until authoritative type metadata is available (a deliberate trade-off — a cold catalog gives no composite verdict instead of a misleading one). A composite must be registered through MapComposite<T> before it can be read, and a type reload must target the process-wide implicit Npgsql data source associated with the same connection string as the context.
Interceptors beyond the command/connection lifecycle There is no exception interceptor — translate a provider exception with a try/catch around the terminal call — and an interceptor must not change CommandText (the command belongs to a cached plan). Global query filters are a separate layer. See Interceptors.
Dynamic columns - write side The read side is implemented: a writable property assignable from Dictionary<string, object?> marked with DynamicColumnsAttribute (or the fluent EntityPropertyBuilder<T>.DynamicColumnsStore) receives the row's unmapped columns when the entity is queried as a whole - the statement renders select <mapped>, * (select t1.<mapped>, `t1`.* on MySQL/MariaDB, whose dialect alias-qualifies the star via RequiresQualifiedSelectStar) and the store is materialised from the result set on every SQL provider (see Dynamic columns). The write side is implemented for INSERT, UPDATE and MERGE: the store's dictionary keys are rendered as physical columns and their values as bound parameters. Keys are ordinal-sorted, so the column order is deterministic; every dynamic key is identifier-quoted with the dialect's quoting even when the global identifier-quoting option (UseQuotedIdentifiers) is off, and the naming convention is skipped (the key is used as the physical column name); an empty key or a key containing a NUL character is rejected. A present key with a null value writes SQL NULL, while a missing key is omitted - on INSERT the column default applies, on UPDATE/MERGE the column is left unchanged (you cannot clear a column by omitting its key); a multi-row INSERT requires the same key set on every row, and in MERGE dynamic columns go to the source/INSERT/SET, never into ON. Values bind from the runtime CLR value with no per-key IPropertyValueConverter/JSON (pass-through), mirroring read-side materialisation. Remaining restrictions: no per-key converters or JSON columns; nextorm tracks no changes; a column cannot be cleared by omission; there is no key allow-list (keys are identifier-quoted and validated instead). The read side is still limited to a single physical source (a query with joins rejects the store with NotSupportedException) and to an entity queried as a whole (an explicit projection does not collect it). On SQL reads the store is created even when it starts null; on write a null or empty store contributes no dynamic columns. The store-bearing entity must be a reference type: a value-type (struct) entity that declares a store is rejected with InvalidOperationException when its metadata is built, rather than boxing silently - use a reference type.
Dynamic columns - parameterless constructor An entity that declares a dynamic-columns store must be a reference type with a public parameterless constructor. The store is materialised with member-init (each mapped column is bound by name and the store receives the star fields), so the regular longest-constructor strategy does not apply to it; a store-bearing entity without a public parameterless constructor is rejected during preparation with a QueryPreparationException. The same store is read by the write side.
Value converters to TimeSpan in table-valued parameters on providers without a native duration type A table parameter has no native duration binding on SQL Server, SQLite and ClickHouse, so a mapped-entity column whose converter targets TimeSpan is rejected with NotSupportedException when the parameter's columns are built, before any row is consumed; bind the converted value as its integer storage instead. PostgreSQL (interval) and MySQL/MariaDB (TIME) keep the converted TimeSpan.
PostgreSQL interval → TimeSpan carrying calendar months A calendar month has no lossless TimeSpan representation, so a mapped interval carrying a non-zero month component — either sign — throws InvalidCastException naming calendar months instead of being approximated as 30 days or silently dropped. A zero-month value converts exactly when it fits a TimeSpan; an unrepresentable one fails with OverflowException instead of wrapping. Scalar ExecuteScalar<T>, raw scalar/composite and JSON paths are outside this rule.
Streaming a LOB (ToStream/ToTextReader) outside PostgreSQL, SQL Server, SQLite MySQL/MariaDB and ClickHouse have no sequential-access support and reject the streaming and multi-column ToDataReader terminals with NotSupportedException (the buffered byte[]/string projection remains available there). On SQLite the query source must expose rowid: a view or WITHOUT ROWID table fails closed with the raw Microsoft.Data.Sqlite.SqliteException: no such column: rowid, with no buffered fallback; the locator can only be appended to a single-source projection, so joins/several sources, DISTINCT/DISTINCT ON, UNION, GROUP BY and aggregates are not supported (they may fail with a driver error), a single-column projection with a raw WithSql override is rejected with NotSupportedException (a rowid locator cannot be added safely), and a raw projection of two or more columns with the usual InvalidOperationException. See Streaming large objects.
CSV export (WriteCsv/WriteCsvAsync) over unsupported projections A projection column that has no single scalar CSV form or whose storage type is unknown/cannot be converted — a nested entity or collection, an array other than byte[], a tuple, a dictionary, an arbitrary object, or a column mapped through an object-based converter or a GetValue fallback — is rejected before the header (and any data row) is written. The spreadsheet formula-injection guard (CWE-1236) is off by default: ExcelMode = true is opt-in, so callers exporting untrusted data must enable it. A query reading a lazy AsTempTable temporary table is rejected with NotSupportedException before the header, because it is a batch (a DROP + CREATE TEMPORARY TABLE ... AS SELECT + read on one session) rather than a single statement. On MySQL/MariaDB/ClickHouse, and for every binary case outside a directly stored byte[] column on a sequential-access provider, a byte[] column is read whole-field, so memory is bounded by the largest field/row rather than a fixed 12 KiB buffer. The destination stream is never closed or disposed, and the rest of the dialect (UTF-8 without BOM, RFC 4180 escaping, CRLF, invariant culture, O/D formats) is fixed. See Streaming data to a Stream.
String and char values are transported exactly, but native comparison limits remain A string or char value is transported exactly — inline or as a bound parameter — up to the valid Unicode representable in the target database column type (unpaired UTF-16 surrogates are out of scope): escaping is per provider, PostgreSQL renders E'…' with \\ and \' and the other C0 characters as \xNN, and SQLite renders a value containing U+0000 as a CAST(X'…' AS TEXT) UTF-8 expression while ordinary values keep doubled apostrophes. A U+0000 in a PostgreSQL string or char is rejected with NotSupportedException naming PostgreSQL and U+0000 (the value is never echoed), both when the literal is translated and before every execution of a bound parameter — a cached command's later executions included. The guarantee does not emulate native limits — a native SQLite LIKE or text comparison stops at the first NUL, and collations and padding behave natively — and caller-owned SQL (raw SQL, stored procedures, raw overrides) keeps its existing parameter transport, which is outside the exact-transport guarantee.

| Portable BASE64, crypto and decimal parsing | base64_encode/base64_decode are the standard RFC 4648 alphabet with padding and no line wrapping; URL-safe or non-canonical input is outside the domain and provider errors are not unified. base64_decode on SQL Server takes varchar (cast an nvarchar column) and both BASE64 members need SQL Server 2025. crypt_gen_random promises 1–1024 bytes only: a constant outside the domain throws before execution and a runtime value keeps the provider's own error. parse/try_parse are SQL Server-only, decimal(38,18) with a fixed en-US culture and no culture argument or other target type. ClickHouse has native base64Encode/base64Decode, but nextorm does not expose them: the capability stays false because the adapter binds a byte[] argument as Array(UInt8) and reads a String result as string, so the byte[] signature cannot be honoured (deferred pending binary parameter/materialisation support). | | ClickHouse nullable-element array parameters — remaining restrictions | A rank-1 array whose declared element type is nullable (Nullable<T> or string) binds as Array(Nullable(T)) built from the declared CLR type, so null elements and their positions survive on input and the caller's array is not mutated. Nested/jagged and multidimensional arrays and a null array object are not supported. On the result side an element of a reference type is declared Nullable(...) unconditionally, so a string[]/string?[] facade result renders Array(Nullable(String)) (jagged: Array(Array(Nullable(String)))) and materialises as a CLR string[] — the element declaration affects only the emitted SQL text, never the data, the CLR type or the public API, and value-type elements (int[], int?[], byte[]) are unaffected. Both decimal array result shapes are materialised: Array(Nullable(Decimal(38,10))) → decimal?[] and Array(Decimal(38,10)) → decimal[], through the direct, member-bound, multi-column and raw read routes, with every representable value preserved exactly; a Decimal(38,10) element whose scaled mantissa exceeds 96 bits (for example 1e19, whose mantissa at scale 10 is 1e29 while CLR decimal's mantissa is 2^96 − 1) fails closed with the driver's OverflowException (Value cannot be represented as System.Decimal), because the pinned driver does not trim trailing zeros and a mathematically smaller representation is not substituted. See Arrays (ClickHouse). | | Percentiles and median (percentile_cont/percentile_disc/median/percentile) — per-provider refusals | The four names are not portable and are never emulated. SQLite refuses all four: the shipped build has no median aggregate and reports SQLITE_ENABLE_PERCENTILE=0, and nextorm produces no window rewrite or custom aggregate in its place. MySQL refuses all four: it has neither the functions nor the WITHIN GROUP grammar (PERCENTILE_CONT is MariaDB-only), so SqlFunctions.MySql.median(v).Over(...) and SqlFunctions.MySql.percentile(fraction, v) throw with the provider named. ClickHouse supports only the native median aggregate (the quantile family, approximate) and refuses the percentile names; no quantile* alias is created. SQL Server and MariaDB expose only the analytic percentile_cont(f) within group (order by x) over (...) shape (one row per input row, partition-only OVER; a window ORDER BY or a frame is refused), while PostgreSQL exposes the ordered-set aggregate (one row per group, never OVER) and refuses a non-floating-point sort expression for the interpolating percentile_cont/median. The generic percentile(fraction, value) is refused by every provider — it never aliases the continuous form — and a fraction is always [0, 1], never a 0–100 percentage (finite and constant; a row-dependent fraction is refused). |

Known edge cases

  • Same-name closure-variable collision. A captured variable read by a global query filter and a different captured variable read by the user's On(...) that share the same CLR name render as the same parameter name, so the placeholders collide; rename one of the variables.
  • Raw-source filter binding is best-effort and unverified. FromSql/From(string) apply a global filter only after BindEntity<TEntity> declares the entity type and the output columns; a filter whose required columns are missing is skipped with a RawSourceFilterSkipped warning on the NextORM.QueryFilters category (reason MissingColumns, physical mapped column names) instead of throwing, while with an empty declared-column list a filter with a proven zero-column dependency is still applied and a column-dependent or undetermined filter is skipped with reason UndeterminedColumns. The column list is the caller's word — nextorm neither parses the raw SQL nor validates it against a schema, so a declared column the fragment does not actually return is accepted and can make a filter run against the wrong shape. Binding is not a security guarantee; a provider without raw-source support rejects the source. See Filters on bound raw sources.

Not limitations

The following are fully implemented and verified; they are documented on their own pages and are not limitations:

  • joins of every type (INNER, LEFT, RIGHT, FULL, CROSS, plus CROSS APPLY/OUTER APPLY and the lateral equivalent where supported, including a correlated apply after a join on a flat 3–8 source chain; no FULL JOIN on MySQL/MariaDB) and arity 2–8, including a derived query as either side (the primary FROM source or the joined table);
  • derived/subquery sources read through an explicit outer Select, including renamed and computed inner columns (see Subqueries), and DTO/record/constructor projections on a direct read (see Projections) — the expression-shape restriction above concerns only a projection-less read of a derived source;
  • correlated scalar subqueries and correlated EXISTS/IN/ANY/ALL in SELECT, WHERE, ORDER BY and HAVING on every SQL provider at any nesting depth (see Subqueries);
  • CASE WHEN / ternary / switch, COALESCE, numeric CAST;
  • string, math and date/time scalar functions and LIKE;
  • the cross-provider scalar string/number functions (SqlFunctions.Sql left/right/lpad/rpad/repeat/reverse/space/concat_ws/translate/ascii/@char), with the per-name gates above;
  • date arithmetic (date_add/end_of_month/date_diff/date_from_parts and DateTime.Add*) on PostgreSQL, SQL Server, ClickHouse, MySQL/MariaDB and SQLite, plus date_trunc on PostgreSQL/SQL Server/ClickHouse and string_agg on PostgreSQL/SQL Server/ClickHouse/MySQL/MariaDB/SQLite;
  • server-side DateTimeOffset translation — .Now/.UtcNow, the seven date parts and the seven Add* members on every SQL provider (the client clock/local time zone is never used; only SQL Server preserves the offset) (see Date and time);
  • the bitwise (bit_and/bit_or/bit_xor), statistical (corr/covar_*) and argMin/argMax aggregates on ClickHouse;
  • binary columns - a byte[] property or projection maps to bytea (PostgreSQL), varbinary/image (SQL Server) or blob (SQLite);
  • LOB streaming on PostgreSQL, SQL Server and SQLite - a single byte[]/string column is read as a Stream/TextReader with CommandBehavior.SequentialAccess and O(buffer) memory, without materialising the whole value (on SQLite the query source must expose rowid), and PostgreSQL, SQL Server and SQLite additionally hand a multi-column result over as a caller-owned DbDataReader through ToDataReader/ToDataReaderAsync (on SQLite that reader is buffered and locator-free, so a LOB column is read whole rather than chunked);
  • unsigned 64-bit integer columns - a ulong/ulong? property or projection materialises through the row reader's DbDataReader.GetFieldValue<ulong> accessor on providers with a native unsigned type (ClickHouse UInt64, MySQL/MariaDB BIGINT UNSIGNED), with no SQL cast; PostgreSQL, SQL Server and SQLite have no unsigned integer type and expose that range only as numeric/decimal;
  • IN over a list/array, logical ! and unary operators;
  • SELECT DISTINCT, INTERSECT/EXCEPT, CTEs (including recursive), window functions and GROUP BY ROLLUP/CUBE/GROUPING SETS (CUBE/GROUPING SETS not on MySQL/MariaDB);
  • data-modifying CTEs (WITH n AS (INSERT|UPDATE|DELETE ... RETURNING ...), single-table and join/multi-table) on PostgreSQL, read typed through MutationCteQuery<T> (see data-modifying CTE);
  • OUTPUT ... INTO <target> on SQL Server — Returning(...).OutputInto(target) writes the modified rows into an existing table, Returning(...).OutputIntoThenOutput(target) writes them and returns them to the client (see insert guide);
  • key-upsert Returning() — PostgreSQL/SQLite append RETURNING to ON CONFLICT ... DO UPDATE, SQL Server renders MERGE ... OUTPUT; MySQL/MariaDB reject it with NotSupportedException (see upsert);
  • user-defined scalar functions ([SqlFunction]) and table-valued-function runtime factories (TvfEntityBuilder<T>);
  • the ClickHouse server/cluster table functions url/s3/file/remote/remoteSecure/cluster/clusterAllReplicas, pre-declared on SqlFunctions.ClickHouse.* with a caller-declared generic TRow row interface (they are gated by SupportsTableFunction; see Table-valued functions);
  • full-text predicates (SqlFunctions.Sql.contains/freetext) on SQL Server, PostgreSQL and MySQL/MariaDB, full-text ranking (SQL Server containstable/freetexttable with KEY/RANK, PostgreSQL ts_rank/ts_rank_cd), text-JSON functions (json_value/json_query/json_modify/isjson) on SQL Server and MySQL/MariaDB and the SQL Server openjson/string_split table functions (plus user-declared TvfEntityBuilder<T> factories, including MySQL JSON_TABLE via callClause); the cross-provider contains/freetext predicates stay unsupported on SQLite (it throws), whose own SQLite-specific FTS3/FTS4/FTS5 query surface (Match, FTS5 FTS5bm25/Highlight/Snippet/Rank, FTS3/4 helpers and the FTS5 table-valued MatchTable FROM source) is available, together with the SQLite-only FTS5 maintenance command builder (CreateSqliteFts5CommandBuilder: AutoMerge/CrisisMerge/Merge/Optimize/Rebuild/IntegrityCheck, with sync/async terminals);
  • statement-level query hints on SQL Server (OPTION (...)), PostgreSQL and MySQL/MariaDB (/*+ ... */), table hints on SQL Server (Hint(...), WithTableHint(...), the per-join JoinOptions.WithJoinTableHint(...)), join/subquery/tables-in-scope hints (JoinOptions.WithJoinHint, FromOptions.WithSubQueryHint, WithTablesInScopeHint), JSON output (ForJson(...) → FOR JSON PATH/AUTO) and XML output (ForXml(...) → FOR XML);
  • the XML data-type methods on SQL Server (xml_value/xml_query/xml_exist → xml.value('xpath', 'type')/xml.query('xpath')/xml.exist('xpath'), and the .nodes rowset via xml_nodes as a CrossApply/OuterApply source);
  • raw SQL as a whole query (WithSql / PrepareFromSql) and as a composable FROM source (FromSql, rendered as a derived table), plus the implicit plan cache / Prepare reuse paths;
  • raw command execution against the context (ExecuteRaw/ExecuteRawAsync, and stored procedures through ExecuteProcedure/ExecuteProcedureAsync, returning a disposable ProcedureResult) with named/DbType/Size parameters, sequential result sets, and output/return-value parameters (see Raw SQL);
  • per-query source overrides (WithTableName/WithSchema/WithDatabase/WithServer/WithTableExpression) on every SQL provider, part of the plan-cache key (see Per-query source overrides).

See also

Source: the per-provider capability matrix maintained on the provider pages (docs/connecting-providers/*.md) and the shared test suite under tests/nextorm.integration.tests/.

Edit this page