Provider-specific SQL

nextorm targets a portable subset of SQL, but it does not hide what a database can do. A construct is available when the active dialect advertises the matching ISqlDialect capability, and a provider that does not opt in rejects it with NotSupportedException.

This section catalogues the constructs that are exclusive to a single provider — every other dialect throws for them. Constructs that are merely spelled differently, or that several providers share (for example greatest/least, full-text search, JSON, table functions, any_agg, UUID generators or PERCENTILE_CONT), are documented with the matching concept page instead, and only four providers have an exclusive surface at all: ClickHouse, PostgreSQL, SQL Server and MySQL/MariaDB.

Prerequisites: Querying and projections · Provider overview · Limitations and out-of-scope features

How provider-specific SQL is gated

The query builder is dialect-driven: every feature that is not part of the portable surface is guarded by a Supports* property, a per-name predicate or a Make* rendering hook on ISqlDialect. The provider implementations under src/nextorm.<provider> turn those flags on and render the native syntax; SqlDialectBase keeps safe defaults that throw. As a result:

• the same LINQ expression compiles against every provider, but only the providers that support the construct execute it — the rest throw NotSupportedException with a provider-specific message;
• the in-memory provider is the strictest: it only implements select, where, joins, grouping, set operations and windowing over in-memory collections, and throws for anything that needs a SQL engine;
• an unsupported construct fails when the query is prepared (SQL providers) or enumerated (in-memory), not when the expression is written.

Providers with an exclusive surface

Provider	Exclusive constructs
ClickHouse	Arrays and ARRAY JOIN, LIMIT n BY expr, GROUP BY ... WITH TOTALS, FINAL/SAMPLE/PREWHERE/SETTINGS, join strictness and GLOBAL JOIN, GLOBAL IN, uniq/quantile/any/argMin/argMax aggregates, the -If combinators, dictionaries, string-JSON JSONExtract, the numbers/zeros table functions
MySQL and MariaDB	Native string/conditional idioms (FIND_IN_SET/FIELD/ELT/SUBSTRING_INDEX/FORMAT), %-templated date conversion and Unix-epoch functions (STR_TO_DATE/DATE_FORMAT/FROM_UNIXTIME/UNIX_TIMESTAMP), hexadecimal hashes (MD5/SHA1/SHA2), IPv4 conversion (INET_ATON/INET_NTOA), the JSON mutation family and UUID_TO_BIN/BIN_TO_UUID (MySQL only)
PostgreSQL	Native array types and operators, native json/jsonb, ordered-set aggregates (percentile_* ... WITHIN GROUP), regression/boolean/bit aggregates, extended scalar and regexp helpers, DISTINCT ON, unnest
SQL Server	CHOOSE, table hints and OPTION (...), FOR JSON/FOR XML, string_split/openjson

Providers without an exclusive surface

SQLite has no provider-exclusive constructs: every capability flag it turns on is also set by at least one other dialect. Its page therefore documents connection setup and limitations only, and the shared constructs it implements are described on the concept pages:

• SQLite — scalar max/min, strftime date parts, JSON1, registered stdev/var aggregates.

The MySQL/MariaDB guide page also covers the shared constructs of the MySQL family: text JSON, full-text MATCH ... AGAINST, ANY_VALUE, PERCENTILE_CONT/MEDIAN and UUID_v4()/UUID_v7().

Portability model

The provider pages describe a construct from the database's point of view; the concept pages carry the full, provider-neutral walkthrough:

• Filtering (WHERE) — IN/Contains, full-text predicates;
• Joins — join types, APPLY/LATERAL, ClickHouse strictness and GLOBAL JOIN;
• Grouping and aggregates — ROLLUP/CUBE, the aggregate families;
• Sorting and paging — Limit/Offset, LIMIT n BY expr;
• Scalar functions — arrays, JSON, UUID, session/info, provider surfaces;
• Table-valued functions — generate_series, string_split, numbers, zeros;
• Query hints — table hints, OPTION (RECOMPILE), ClickHouse query modifiers;
• JSON support across providers — every JSON surface side by side;
• Range columns — native Range<T> types on PostgreSQL, a pair of scalar columns on the other providers.

Portable functions that are spelled differently on every database live on CommonFunctions and are documented with the concept pages: iif(condition, a, b), gen_random_uuid()/uuidv7(), and session/info functions such as current_user()/session_user()/current_schema()/version().

Null-safe comparison is one of them and is documented once, on the concept page: the portable SqlFunctions.Sql.is_not_distinct_from/is_distinct_from become PostgreSQL IS [NOT] DISTINCT FROM, SQLite IS/IS NOT and MySQL/MariaDB <=>, and are emulated with a guarded is null/= predicate on SQL Server and ClickHouse. See Null-safe comparison.

See also

• Limitations and out-of-scope features
• Provider overview

---

Source: src/nextorm.core/DataContext/Dialect/ISqlDialect.cs, src/nextorm.core/DataContext/Dialect/SqlDialectBase.cs, src/nextorm.{postgres,sqlserver,clickhouse}/*Dialect.cs.