SQL Dialects¶

A SQL dialect controls how identifiers are quoted, how parameters are prefixed, and how dialect-specific syntax (paging, stored procedures, string concatenation) is rendered.

Available dialects¶

The default dialect produces SQL that is broadly compatible with SQL Server. SqlServerDialect is a subclass that adds T-SQL-specific behaviours such as the EXEC stored procedure syntax and the + concatenation operator.

Passing a dialect to ToSql()¶

The recommended approach is to pass the dialect explicitly on each call. This is safe in any concurrent environment:

var dialect = new PostgreSqlDialect();

var sql = Sql
    .Select("Id", "Name")
    .From("Users")
    .Where(SqlExpression.Equal("Id", "UserId" + (SqlConstant)7))
    .ToSql(dialect);

SELECT [Id], [Name] FROM [Users] WHERE [Id] = @UserId

SELECT "Id", "Name" FROM "Users" WHERE "Id" = :UserId

SELECT `Id`, `Name` FROM `Users` WHERE `Id` = ?UserId

Dialect differences summary¶

Paging¶

Each dialect renders LIMIT/OFFSET paging differently:

See Ordering and Paging for full examples.

String concatenation¶

See Expressions — String concatenation for details.

Stored procedures¶

var sql = Sql.Call("sp_GetData", "Id" + (SqlConstant)1).ToSql(new SqlServerDialect());

EXEC sp_GetData @Id

var sql = Sql.Call("get_data", "id" + (SqlConstant)1).ToSql(new PostgreSqlDialect());

CALL get_data(:id)

PostgreSQL: parameter type casts¶

When querying PostgreSQL with typed columns (JSON, UUID, etc.), use SqlParameter<T> with the appropriate NpgsqlDbType. The PostgreSqlDialect appends the required ::type cast automatically:

// DbType.Guid  →  ::uuid
var guidParam = new SqlParameter("UserId", DbType.Guid, Guid.NewGuid());

// DbType.Xml  →  ::xml
var xmlParam = new SqlParameter("Payload", DbType.Xml, "<root/>");

// NpgsqlDbType.Jsonb  →  ::jsonb
var jsonbParam = new SqlParameter<NpgsqlDbType>("Data", NpgsqlDbType.Jsonb, "{}");

Writer settings¶

SqlWriterSettings lets you customise the rendering output:

These settings are passed through the SqlWriter and are primarily useful for advanced rendering scenarios or style preferences.

Setting a process-wide dialect¶

// Application startup only — single-threaded context
#pragma warning disable CS0618
SqlDialect.UseDialect(new PostgreSqlDialect());
#pragma warning restore CS0618

From that point on, calls to ToSql() without an explicit dialect will use the configured one.

Dependency injection¶

Dialect classes are stateless and safe to share across threads, so registering them as singletons is the correct lifetime.

Registration¶

// Program.cs / Startup.cs
services.AddSingleton<PostgreSqlDialect>();

If you want to inject by the base type as well:

services.AddSingleton<SqlDialect, PostgreSqlDialect>();
services.AddSingleton<PostgreSqlDialect>(sp => (PostgreSqlDialect)sp.GetRequiredService<SqlDialect>());

Consuming the dialect¶

Inject the dialect into your class and pass it to ToSql():

class UserRepository(PostgreSqlDialect dialect)
{
    public string GetAll() =>
        Sql.Select("Id", "Name")
           .From("Users")
           .ToSql(dialect);

    public string GetById(int id) =>
        Sql.Select("Id", "Name")
           .From("Users")
           .Where(SqlExpression.Equal("Id", "UserId" + (SqlConstant)id))
           .ToSql(dialect);
}

If you registered the dialect as SqlDialect, inject that instead:

class UserRepository(SqlDialect dialect) { … }

Custom dialects¶

Extend SqlDialect to support an additional database engine. Override the methods you need:

public class MyDialect : SqlDialect
{
    public override string FormatIdentifier(string identifier) => $"`{identifier}`";
    public override string FormatParameterName(string parameterName) => $"${parameterName}";
}

Overridable methods¶