Provider overview
A provider supplies a dialect (SQL text rules) and a
DataContextsubclass (connection + parameter creation); pick the one that matches the database you already run.
Prerequisites: Quickstart · Dependency injection
Overview
nextorm is split into a provider-neutral core (nextorm, namespace NextORM.Core) and one package per
database: nextorm.sqlite, nextorm.sqlserver, nextorm.postgres, nextorm.mysql, nextorm.mariadb
and nextorm.clickhouse. The in-memory provider is built
into the core package. A provider contributes two things:
- a dialect — a stateless object that renders everything that differs between databases (parameter placeholders, paging, quoting, function names, capability flags); and
- a context — a
DataContextsubclass that knows how to create a connection and parameters, and exposes the dialect through itsDialectproperty.
SQL generation is driven entirely by ISqlDialect (src/nextorm.core/DataContext/Dialect/ISqlDialect.cs),
so the SQL builder and expression visitors never contain provider names. Query semantics (projection,
filtering, joins, grouping, set operations, CTE, window functions, scalar/UDF/TVF support) are shared;
only the rendering differs.
Choosing a provider
| Situation | Provider |
|---|---|
| Local development, tests, embedded database, small apps | nextorm.sqlite |
| You already run Microsoft SQL Server / Azure SQL | nextorm.sqlserver |
| You already run PostgreSQL | nextorm.postgres |
| You already run MySQL | nextorm.mysql |
| You already run MariaDB | nextorm.mariadb |
| You already run ClickHouse | nextorm.clickhouse |
| Unit tests that must not touch a database, plan-cache tests, query-shape tests | InMemoryDataContext (core) |
Use the same query code against every provider; the provider differences below are the only things that change.
Support matrix
| Capability | SQLite | SQL Server | PostgreSQL | MySQL | MariaDB | ClickHouse | In-memory |
|---|---|---|---|---|---|---|---|
| Package | nextorm.sqlite |
nextorm.sqlserver |
nextorm.postgres |
nextorm.mysql |
nextorm.mariadb |
nextorm.clickhouse |
built into nextorm |
| Parameter placeholder | $name |
@name |
@name |
@name |
@name |
@name |
not applicable |
Table-valued parameters (ProcedureParameter.Table<T>) |
json_each JSON document |
native SqlDbType.Structured + TypeName |
typed array / jsonb document |
JSON_TABLE JSON document |
JSON_TABLE JSON document |
bound Array(T)/Array(Tuple(...)) + arrayJoin |
throws NotSupportedException |
Transactions (ITransactionManager) |
supported | supported | supported | supported | supported | throws NotSupportedException |
not applicable (no connection) |
| Savepoints (create / rollback to / release) | supported | supported (release is a no-op) | supported | supported | supported | throws NotSupportedException |
not applicable (no connection) |
INSERT ... VALUES |
supported | supported | supported | supported | supported | supported (small batches) | throws NotSupportedException |
Bulk insert (CreateBulkInsertBuilder) |
portable INSERT ... VALUES (no native path) |
native SqlBulkCopy |
native binary COPY |
native MySqlBulkCopy (LOAD DATA LOCAL INFILE) |
native MySqlBulkCopy (LOAD DATA LOCAL INFILE) |
native InsertBinaryAsync |
throws NotSupportedException |
Bulk-insert modes (BulkInsertMode Auto/Native/Portable) |
portable only; Native refused |
all three | all three | all three | all three | all three | not applicable |
Bulk-copy flags (CheckConstraints/TableLock/KeepNulls/FireTriggers) |
CheckConstraints=false via a scoped pragma outside a transaction; KeepNulls=false only for a default-equivalent shape; the rest refused |
SqlBulkCopyOptions (native path only) |
KeepNulls=false only for a default-equivalent shape; the rest refused |
KeepNulls=false only for a default-equivalent shape; the rest refused |
CheckConstraints=false via a scoped session control; KeepNulls=false only for a default-equivalent shape; the rest refused |
KeepNulls=false only for a default-equivalent shape; the rest refused |
throws NotSupportedException |
Key upsert (CreateMergeBuilder) |
ON CONFLICT ... DO UPDATE |
MERGE ... USING (VALUES ...) |
ON CONFLICT ... DO UPDATE |
ON DUPLICATE KEY UPDATE |
ON DUPLICATE KEY UPDATE |
throws NotSupportedException |
applied in the context (upsert) |
Full MERGE (WhenMatched/WhenNotMatched, branches) |
throws NotSupportedException |
MERGE ... WHEN MATCHED THEN ... |
MERGE ... WHEN MATCHED THEN ... (15+) |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
Merge search condition (On/WhenMatched(condition)) |
throws NotSupportedException |
ON <condition> / WHEN ... AND <condition> |
ON <condition> / WHEN ... AND <condition> |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
Merge assignment expressions (ThenUpdate(assignments)/ThenInsert(assignments)) |
key upsert: target + source roles, native excluded.<col> |
full/key MERGE: target + source; excluded rejected (no proposed row) |
full MERGE and ON CONFLICT: target + source, native excluded.<col> |
key upsert: target + source, native VALUES(<col>); a batch/query source reading the source role is rejected |
same as MySQL, with dedicated live coverage | throws NotSupportedException |
throws NotSupportedException |
MERGE ... RETURNING/OUTPUT (Returning) |
throws NotSupportedException |
OUTPUT inserted.<col> |
RETURNING target.<col> (17+) |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
DELETE (CreateDeleteBuilder/Delete) |
supported | supported | supported | supported | supported | ALTER TABLE ... DELETE ... SETTINGS mutations_sync = 1 |
throws NotSupportedException |
UPDATE (CreateUpdateBuilder/Update(entity)) |
supported | supported | supported | supported | supported | ALTER TABLE ... UPDATE ... SETTINGS mutations_sync = 1 |
throws NotSupportedException |
UPDATE ... RETURNING (Returning) |
RETURNING |
OUTPUT inserted.<col> |
RETURNING |
throws NotSupportedException |
RETURNING when version ≥13.0 is configured; otherwise throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
UPDATE ... FROM (CreateUpdateJoinBuilder) |
UPDATE ... FROM |
UPDATE <alias> ... FROM ... JOIN |
UPDATE ... FROM |
UPDATE ... JOIN ... SET |
UPDATE ... JOIN ... SET |
throws NotSupportedException |
throws NotSupportedException |
DELETE ... RETURNING (Returning) |
RETURNING |
OUTPUT deleted.<col> |
RETURNING |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
TRUNCATE (CreateTruncateBuilder) |
throws NotSupportedException |
supported | supported | supported | supported | supported | throws NotSupportedException |
LOB streaming (ToStream/ToTextReader) |
supported (single byte[]/string column; the source must expose rowid) |
supported (single byte[]/string column) |
supported (single byte[]/string column) |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
supported (single byte[]/string column; returns a caller-owned MemoryStream/StringReader) |
Multi-column reader (ToDataReader/ToDataReaderAsync) |
supported (buffered, locator-free; no chunked LOB) | supported | supported | throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
Materialize a query (ToTempTable/ToTable) |
CREATE [TEMPORARY] TABLE ... AS SELECT |
SELECT ... INTO (ToTable; session-scoped via #name) |
CREATE [TEMPORARY] TABLE ... AS SELECT |
CREATE [TEMPORARY] TABLE ... AS SELECT |
CREATE [TEMPORARY] TABLE ... AS SELECT |
CREATE TABLE ... ENGINE = MergeTree ... AS SELECT (ToTable) |
throws NotSupportedException |
Batch (Batch) |
;-joined command |
;-joined command (not SqlBatch) |
NpgsqlBatch |
MySqlBatch |
MySqlBatch |
throws NotSupportedException |
throws NotSupportedException |
DELETE ... USING/join (From<T>().Join(...).Delete(), INNER) |
throws NotSupportedException |
DELETE <a> FROM ... JOIN ... |
DELETE FROM ... USING ... |
DELETE <a> FROM ... JOIN ... |
DELETE <a> FROM ... JOIN ... |
throws NotSupportedException |
throws NotSupportedException |
Generated key (ReturningIdentity/ReturningKey) |
RETURNING |
OUTPUT inserted.<col> |
RETURNING |
LAST_INSERT_ID() fallback |
LAST_INSERT_ID() fallback |
throws NotSupportedException |
throws NotSupportedException |
Identity function (ReturningIdentity<TKey>()) |
last_insert_rowid() |
SCOPE_IDENTITY() |
lastval() |
LAST_INSERT_ID() |
LAST_INSERT_ID() |
throws NotSupportedException |
throws NotSupportedException |
Return inserted rows (Returning) |
RETURNING |
OUTPUT inserted.<cols> |
RETURNING |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
| Limit only | limit n |
top(n) |
limit n |
limit n |
limit n |
limit n |
in-process take |
| Limit + offset | limit n offset m |
offset m rows fetch next n rows only |
limit n offset m |
limit n offset m |
limit n offset m |
limit n offset m |
in-process skip/take |
| Offset only | limit -1 offset m |
offset m rows |
offset m |
limit 18446744073709551615 offset m |
limit 18446744073709551615 offset m |
limit 18446744073709551615 offset m |
in-process skip |
Paging without ORDER BY |
accepted | injects order by (select null as anyorder) |
accepted | accepted | accepted | accepted | accepted |
Explicit NULL placement (NullsPosition) |
native NULLS FIRST/NULLS LAST |
portable CASE emulation |
native | portable CASE emulation |
portable CASE emulation |
native | honoured in process |
INTERSECT ALL / EXCEPT ALL (*ALL) |
throws NotSupportedException |
throws NotSupportedException |
supported | throws NotSupportedException |
supported | supported | not applicable |
| Recursive CTE keyword | with recursive |
with (plus option (maxrecursion n)) |
with recursive |
with recursive |
with recursive |
with (no recursive CTE support) |
not applicable |
Named windows / GROUPS / EXCLUDE |
named window + GROUPS + EXCLUDE |
throws (no WINDOW clause) |
named window + GROUPS + EXCLUDE |
named window only | named window only | named window + GROUPS |
not applicable |
| String concatenation | \|\| |
+ |
\|\| |
concat(a, b) |
concat(a, b) |
concat(a, b) |
not applicable |
| Boolean literal | 1 / 0 |
1 / 0 (via bit) |
true / false |
1 / 0 (true / false) |
1 / 0 (true / false) |
true / false |
not applicable |
?? (coalesce) |
ifnull(a, b) |
isnull(a, b) |
coalesce(a, b) |
coalesce(a, b) |
coalesce(a, b) |
coalesce(a, b) |
not applicable |
stdev / stdevp |
stdev / stdevp (custom) |
stdev / stdevp (native) |
stddev / stddev_pop |
stddev_samp / stddev_pop |
stddev_samp / stddev_pop |
stddevSamp / stddevPop |
not applicable |
var / varp |
var / varp (custom) |
var / varp (native) |
variance / var_pop |
var_samp / var_pop |
var_samp / var_pop |
varSamp / varPop |
not applicable |
date_trunc |
throws | datetrunc(...) (2022+) |
supported | throws | throws | dateTrunc(...) |
throws |
Date arithmetic (date_add, date_diff, end_of_month, date_from_parts, DateTime.Add*) |
datetime(x, n \|\| ' days') / date(...) / strftime difference |
supported | supported | supported | supported | addDays(...) … / toLastDayOfMonth(...) |
throws |
Date conversion / parts (SqlFunctions.ClickHouse.to_*) |
throws | throws | throws | throws | throws | toDate/toDateTime/toDate32, toYear/…, toStartOf*, toMonday, toYYYYMM/toYYYYMMDD, toUnixTimestamp |
throws |
string_agg |
group_concat(x, delimiter) |
supported (2017+) | supported | group_concat(x separator delimiter) |
group_concat(x separator delimiter) |
arrayStringConcat(groupArray(...), ...) |
throws |
Full-text contains / freetext (cross-provider) |
throws | contains / freetext |
to_tsvector(...) @@ ...tsquery(...) |
match(...) against(...) |
match(...) against(...) |
throws | throws |
| Full-text ranking / score | throws | containstable / freetexttable (RANK, table function) |
ts_rank / ts_rank_cd |
throws | throws | throws | throws |
| SQLite-only full-text (FTS3/FTS4/FTS5) | SqlFunctions.Sqlite query surface (Match, FTS5 FTS5bm25/Highlight/Snippet/Rank, FTS3/4 helpers, FTS5 table-valued MatchTable; FTS3/4 Rank needs a connection-registered rank UDF) |
throws | throws | throws | throws | throws | throws |
Regular expressions (Regex.IsMatch / Regex.Replace, constant pattern) |
s regexp ... / regexp_replace(...) (registered) |
regexp_like(...) / regexp_replace(...) (2025+) |
s ~ ... / regexp_replace(...) |
regexp_like(...) / regexp_replace(...) |
s regexp ... / regexp_replace(...) |
match(...) / replaceRegexpAll(...) |
native System.Text.RegularExpressions |
Bit / statistical / -If aggregates |
throws | throws | supported | throws | throws | groupBit*, corr/covarPop, countIf/… |
throws |
multi_if (multi-branch) |
throws | throws | throws | throws | throws | multiIf(c1, v1, …, else) |
not applicable |
lag_in_frame / lead_in_frame |
throws | throws | throws | throws | throws | lagInFrame / leadInFrame |
not applicable |
| Identifier / alias quoting | single quotes: as 't1' |
brackets: as [t1] |
double quotes: as "t1" |
backticks: as `t1` |
backticks: as `t1` |
backticks: as `t1` |
not applicable |
Derived table (subquery in FROM) alias |
not required | required | required | required | required | required | not applicable |
Per-query source override (WithSchema/WithDatabase/WithServer) |
schema = attached database (main.t); database = same; server throws |
schema.t, db.schema.t, server.db.schema.t |
schema.t; database/server throw |
db.t (database = schema); server throws |
same as MySQL | db.t (database = schema); server throws |
throws (no SQL source) |
| Table-valued function alias | not required | required | required | required | required | required | TVF source not supported |
LEFT / RIGHT / FULL / CROSS join |
yes | yes | yes | no FULL |
no FULL |
yes | yes |
RIGHT / FULL join capability |
supported | supported | supported | RIGHT only |
RIGHT only |
supported | supported |
Native JSON (json/jsonb) / JSON functions |
JSON1 via SqlFunctions.Sqlite only (cross-provider Json.* API not mapped) |
JSON as text: json_value/json_query/json_modify/isjson (nvarchar); SQL Server 2025+ has a native json type, reachable for [JsonColumn] only from a context declaring major version 17+ or an Azure/Fabric SQL database engine edition (string transport, object/array roots only) |
native json/jsonb + json_agg/json_build_object + operators ->/->>/#>/@>/? |
json_value/json_query/json_modify/isjson over JSON_EXTRACT/JSON_SET; a real json column (MySQL 8) is reached through text storage (Auto/Text) — Native refused, the driver has no JSON binding |
same functions as MySQL; JSON is a LONGTEXT + json_valid() CHECK alias (reported as longtext), used through text storage, Native refused |
string JSON JSONExtract*/visitParam*, JSONPath json_value/json_query/json_exists, json_all_paths/to_json_string; native JSON column (object-rooted) mapped through [JsonColumn]/JsonObject/JsonDocument/JsonElement |
not applicable |
Arrays / higher-order array functions / ARRAY JOIN |
throws NotSupportedException |
throws NotSupportedException |
native arrays (any/all, cardinality, array_agg) |
throws NotSupportedException |
throws NotSupportedException |
Array(T), arrayMap/arrayFilter/arrayExists/… (higher-order), ARRAY JOIN (ArrayJoin/LeftArrayJoin) |
throws NotSupportedException |
| Native range types / range-over-scalar-columns | [RangeColumns] pair |
[RangeColumns] pair |
native int4range…daterange + multiranges (Range<T>/Range<T>[]); pair also accepted |
[RangeColumns] pair |
[RangeColumns] pair |
[RangeColumns] pair |
CLR Range<T>/Range<T>[] |
Temporal tables (FOR SYSTEM_TIME) |
throws NotSupportedException |
AS OF/BETWEEN/FROM … TO/CONTAINED IN/ALL |
throws NotSupportedException |
throws NotSupportedException |
AS OF/BETWEEN/FROM … TO/ALL (CONTAINED IN throws) |
throws NotSupportedException |
throws NotSupportedException |
| Data-modifying CTEs | throws NotSupportedException |
throws NotSupportedException |
With(name, insert/update/delete) (body requires RETURNING) |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
Query modifiers (DISTINCT ON/WITH TIES/TABLESAMPLE) |
throws NotSupportedException |
WITH TIES; TABLESAMPLE SYSTEM only |
DISTINCT ON; WITH TIES; TABLESAMPLE SYSTEM/BERNOULLI |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException (has its own SAMPLE) |
throws NotSupportedException |
PIVOT / UNPIVOT source |
throws NotSupportedException |
native T-SQL PIVOT/UNPIVOT (raw identifiers and pivot values, always safely quoted) |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
ClickHouse clauses (FINAL/SAMPLE/PREWHERE/SETTINGS/LIMIT n BY/WITH TOTALS/GLOBAL IN) |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
Final()/Sample()/PreWhere()/Settings()/LimitBy()/WithTotals()/global_in |
throws NotSupportedException |
Join strictness (ANY/ALL/ASOF, SEMI/ANTI/PASTE) / GLOBAL join |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
WithStrictness(...) / Global() |
throws NotSupportedException |
Dictionary lookups (dictGet) |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
throws NotSupportedException |
dict_get/dict_get_or_default/dict_has/… (needs a configured dictionary) |
throws NotSupportedException |
Provider table functions (FROM source) |
json_each/json_tree |
string_split/openjson/containstable/freetexttable |
generate_series/unnest/regexp_matches/jsonb_to_recordset |
throws NotSupportedException |
throws NotSupportedException |
numbers/zeros/generateRandom/values (url/s3/file/remote/cluster pre-declared, distributed execution out of scope) |
TVF source not supported |
Filtered aggregates (FILTER (WHERE …) / -If) |
ANSI count(*) filter (where …) |
throws NotSupportedException |
ANSI filter (where …) (9.4+; version-gated) |
throws NotSupportedException |
throws NotSupportedException |
-If combinator (countIf/sumIf/…) |
throws NotSupportedException |
| Ordered-set / boolean / regression aggregates | throws NotSupportedException |
window percentile_cont(f) within group (order by x) over (…) (2012+) |
percentile_cont/percentile_disc/mode, bool_and/bool_or/every, regr_* |
throws NotSupportedException |
window percentile_cont (10.3+) |
quantile*/median (own family); no bool_and/regr_* |
throws NotSupportedException |
| Median (native composition) | throws NotSupportedException (SqlFunctions.Sqlite.median) |
SqlFunctions.SqlServer.median(v).Over(…) → percentile_cont(0.5) within group (order by v) over (…), analytic: one row per input row, partition-only window |
SqlFunctions.Postgres.median(() => v) → percentile_cont(0.5) within group (order by v), aggregate: one row per group; the sort expression must be floating-point |
throws NotSupportedException (SqlFunctions.MySql.median; message names MySQL) |
SqlFunctions.MySql.median(v).Over(…) → percentile_cont(0.5) within group (order by v) over (…), analytic: one row per input row, partition-only window |
SqlFunctions.ClickHouse.median(v) → toFloat64(median(v)), aggregate: one row per group (the T286b native form) |
throws NotSupportedException |
Generic percentile(fraction, value) |
throws NotSupportedException (SqlFunctions.Sqlite.percentile) |
throws NotSupportedException (SqlFunctions.SqlServer.percentile) |
throws NotSupportedException (SqlFunctions.Postgres.percentile) |
throws NotSupportedException (SqlFunctions.MySql.percentile, names MySQL) |
throws NotSupportedException (SqlFunctions.MySql.percentile, names MariaDB) |
throws NotSupportedException (SqlFunctions.ClickHouse.percentile) |
throws NotSupportedException |
| PostgreSQL settings & sequences | throws NotSupportedException |
throws NotSupportedException |
current_setting/set_config, nextval/setval/currval/lastval |
throws NotSupportedException |
sequences via SqlFunctions.MySql (next_value_for/nextval/setval/lastval) |
throws NotSupportedException |
throws NotSupportedException |
Provider scalar-function library (SqlFunctions.<Provider>) |
SqlFunctions.Sqlite (core scalars, JSON1, date, math, FTS3/4/5 query) |
SqlFunctions.SqlServer (T-SQL library, XML methods, JSON-as-text, string_split/openjson) |
SqlFunctions.Postgres (arrays, JSON/JSONB, ranges, aggregate families, full-text ts_*, settings/sequences, regexp_*) |
SqlFunctions.MySql (string/date/hash/inet/JSON) |
inherits SqlFunctions.MySql + MariaDB additions (nvl, add_months, to_char, xxh3, sequences, …); months_between/to_date/to_number, xxh3/xxh32 and the UUID generators are version-gated (12.2.1 / 12.3 / 12.2.1; 13.1; 11.7) and refused by an unversioned context |
SqlFunctions.ClickHouse (quantile/uniq/topK/sequence aggregates, arrays, maps, hashes, dictionaries, table functions) |
none (SqlFunctions.Sql evaluates in-process) |
Row values / tuples ((a, b), element access, row comparison) |
flat (a, b) constructor as a direct ==/!= predicate operand (inline .ItemN folds; projection/ordering/grouping/function arguments and server-side .ItemN throw NotSupportedException) |
throws NotSupportedException |
ROW(a, b), (row).fN, ROW(...) = ROW(...); a single raw ROW(...) (arity 1..7) materialises into System.Tuple<...> via ExecuteRaw(...).Read<T>() over the ordinary connection string, while a caller-registered named composite materialises into the named type when the caller supplies a configured NpgsqlDataSource (#194); ValueTuple, arity ≥8, a nested/empty ROW, several/mixed record columns, an unregistered composite and a composite declared as System.Tuple throw NotSupportedException |
flat (a, b) constructor as a direct ==/!= predicate operand (inline .ItemN folds; server-side .ItemN throws) |
same as MySQL | tuple(a, b), tupleElement, comparison |
throws NotSupportedException |
Tuple IN/Contains over a value list (arity 1..7) |
(a, b) IN (VALUES (@p0, @p1), ...) |
throws NotSupportedException for every tuple form (incl. null collection/entry, arity ≥8, nested) — SQL Server does not support tuple IN/Contains translation. |
ROW(a, b) IN (ROW(@p0, @p1), ...) |
(a, b) IN ((@p0, @p1), ...) |
same as MySQL | tuple(a, b) IN (tuple(@p0, @p1), ...); global_in renders GLOBAL IN |
evaluated in process against the in-memory rows (Enumerable.Contains; no rejection) |
Explicit NULL placement (NullsPosition) is native on SQLite,
PostgreSQL and ClickHouse (NULLS FIRST/NULLS LAST) and a portable CASE emulation on SQL Server,
MySQL and MariaDB (CASE WHEN k IS NULL THEN … END ASC, k); placement is independent of direction, and the
emulating providers fail closed on DISTINCT/DISTINCT ON shapes that cannot carry the repeated key (see
Sorting and paging).
Provider differences: unification decisions
Where providers differ, nextorm either unifies the surface in code, gates the feature so an
unsupported provider throws NotSupportedException, or documents the difference and leaves it
provider-specific. The decision for every known divergence:
| Difference | Decision | Rationale / hook |
|---|---|---|
date_add/date_diff/date_trunc accepted fields |
Leave provider-specific, gated per field | SupportsDateAddField/SupportsDateDiffField/SupportsDateTruncField reject an unsupported field; a single normalized set would silently change results (SQLite folds millisecond/quarter, SQL Server has no decade/century/millennium). |
FULL JOIN on MySQL/MariaDB |
Gated, no polyfill | SupportsFullJoin => false; a LEFT JOIN … UNION … RIGHT JOIN rewrite changes row shape/deduplication and can defeat the planner, so it is never emitted implicitly. |
CUBE/GROUPING SETS on MySQL/MariaDB (and all of ROLLUP/CUBE/GROUPING SETS in-memory) |
Gated, no polyfill | SupportsCube/SupportsGroupingSets; a UNION ALL emulation multiplies scans and changes semantics (GROUPING()), so it is left to raw SQL. |
GREATEST/LEAST NULL semantics |
Documented difference | PostgreSQL/SQL Server 2022+/ClickHouse ignore NULL arguments; MySQL/MariaDB and SQLite return NULL when any argument is NULL. Availability is gated by SupportsGreatestLeast; the NULL behaviour is not rewritten. |
Cross-provider scalar functions (left/rpad/concat_ws/translate/ascii/…) |
Unified, gated per name | IScalarFunctions renders each provider's native form (SQLite substr/unicode, SQL Server REPLICATE-based pads, ClickHouse *UTF8); a name a provider cannot express is rejected. concat_ws NULL handling and negative-n left/right are documented differences, not rewritten. |
Date/number formatting templates (to_char, FORMAT, strftime, formatDateTime) |
Closed — not unifiable | The template languages are incompatible, so formatting stays provider-specific [SqlFunction] UDFs; there is no portable template argument. |
FOR JSON / FOR XML |
Gated (SQL Server) | SupportsForJson/SupportsForXml. |
| Statement-level query hints | Unified | SQL Server OPTION (...), PostgreSQL/MySQL/MariaDB inline /*+ ... */; SQLite/ClickHouse have no syntax and stay gated (see Query hints). |
Query tags (WithTag) |
Unified | A portable /* tag */ block comment right after SELECT on every SQL provider; the native SQL Server OPTION (LABEL) and ClickHouse log_comment are not used, and in-memory accepts the call as a no-op (see Query tags). |
| Locking table hints vs index hints | Leave provider-specific | WITH (NOLOCK) has no equivalent in MySQL/MariaDB/SQLite index hints (USE INDEX/INDEXED BY change the plan, not locking), so only SQL Server is wired (SupportsTableHints). |
| Join / subquery / tables-in-scope hints | Two forms | SQL Server renders a join hint inside the join (INNER LOOP JOIN) and a tables-in-scope hint as WITH (...) on every table; PostgreSQL/MySQL/MariaDB fold all three into the inline /*+ ... */ comment; SQLite/ClickHouse/in-memory reject them (see Query hints). |
Row-locking wait modes (NOWAIT/SKIP LOCKED) |
Unified | ILockRenderer.Render emits each capable provider's native form: PostgreSQL/MySQL/MariaDB append NOWAIT/SKIP LOCKED (MySQL switches a shared lock to FOR SHARE), SQL Server adds NOWAIT/READPAST to the locking table hint (READPAST approximates SKIP LOCKED); SQLite/ClickHouse/in-memory reject any row lock. |
Raw SQL as a composable FROM source |
Unified | FromSql + SupportsRawSqlSource on every SQL provider (see Raw SQL). |
| Per-query source override levels | Gated per level | MakeQualifiedTableName plus SupportsCrossDatabase/SupportsLinkedServer: a provider that cannot express a level rejects WithDatabase/WithServer with NotSupportedException instead of silently dropping the qualifier. Schema/data-table naming and raw WithTableExpression are the universal levels on SQL providers. |
INTERSECT ALL/EXCEPT ALL |
Gated | PostgreSQL and MariaDB support them; SQL Server/SQLite/MySQL reject via SupportsIntersectExceptAll. |
Server-version-gated syntax (FILTER on PostgreSQL; UPDATE ... RETURNING and the post-11.4 native functions on MariaDB) |
Gated by an explicitly configured version | The provider carries the server Version (SupportsUpdateReturning reflects the MariaDB gate). PostgreSQL enables ANSI FILTER at 9.4+ and keeps the unset default at ≥9.4 (today's behaviour); MariaDB enables UPDATE ... RETURNING only at an explicit 13.0+ and each post-11.4 function only at its own minimum. There is no live server-version probing. PostgreSQL additionally fixes one version per concrete context type for the process lifetime; MariaDB imposes no such guard (each context carries its own dialect and capability predicate). |
The per-feature rows in the limitations table spell out the resulting runtime behaviour.
Variability aggregates are unified, not left as a documented difference. Stdev/Stdevp/Var/Varp
and the eight Sql.stdev*/Sql.var* facade forms return a structurally nullable T? on every provider:
a sample result over zero or one contributing non-null value is null, a population result over zero
values is null and over one value is 0.0, and two or more values keep each provider's established
formula. The cardinality contract is the same for SQLite, PostgreSQL, SQL Server, MySQL/MariaDB and
ClickHouse; only the native function name differs (stddev/stddev_pop/variance/var_pop,
stddev_samp/stddev_pop/var_samp/var_pop, stddevSamp/stddevPop/varSamp/varPop). The
verification status of each provider is stated on its own page.
Server-version gates
Two capabilities depend on the server version, and both are gated by an explicitly configured
version — nextorm never probes the live server. Pass the server version when the context is created
(the provider context has a constructor overload with a Version parameter); leaving it unset keeps the
historical behaviour: PostgreSQL assumes 9.4 or later and emits FILTER (WHERE ...), while MariaDB
leaves UPDATE ... RETURNING and the post-11.4 native functions disabled. PostgreSQL's filtered
aggregates require 9.4+ and throw NotSupportedException below it; MariaDB's UPDATE ... RETURNING
requires an explicit 13.0+, while months_between/to_number (12.2.1), to_date (12.3) and the UUID
generators (11.7) each require their own minimum. PostgreSQL
enforces one immutable server version per concrete context type for the process lifetime, because the
cached SELECT SQL depends on the FILTER gate: a second PostgreSQL context of the same concrete type
that requests a different version throws InvalidOperationException, so declare a distinct context
subclass per PostgreSQL version. MariaDB imposes no such guard: each context carries its own dialect
and capability predicate, so different versions may be used on the same context type. Insert/delete
RETURNING and ANY_VALUE (any_agg, 13.2) are unaffected.
How a dialect plugs in
A dialect implements ISqlDialect or derives from SqlDialectBase. In SqlDialectBase only
MakeParam and MakePage are abstract; every other member has a working ANSI default, so a dialect
overrides just what is different. Capability differences (paging requiring an ORDER BY, required
subquery aliases, INTERSECT ALL/EXCEPT ALL) are expressed as properties rather than special cases in
the SQL builder.
// The built-in dialects are singletons exposed as a static Instance.
ISqlDialect sqlite = SqliteDialect.Instance;
ISqlDialect sqlServer = SqlServerDialect.Instance;
ISqlDialect postgres = PostgresDialect.Instance;
ISqlDialect mysql = MySqlDialect.Instance;
ISqlDialect mariaDb = MariaDbDialect.Instance;
ISqlDialect clickHouse = ClickHouseDialect.Instance;
A provider context returns its dialect from an overridden property:
public class SqliteDataContext : DataContext
{
public override ISqlDialect Dialect => SqliteDialect.Instance;
// CreateDbConnection / CreateParam are provider specific.
}
The dialect contract deliberately excludes connection/parameter creation (CreateConnection/
CreateParam) and column mapping (MapColumnExpression): those live on the context, because they are a
separate axis from SQL text.
Registering a provider
Every provider package adds UseXxx extension methods on DataContextBuilder, and every context also has a
public constructor that takes a connection string or an existing DbConnection:
using NextORM.Core;
using NextORM.Sqlite; // or nextorm.sqlserver / nextorm.postgres
var builder = new DataContextBuilder().UseSqlite("app.db"); // provider-specific overload
using var ctx = builder.CreateDataContext(); // returns IDataContext
With dependency injection:
services.AddNextOrmContext(builder => builder.UseSqlite("app.db"));
See Dependency injection for keyed and generic registrations.
See also
- SQLite
- SQL Server
- PostgreSQL
- MySQL
- MariaDB
- ClickHouse
- In-memory
- Limitations and out-of-scope features
Source: src/nextorm.core/DataContext/Dialect/ISqlDialect.cs, src/nextorm.core/DataContext/Dialect/SqlDialectBase.cs,
tests/nextorm.sqlite.tests/Dialect/SqliteDialectTests.cs, tests/nextorm.sqlserver.tests/Dialect/SqlServerDialectTests.cs,
tests/nextorm.postgres.tests/Dialect/PostgresDialectTests.cs, tests/nextorm.mysql.tests/Dialect/MySqlDialectTests.cs,
tests/nextorm.mariadb.tests/Dialect/MariaDbDialectTests.cs, tests/nextorm.clickhouse.tests/Dialect/ClickHouseDialectTests.cs.