MySQL provider

Use nextorm.mysql for MySQL 8.x; it renders @name parameters, backtick-quoted identifiers, concat(...) concatenation, limit/offset paging and the standard-deviation/variance aggregate names.

Prerequisites: Provider overview · Quickstart

Overview

MySqlDataContext (src/nextorm.mysql/MySqlDataContext.cs) wraps MySqlConnector. It creates a MySqlConnection from the connection string and returns Instance from its Dialect property. MySqlDialect (src/nextorm.mysql/MySqlDialect.cs) is the dialect:

parameter placeholder @name;
identifiers and aliases are quoted with backticks;
string concatenation uses the concat(a, b, ...) function — MySQL's infix || is a logical OR unless the PIPES_AS_CONCAT SQL mode is set, so the dialect never emits it;
MakeCoalesce renders coalesce(a, b);
MakeStringLength renders char_length(x) (MySQL's length() counts bytes);
MakeNow renders now() for local time and utc_timestamp() for UTC;
stdev/stdevp/var/varp map to stddev_samp/stddev_pop/var_samp/var_pop (stddev and variance are the population synonyms in MySQL);
a CLR conversion is rendered with a MySQL CAST target (signed/unsigned for the integer types, double, decimal, char, datetime) rather than the ANSI bigint/integer;
the integer ones-complement renders as (-(x) - 1), because MySQL's ~ yields an unsigned 64-bit value that overflows the signed CLR integer;
the ESCAPE character of a LIKE predicate is emitted as the mode-independent _utf8mb4 X'5c' token, so NO_BACKSLASH_ESCAPES, ANSI_QUOTES and the driver's NoBackslashEscapes cannot change its meaning;
paging is limit n / limit n offset m; an offset without a limit becomes limit 18446744073709551615 offset m, because MySQL only accepts offset together with limit;
the session/information family (SessionInfoFunctions) renders SqlFunctions.Sql.current_user/session_user/current_database/version as current_user()/session_user()/ database()/version() and current_schema as schema();
the arbitrary-value aggregate SqlFunctions.Sql.any_agg renders as ANY_VALUE(x) (SupportsAnyValueAggregate, MySQL 5.7+);
the portable conditional SqlFunctions.Sql.iif renders as if(condition, a, b) (Iif, IIifRenderer.Render).

The variability aggregates follow the shared cardinality contract: Stdev/Var are null for zero or one contributing value, Stdevp/Varp are null for zero and 0.0 for one. One provider limitation is independent of the nullable result: MySQL 8.4 rejects DISTINCT inside STDDEV_SAMP/STDDEV_POP/VAR_SAMP/VAR_POP (stddev_samp(distinct x) fails with a syntax error). The renderer keeps emitting that spelling for the *_distinct forms, so only the SQL text is guaranteed and a live distinct variance query is not claimed as supported on MySQL 8.4.

Registering the provider

Two overloads are available on DataContextBuilder (src/nextorm.mysql/DI/MySqlDataContextOptionsBuilderExtensions.cs):

using NextORM.Core;
using NextORM.MySql;

// From a connection string.
var builder = new DataContextBuilder()
    .UseMySql("Server=localhost;Port=3306;Database=app;User ID=app;Password=secret");

// From an existing, caller-owned connection.
using var connection = new MySqlConnector.MySqlConnection("Server=localhost;Database=app");
var byConnection = new DataContextBuilder().UseMySql(connection);

using var ctx = builder.CreateDataContext();   // IDataContext

You can also construct the context directly (this is what the provider tests do):

using NextORM.Core;
using NextORM.MySql;

using IDataContext ctx = new MySqlDataContext(
    "Server=localhost;Database=app;User ID=app;Password=secret", new DataContextBuilder());

String concatenation

var query = ctx.From<ISimpleEntity>().Select(x => new { Label = "id:" + x.Id });

select concat('id:', id) as `Label`
 from simple_entity

Paging

ctx.From<ISimpleEntity>().Page(5, 10).Select(x => x.Id);   // limit 5 offset 10
ctx.From<ISimpleEntity>().Offset(10).Select(x => x.Id);    // limit 18446744073709551615 offset 10

String literals and parameters

Every inline text value — including an operand in a position where the grammar accepts only a literal — is rendered as the mode-independent _utf8mb4 X'…' form, an uppercase UTF-8 hex string, so neither sql_mode nor ANSI_QUOTES, NO_BACKSLASH_ESCAPES or the driver's NoBackslashEscapes can change its meaning. An ORM-owned text parameter slot in a read query keeps its @name placeholder in the cached command and is lowered to the same _utf8mb4 X'…' literal at execution time: the driver parameter is omitted for that execution, and the command is restored to its canonical text and bindings before the next one. Mutations (INSERT/UPDATE/DELETE) are never prepared, so their text values are lowered at render time and no template is kept.

GROUP_CONCAT ... SEPARATOR is a grammar-token position where the engine accepts only a text literal. A constant separator is rendered as a bare apostrophe-doubled literal from the accepted set U+0020–U+007E excluding the backslash; a non-constant, null, backslash-containing, NUL-containing or otherwise non-printable-ASCII separator fails closed with NotSupportedException naming MySQL/MariaDB — no placeholder, no invalid SQL, and the value is never echoed.

var query = ctx.From<IComplexEntity>().Where(e => e.String == "O'Brien").Select(e => e.Id);

-- the text parameter slot is lowered to a literal for this execution
select `Id` as `Id`
 from complex_entity
 where `String` = _utf8mb4 X'4F27427269656E'

A constant SEPARATOR keeps the literal form:

var names = ctx.From<IComplexEntity>().Select(e => SqlFunctions.Sql.string_agg(e.String, ","));

select group_concat(`String` separator ',')
 from complex_entity

Bulk insert

The native path is MySqlBulkCopy, a LOAD DATA LOCAL INFILE load, and it needs both prerequisites: AllowLoadLocalInfile=true on the connection string (client side) and local_infile=1 as the server variable @@GLOBAL.local_infile. The server value is read with a read-only preflight (select @@global.local_infile), and nextorm never enables either one. Without them Auto falls back only when the portable path honours the whole request and Native refuses by name; a runtime LOAD failure is never replayed through the SQL path.

The source is read in bounded chunks (MaxBatchSize, default 10 000 rows); each chunk is one load.
KeepIdentity writes the explicit identity column under a scoped NO_AUTO_VALUE_ON_ZERO guard, so an explicit 0 is not replaced by a generated value.
IgnoreDuplicates maps to ConflictOption.Ignore on the native load and INSERT IGNORE on the portable statement.
KeepNulls=false needs a default-equivalent shape; CheckConstraints=false, TableLock and FireTriggers are refused (MySQL always checks constraints), and Returning* is refused — read keys with CreateInsertBuilder.
DatabaseName renders db.table; ServerName is refused (no rerouting). Timeout is one whole-invocation budget; an expired budget is a TimeoutException and cancellation wins.

See Bulk insert.

Provider differences

Aspect	MySQL
Parameter placeholder	`@name`
Concat	`concat(a, b)`
Coalesce	`coalesce`
Boolean literal	`1` / `0` (aliases `true` / `false`)
Identifier quoting	backticks (as `t1`)
Derived table alias	required
TVF alias	required
`FULL JOIN`	not supported (right join is)
`*ALL`	not supported (MySQL 8.0.31 has `INTERSECT`/`EXCEPT`, but not the `ALL` variants)
Text JSON	`json_value`/`json_query`/`json_modify`/`isjson` (over `JSON_EXTRACT`/`JSON_UNQUOTE`/`JSON_SET`/`JSON_VALID`)
Session/info functions	`current_user()`, `session_user()`, `schema()`, `database()`, `version()`
Arbitrary-value aggregate	`ANY_VALUE(x)`
Conditional function	`iif(cond, a, b)` → `if(cond, a, b)`
Window percentiles	not supported: `percentile_cont`/`percentile_disc` on a window throw `NotSupportedException` (MySQL has neither the functions nor `WITHIN GROUP`; `PERCENTILE_CONT` is MariaDB-only)
Median (analytic)	not supported: `SqlFunctions.MySql.median(v).Over(...)` throws `NotSupportedException("The median function is not supported by MySQL.")`
Generic percentile	not supported: `SqlFunctions.MySql.percentile(fraction, v)` throws `NotSupportedException("The percentile function is not supported by MySQL.")`
LOB streaming (`ToStream`/`ToTextReader`)	`NotSupportedException`

MySQL 8.0.31 and later support INTERSECT/EXCEPT; the dialect rejects the *ALL variants with a NotSupportedException, matching the engine.

Table of Contents