Dapper Dialects

Dapper: Dialects

Everything structural — which conditions apply, how groups combine, how paging maps onto grouping levels — lives in SqlQueryCompiler and is shared by every database. A dialect supplies only the handful of things that genuinely differ. That is why adding an engine is a small class rather than a reimplementation.

---

The comparison table

	SQL Server	PostgreSQL	MySQL / MariaDB	Oracle	SQLite
ProviderType	MSSQL	PostgreSQL	MySQL	Oracle	SQLite
Identifier quoting	[name]	"name"	`name`	"name"	"name"
Escapes	] → ]]	" → ""	` → ``	" → ""	" → ""
Parameter prefix	@	@	@	:	@
Default schema	dbo	public	(none)	(none)	(none)
Paging	OFFSET n ROWS FETCH NEXT m ROWS ONLY	LIMIT m OFFSET n	LIMIT m OFFSET n	OFFSET n ROWS FETCH NEXT m ROWS ONLY	LIMIT m OFFSET n
Paging needs ORDER BY	yes	no	no	no	no
ORDER BY fallback	(SELECT NULL)	1	1	1	1
Null ordering emitted	(none — default matches)	NULLS FIRST / NULLS LAST	(none)	NULLS FIRST / NULLS LAST	(none)
LIKE escape clause	ESCAPE '\'	ESCAPE '\'	ESCAPE '\\'	ESCAPE '\'	ESCAPE '\'
LIKE chars escaped	\ % _ [	\ % _	\ % _	\ % _	\ % _
Table-valued functions	✅	✅	—	✅ pipelined	—
Stored procedures	✅	✅	✅	—	—

MySQL's escape clause is doubled ('\\') because MySQL processes backslash escapes inside string literals; the other engines take the backslash at face value.

---

Per-engine notes

SQL Server (SqlServerDialect)

The only engine that requires an ORDER BY before OFFSET … FETCH. When a query has no usable sort column, the compiler emits ORDER BY (SELECT NULL) — valid, and imposes no meaningful order.

Its LIKE escaping additionally covers [, which opens a character class in T-SQL's pattern syntax and is not a wildcard on any other engine.

Stored procedures are called with named arguments, so the caller's parameter order does not have to match the procedure's declaration:

EXEC [dbo].[usp_GetUserReport] @IncludeDeleted = @p0

With no arguments, just EXEC [dbo].[usp_X].

PostgreSQL (PostgreSqlDialect)

Defaults to nulls last ascending, which is the opposite of most engines, so the dialect states placement explicitly on every ordering term.

Set-returning functions are selected from exactly like a table, so TVF needs no special wrapping:

SELECT * FROM "public"."tvf_GetUsersByTenant"(@p0)

Procedures are called with named notation:

SELECT * FROM "public"."usp_GetUserReport"(IncludeDeleted => @p0)

PostgreSQL is also the engine that surfaced the loosely-typed-value problem: it rejects integer > text outright rather than coercing, which is why values are coerced to the column's real type before binding. See Query Semantics.

MySQL / MariaDB (MySqlDialect)

No schema layer above the database, so the schema part of a query object is omitted unless you set one explicitly. DefaultSchema is empty and ForObject("Users") emits `Users`.

No table-valued functions. DapperObjectType.TVF throws NotSupportedException.

Procedure arguments are positional — CALL takes no named notation — so they are passed in the order of the Parameters dictionary:

CALL `usp_GetUserReport`(@p0, @p1)

Because the dictionary's order decides the mapping, use an ordered dictionary or be careful about insertion order when calling a MySQL procedure with more than one argument.

MariaDbConnection maps to this dialect too.

Oracle (OracleDialect)

The engine that needs the most care. Handled for you:

• Named binding is forced on. ODP.NET binds positionally unless BindByName is set, which would pair :p0 and :p1 by declaration order rather than by name. QueryForge sets it reflectively.
• Nulls are ordered explicitly — Oracle defaults to nulls last ascending.
• Derived tables are aliased without AS, because Oracle rejects FROM (…) AS x.
• Pipelined functions are unnested with TABLE():

  SELECT * FROM TABLE("TVF_GETUSERSBYTENANT"(:p0))

Your responsibility:

• Write object names the way Oracle stored them. Oracle folds unquoted identifiers to upper case; QueryForge quotes what it is given. So a table created as CREATE TABLE Users is stored as USERS and must be named "USERS" here. Column names are discovered from the result set and are always correct.
• No stored procedures. Oracle returns result sets through REF CURSOR output parameters, which cannot be expressed as portable command text. BuildStoredProcedureCall throws NotSupportedException naming the alternative: wrap the logic in a pipelined function and use DapperObjectType.TVF.
• Empty strings are NULL. A filter for Equals "" behaves as IS NULL on Oracle and as an empty-string match everywhere else. This is the database's own behaviour.
• Paging needs Oracle 12c or later, where OFFSET … FETCH NEXT became available.

SQLite (SqliteDialect)

No schemas beyond attached databases, no table-valued functions, no stored procedures. Both TVF and SP throw NotSupportedException.

Useful in its own right for desktop and embedded applications, and useful in a test suite: it exercises the whole compile-and-execute path in-process without a database server, which is why the repository's own conformance suites run against it on every build.

---

The ISqlDialect contract

namespace PepperX.QueryForge.Dapper.Compiler;

public interface ISqlDialect
{
    DapperDatabaseProvider ProviderType { get; }
    string DefaultSchema { get; }

    string QuoteIdentifier(string identifier);
    string ParameterReference(string name);

    string PagingClause(int offset, int size);
    bool RequiresOrderByForPaging { get; }
    string OrderByFallback { get; }

    string NullOrdering(SortOrder order);

    string EscapeLikeValue(string value);
    string LikeEscapeClause { get; }

    bool SupportsTableValuedFunctions { get; }
    bool SupportsStoredProcedures { get; }

    string BuildSource(
        string schema, string name, DapperObjectType type,
        IReadOnlyList<string> argumentReferences);

    string BuildStoredProcedureCall(
        string schema, string name,
        IReadOnlyList<KeyValuePair<string, string>> argumentNames);
}

Member by member

Member	Contract
ProviderType	Which engine this dialect serves. Registering a dialect replaces any previous one for the same value.
DefaultSchema	Used when a query object leaves Schema empty. Return empty for engines with no schema layer.
QuoteIdentifier	Wrap an identifier so reserved words and unusual names are safe. Must neutralize the closing delimiter. Throws ArgumentException on null or whitespace.
ParameterReference	Render a reference from a bare name: "p0" → "@p0".
PagingClause	The clause limiting a result to one page, appended after ORDER BY. Offset and size are integers computed by the compiler, never caller text.
RequiresOrderByForPaging	true when the paging clause is only valid after an ORDER BY.
OrderByFallback	An expression satisfying ORDER BY without imposing a meaningful order, used when nothing else sorts.
NullOrdering	The clause pinning null placement, including a leading space, or empty when the engine's default already matches nulls-first-ascending.
EscapeLikeValue	Escape LIKE wildcards so the caller's value matches literally. Must be consistent with LikeEscapeClause.
LikeEscapeClause	The matching ESCAPE clause, including a leading space.
SupportsTableValuedFunctions	false makes DapperObjectType.TVF throw with a clear message.
SupportsStoredProcedures	false makes DapperObjectType.SP throw with a clear message.
BuildSource	The FROM source for a table, view or TVF. argumentReferences is empty for tables and views, and positional for a TVF.
BuildStoredProcedureCall	The statement invoking a procedure. argumentNames pairs each caller-supplied name with its parameter reference, so you can emit named or positional syntax.

Contract obligations

A dialect that does these things will work with the shared compiler:

1. Never inline a caller's value. Only identifiers, integers computed by the compiler, and fixed syntax may appear in returned strings.
2. Always neutralize your own closing delimiter in QuoteIdentifier. It is the last line of defence even though the compiler only passes whitelisted names.
3. Return null-ordering and escape clauses with a leading space, since they are concatenated directly.
4. Keep EscapeLikeValue and LikeEscapeClause consistent. If you escape with \, the clause must say \.
5. Throw NotSupportedException with a message naming the alternative rather than emitting SQL that cannot work.
6. Be stateless and thread-safe. One instance serves the whole application.

---

Registering a dialect

builder.Services.AddQueryForgeDapper();
builder.Services.AddQueryForgeDialect(new MyDialect());

AddQueryForgeDialect must be called after AddQueryForgeDapper, or it throws:

InvalidOperationException: Call AddQueryForgeDapper() before AddQueryForgeDialect().

Registering a dialect whose ProviderType matches a built-in one replaces it. That is the supported way to override behaviour for an engine QueryForge already ships — for example to change null ordering to match a legacy application:

public sealed class LegacyPostgresDialect : PostgreSqlDialect
{
    // The base type is sealed, so in practice you implement ISqlDialect and delegate.
}

The built-in dialects are sealed, so override by implementing ISqlDialect and forwarding to a private instance of the built-in one for the members you are not changing. See Extending QueryForge for a complete worked example.

Registration also updates the static registry behind the IDbConnection.QueryForgeAsync extension, so a custom dialect is available on that path too.

---

Adding a brand-new engine

Two things are needed:

1. A dialect implementing ISqlDialect.
2. Engine detection. DapperRegistry.Resolve(IDbConnection) maps a fixed set of connection type names to DapperDatabaseProvider values. An engine outside that set cannot be inferred from a connection, so you must either name your connection type to match an existing entry, or use the DapperDatabaseProvider overload internally.

DapperDatabaseProvider is a closed enum:

public enum DapperDatabaseProvider { MSSQL, MySQL, PostgreSQL, Oracle, SQLite }

An engine that is not one of those five has no value to register under today. The practical routes are to reuse the nearest member — Firebird under MSSQL, say — with a dialect that emits the right syntax, or to open an issue so a member can be added. Full discussion in Extending QueryForge.

---

Testing a dialect

Dialects are pure functions, so they test without a database:

[Fact]
public void Postgres_pins_null_ordering()
{
    var dialect = new PostgreSqlDialect();

    Assert.Equal(" NULLS FIRST", dialect.NullOrdering(SortOrder.Ascending));
    Assert.Equal(" NULLS LAST",  dialect.NullOrdering(SortOrder.Descending));
}

[Fact]
public void Quoting_neutralises_the_closing_delimiter()
{
    Assert.Equal("[a]]b]", new SqlServerDialect().QuoteIdentifier("a]b"));
    Assert.Equal("\"a\"\"b\"", new PostgreSqlDialect().QuoteIdentifier("a\"b"));
    Assert.Equal("`a``b`", new MySqlDialect().QuoteIdentifier("a`b"));
}

The repository carries a dialect portability suite that runs the same query through every dialect and asserts the invariants that must hold on all of them — no inlined values, quoted identifiers, a paging clause present, null ordering pinned. Adding a dialect and adding it to that suite is the whole job. See Testing.