Table of Contents

Provider-specific features

This page covers behaviour that is provider-dependent rather than portable: the active dialect has to advertise the matching Supports* capability, and the providers that do not opt in reject the construct with NotSupportedException (FOR JSON/FOR XML on SQL Server, row locking on PostgreSQL/MySQL/MariaDB/SQL Server). Features that every provider implements — even when the storage or the spelling differs — are documented on concept pages instead, for example range columns (a native range type on PostgreSQL, a pair of scalar columns elsewhere) or JSON support across providers. The closing table lists spelling differences that do not gate a feature at all.

JSON output (SQL Server)

ForJson is a terminal operator: it executes the query and returns the whole result set as one JSON document (SupportsForJson). Because it is terminal it applies no implicit TOP 1, so the document covers every row; the query element type is irrelevant since the database returns a single document column:

string? json = dataContext.From<IComplexEntity>()
    .Select(e => new { e.Id, e.String })
    .ForJson(ForJsonMode.Path, root: "items", includeNullValues: true);

select id, somestring
 from complex_entity for json path, root('items'), include_null_values

Path shapes the document from the projection aliases, Auto from the table structure. ForJson returns null when the query produces no rows (SQL Server returns SQL NULL for an empty FOR JSON result). The clause is placed after ORDER BY and before a trailing OPTION (...); other providers throw NotSupportedException. Use WithForJson to only attach the clause and keep the command composable (for further hints or SQL inspection).

XML output (SQL Server)

ForXml is the XML counterpart and terminal (SupportsForXml); it supports RAW, AUTO, EXPLICIT and PATH, with an optional row element name, a ROOT('...') wrapper and the ELEMENTS flag:

string? xml = dataContext.From<IComplexEntity>()
    .Select(e => new { e.Id })
    .ForXml(ForXmlMode.Raw, elementName: "row", root: "items", elements: true);

select id
 from complex_entity for xml raw('row'), root('items'), elements

Like ForJson, ForXml returns null for an empty result set, and WithForXml attaches the clause without executing. FOR JSON and FOR XML are mutually exclusive; combining them throws NotSupportedException.

Row locking (FOR UPDATE / FOR SHARE)

ForUpdate and ForShare lock the selected rows until the surrounding transaction ends. PostgreSQL, MySQL and MariaDB emit a trailing clause placed last, after WHERE, ORDER BY and a page request; SQL Server attaches a WITH (updlock)/WITH (holdlock) table hint to every physical source of the statement:

var rows = await dataContext.From<SimpleEntity>()
    .Where(x => x.Id > 5)
    .ForUpdate()
    .ToListAsync();

-- PostgreSQL / MySQL / MariaDB
select id
 from simple_entity where (id > 5) for update

-- SQL Server
select id
 from simple_entity with (updlock) where (id > 5)

ForUpdate() locks rows exclusively; ForShare takes a shared lock — PostgreSQL renders for share, MySQL/MariaDB render lock in share mode, and SQL Server renders holdlock (shared) versus updlock for ForUpdate. The whole-query clause is implemented by PostgreSQL, MySQL, MariaDB and SQL Server (Lock; SQL Server uses ILockRenderer.UsesTableHints); every other provider throws NotSupportedException when the SQL is built.

Whole-query vs source-level lock

A whole-query ForUpdate()/ForShare() locks the statement; a source-level lock targets one physical source and is configured through that source's options:

The source is addressed by its SQL alias, so the lock never leaks to a neighbouring join, to another instance of the same CLR type or to another alias:

var rows = await dataContext.From<SimpleEntity>(o => o.ForUpdate())
    .Join<ComplexEntity>((s, c) => s.Id == c.Id, j => j.ForShare())
    .Select(p => new { p.Item1.Id })
    .ToListAsync();

-- PostgreSQL
select t1.id
 from simple_entity as "t1"
 join complex_entity as "t2" on cast(t1.id as bigint) = t2.id
for update of "t1"
for share of "t2"

-- MySQL
select t1.id
 from simple_entity as `t1`
 join complex_entity as `t2` on cast(t1.id as signed) = t2.id
for update of `t1`
for share of `t2`

PostgreSQL and MySQL render one targeted FOR UPDATE OF <alias>/FOR SHARE OF <alias> clause per locked source. Because LOCK IN SHARE MODE has no OF form, a MySQL source-level share always uses the newer FOR SHARE OF spelling. A whole-query and a source-level lock are mutually exclusive — combining them throws InvalidOperationException — and a source-level lock is only valid on a physical table source: FromSql, a query/builder subquery and a CTE source throw NotSupportedException.

SQL Server. A whole-query ForUpdate()/ForShare() places the lock hint on every FROM/JOIN physical source of the statement. A source-level lock places the hint on the chosen source only.

MariaDB has no FOR UPDATE OF/FOR SHARE OF grammar, so a source-level lock is not supported: it is rejected deterministically with NotSupportedException (fail-closed) instead of emitting an unsupported OF, substituting a whole-query lock or silently ignoring the target. Whole-query ForUpdate()/ForShare() keep MariaDB's own grammar (for update, lock in share mode).

Pass a LockWaitMode to control what happens when another transaction already holds the row: NoWait fails immediately instead of waiting, and SkipLocked leaves the locked rows out of the result — the standard way to build a queue or worker pool:

var claimed = await dataContext.From<Job>()
    .Where(x => x.State == "pending")
    .ForUpdate(LockWaitMode.SkipLocked)
    .ToListAsync();

-- PostgreSQL / MySQL / MariaDB
select id
 from job where (state = 'pending') for update skip locked

-- SQL Server (READPAST approximates SKIP LOCKED)
select id
 from job with (updlock, readpast) where (state = 'pending')

PostgreSQL and MySQL append the trailing nowait/skip locked (FOR UPDATE/FOR SHARE [NOWAIT | SKIP LOCKED]); a shared lock with a wait mode switches MySQL from lock in share mode to for share, because LOCK IN SHARE MODE takes no lock option. MariaDB appends the mode to both for update and lock in share mode (NOWAIT on 10.3+, SKIP LOCKED on 10.6+). SQL Server adds nowait or readpast to the same table hint (with (updlock, nowait) / with (updlock, readpast)); readpast skips any locked row, not only a row locked by another writer, so it approximates rather than exactly matches SKIP LOCKED. Wait is the default and keeps the blocking behaviour.

Provider differences

Provider Behaviour
SQLite Column aliases are single-quoted (as 'Calc'); derived tables do not require an alias.
SQL Server Column aliases are bracket-quoted (as [Calc]); every derived table must be aliased (as [t1]). String concatenation uses +.
PostgreSQL Column aliases are double-quoted (as "Calc"); derived tables must be aliased (as "t1").
MySQL Column aliases are backtick-quoted (as `Calc`); every derived table must be aliased (as `t1`). String concatenation uses concat(a, b).
MariaDB Same as MySQL: backtick-quoted aliases, aliased derived tables and concat(a, b) concatenation.
ClickHouse Column aliases are backtick-quoted (as `Calc`); every derived table must be aliased (as `t1`). String concatenation uses concat(a, b).
In-memory No SQL is generated; the projection delegates are compiled and run over in-memory objects.

See also

Edit this page