Provider Reference

The expression API is provider-agnostic — the same C# query compiles against every provider. What differs is the SQL each provider emits, the storage type of the JSON column, and a handful of behavioral edge cases. This page lays them out side-by-side so you can pick the right provider, predict generated SQL, and avoid provider-specific surprises.

For provider-specific install, setup, and feature notes see the per-provider pages:

Provider	Doc
SQLite	SQLite
SQLCipher (encrypted SQLite)	SQLCipher
PostgreSQL	PostgreSQL
SQL Server	SQL Server
MySQL	MySQL
DuckDB	DuckDB
Azure Cosmos DB	Cosmos DB
MongoDB	MongoDB
LiteDB	LiteDB
IndexedDB (Blazor WASM)	IndexedDB

Quick comparison

Capability	SQLite	SQLCipher	PostgreSQL	SQL Server	MySQL	DuckDB	CosmosDB	MongoDB	LiteDB	IndexedDB
Storage column type	TEXT	TEXT	JSONB	JSON (SQL Server 2025+ / Azure SQL)	JSON	JSON (DuckDB native)	native JSON (string data envelope)	BsonDocument	BsonDocument	object store record
Query<T>(string sql, ...) raw SQL	yes	yes	yes	yes	yes	yes	yes (Cosmos SQL)	no	no	no
CreateIndexAsync<T>(x => x.Prop)	yes	yes	yes	yes (persisted computed column + filtered index)	yes	yes (functional index on json_extract_string)	n/a (Cosmos auto-indexes)	n/a (Mongo indexes manually)	n/a	n/a (schema-time only)
Composite / multi-column index	yes	yes	yes	yes (persisted computed columns + composite index)	yes	yes	n/a (Cosmos auto-indexes)	n/a (Mongo indexes manually)	n/a	n/a (schema-time only)
Predicate translated to SQL	yes	yes	yes	yes	yes	yes	yes	partial — server-side filter + C# evaluation	no — runs in C# after load	no — runs in C# after load
Upsert deep merge (RFC 7396)	yes (json_patch)	yes	shallow only (jsonb ||)	shallow only (hand-rolled OPENJSON)	yes (JSON_MERGE_PATCH)	yes (json_merge_patch)	yes (C# recursive merge)	yes (C# recursive merge)	yes (C# recursive merge)	yes (C# recursive merge)
Spatial (WithinRadius, etc.)	yes (R*Tree sidecar)	yes	no	no	no	no	yes (native GeoJSON)	no	no	no
Hot Backup()	yes	yes (encrypted)	no	no	no	no	no	no	yes	no
Concurrent writers	reader-many / writer-one	same	yes	yes	yes	reader-many / writer-one	yes	yes	single-process	single browser tab
Transactions	native	native	native	native	native	native	compensating (track inserts, undo on failure)	compensating (replica-set required for native ACID)	bulk insert in one tx	one IDB transaction
BatchInsert chunk size	500	500	500	500	500	500	100 (Cosmos TransactionalBatch limit)	InsertMany (ordered)	bulk insert in one tx	one IDB transaction

The JSON column is always named Data across every SQL provider. Companion columns are Id, TypeName, CreatedAt, UpdatedAt.

JSON syntax cheat-sheet

The operations below all run inside the generated SQL — you never write these yourself with the LINQ builder. They matter when you write raw SQL via Query<T>("...", parameters) or when debugging via the Logging callback.

Extract a scalar property ($.prop)

Provider	Generated expression
SQLite / SQLCipher	json_extract(Data, '$.prop')
PostgreSQL	Data #>> '{prop}'
SQL Server	JSON_VALUE(Data, '$.prop')
MySQL	NULLIF(JSON_UNQUOTE(JSON_EXTRACT(Data, '$.prop')), 'null')
DuckDB	json_extract_string(Data, '$.prop')
CosmosDB	c.data.prop
MongoDB	data.prop (translated to BSON field path)

Set a property in place (SetProperty)

Provider	Function
SQLite / SQLCipher	json_set(Data, '$.prop', @value)
PostgreSQL	jsonb_set(Data, '{prop}', to_jsonb(@value))
SQL Server	JSON_MODIFY(Data, '$.prop', @value)
MySQL	JSON_SET(Data, '$.prop', CAST(@value AS JSON))
DuckDB	json_merge_patch(Data, '{"prop":@value}'::JSON) (path folded into a synthetic merge patch)
MongoDB	$set: { "data.prop": @value }

Remove a property (RemoveProperty)

Provider	Function
SQLite / SQLCipher	json_remove(Data, '$.prop')
PostgreSQL	Data #- string_to_array('prop', ',')
SQL Server	JSON_MODIFY(Data, '$.prop', NULL)
MySQL	JSON_REMOVE(Data, '$.prop')
DuckDB	json_merge_patch(Data, '{"prop":null}'::JSON) (RFC 7396 null = delete)
MongoDB	$unset: { "data.prop": 1 }

Iterate child collections (Any / Count / collection aggregates)

Provider	Mechanism
SQLite / SQLCipher	json_each(Data, '$.lines')
PostgreSQL	jsonb_array_elements(Data #> '{lines}')
SQL Server	OPENJSON(Data, '$.lines')
MySQL	JSON_TABLE(Data, '$.lines[*]' COLUMNS(value JSON PATH '$'))
DuckDB	unnest(CAST(json_extract(Data, '$.lines') AS JSON[]))

Upsert merge-patch depth — read this

Upsert is documented as RFC 7396 JSON Merge Patch (deep merge of objects, scalar/array replacement). That holds for SQLite, MySQL, DuckDB, CosmosDB, MongoDB, LiteDB, and IndexedDB. It does not hold for PostgreSQL or SQL Server, which only perform a shallow top-level merge.

What this means in practice — given an existing document:

{ "name": "Alice", "address": { "city": "Portland", "zip": "97201" } }

and an Upsert patch:

{ "address": { "city": "Seattle" } }

Provider	Result of address after upsert
SQLite / MySQL / DuckDB / CosmosDB / MongoDB / LiteDB / IndexedDB	{ "city": "Seattle", "zip": "97201" } (deep merge — zip preserved)
PostgreSQL / SQL Server	{ "city": "Seattle" } (shallow merge — zip lost)

Caution

On PostgreSQL and SQL Server, never send a partial nested object to Upsert unless you intend to replace the whole sub-object. Either include all fields of the nested object, use SetProperty for individual nested fields, or use full Update to replace the document.

SQL Server CreateIndexAsync

SQL Server cannot index a JSON_VALUE expression directly, so CreateIndexAsync<T>(x => x.SomeProperty) adds a PERSISTED computed column and builds a filtered index on it:

ALTER TABLE [documents]
    ADD cc_idx_json_User_name AS CAST(JSON_VALUE(Data, '$.name') AS NVARCHAR(450));

CREATE INDEX idx_json_User_name
    ON [documents] (cc_idx_json_User_name)
    WHERE TypeName = N'User';

DropIndexAsync drops both the index and the backing computed column. The other SQL providers (SQLite, PostgreSQL, MySQL) emit partial expression / functional indexes — see Indexes for the exact DDL.

Storage type implications

PostgreSQL — JSONB, not JSON

The provider creates the Data column as JSONB. This is the binary form: parsed once on write, stored without whitespace, key order not preserved. Lookups are fast and the column can be indexed with expression indexes (used by CreateIndexAsync). If you ever need GIN indexes (e.g. for @> containment queries from outside the library), add them yourself — the library does not.

SQL Server — native JSON type (2025+)

The provider uses the new JSON storage type, not nvarchar(max). This requires SQL Server 2025 or later, or Azure SQL with the JSON-type preview enabled. Earlier SQL Server versions will fail at table creation.

MySQL — JSON (and value unwrapping)

MySQL’s JSON_EXTRACT returns a JSON-typed value, not a SQL string — comparing it directly to a bound parameter would fail. The provider always wraps extraction in NULLIF(JSON_UNQUOTE(...), 'null') so a stored JSON null and a missing key both surface as SQL NULL. Generally invisible — relevant only when you write raw SQL.

DuckDB — native JSON column

DuckDB stores the Data column as its built-in JSON type (text-backed, parsed on read). The json extension is loaded automatically on every connection (INSTALL json; LOAD json;). DuckDB 0.10+ exposes json_merge_patch — Upsert uses it directly for true RFC 7396 deep merge with no read-merge-write round trip.

DuckDB has no json_set / json_remove. SetProperty and RemoveProperty fold the JSON path into a synthetic merge-patch document on the server using list_reduce(list_reverse(string_split(...)), ...) — same RFC 7396 semantics, different SQL. Generally invisible — relevant only when you’re reading the Logging callback.

DuckDB is single-process / reader-many / writer-one (similar to SQLite). It is not a multi-user server — pick it for embedded analytical workloads, on-device reporting, or pipelines that ingest Parquet/CSV via DuckDB’s native tooling alongside your document store.

CosmosDB — data is a stringified envelope

The Cosmos document layout is:

{
  "id": "...",
  "typeName": "User",
  "data": "{\"name\":\"Alice\",...}",
  "createdAt": "...",
  "updatedAt": "..."
}

Inside the SDK the data field is the serialized JSON of your document — accessed in Cosmos SQL as c.data.name. Partition key is /typeName. Cosmos auto-indexes every path by default; tune via the indexing policy on the container if cost matters.

MongoDB — BSON envelope + composite key

The Mongo document layout mirrors the SQL envelope:

{
  "_id": "{TypeName}:{Id}",
  "id": "...",
  "typeName": "User",
  "data": { "name": "Alice", "age": 25, "...": "..." },
  "createdAt": "...",
  "updatedAt": "..."
}

_id is the composite Cosmos-style key — this guarantees uniqueness per type/Id pair across the shared "documents" collection (or any custom collection mapped via MapTypeToCollection<T>()). The data sub-document is a real BSON object (not a stringified envelope), so MongoDB queries, indexes, and aggregations can target nested fields natively.

LINQ predicates are translated to a MongoDB FilterDefinition for the type/sort/skip/take phase. Complex sub-expressions outside the translated subset are evaluated in C# after a typed find. Raw SQL is not supported — pass LINQ.

RunInTransaction uses a compensating model rather than a multi-document session: inserts are tracked and deleted on failure, matching the CosmosDB provider. To use true ACID transactions, run MongoDB in a replica set and lift the constraint in your fork. Updates and removes inside a RunInTransaction callback are not compensated — if you need rollback semantics for updates, wrap the operations in your own session.

LiteDB / IndexedDB — predicates run client-side

These two providers do not translate LINQ predicates to a query language. They:

1. Load all documents of the type (filtered only by TypeName / object-store name) into memory.
2. Apply the compiled predicate in C#.
3. Apply ordering, pagination, and projection in C#.

For small datasets this is fine. For collections > a few thousand documents, expect linear scans on every query. There is no way to push a predicate down. If you need server-side filtering on a large dataset in Blazor WASM, use SQLite-in-WASM (see IndexedDB notes).

Raw SQL & parameter syntax

Query<T>(string whereClause, object? parameters) and QueryStream<T>(...) are supported by every SQL provider (SQLite, SQLCipher, PostgreSQL, SQL Server, MySQL, DuckDB) plus CosmosDB. LiteDB, MongoDB, and IndexedDB throw NotSupportedException — use the LINQ-based Query<T>() overload instead.

Parameter syntax in the whereClause you write:

Provider	Parameter prefix
SQLite, SQLCipher	@name
PostgreSQL	@name (Npgsql rewrites to $1 style internally)
SQL Server	@name
MySQL	@name
DuckDB	@name
CosmosDB	@name (Cosmos SQL native)

// Same C# call — provider rewrites the JSON extraction syntax under the hood
var results = await store.Query<User>(
    /* SQLite */    "json_extract(Data, '$.name') = @name",
    /* Postgres */  // "Data #>> '{name}' = @name"
    /* SQL Server */ // "JSON_VALUE(Data, '$.name') = @name"
    new { name = "Alice" });

When porting raw SQL across providers, the WHERE-clause syntax is yours to translate — only the LINQ builder is provider-agnostic.

Identifier quoting & reserved words

SQLite quotes table identifiers with " so a type named Order or User works as a mapped table. The other providers follow their native quoting rules; if you hit a reserved-word collision, prefer an explicit non-reserved name via MapTypeToTable<T>("orders").

Behavioral differences worth knowing

Concern	What differs
LIKE case sensitivity	SQLite: ASCII case-insensitive by default. PostgreSQL: case-sensitive (use ILIKE if you need otherwise). SQL Server / MySQL: depends on column / database collation.
Stored JSON null vs missing key	Distinguishable in SQLite, PostgreSQL, SQL Server. MySQL collapses both to NULL via the NULLIF(...,'null') wrapper.
jsonb key ordering	PostgreSQL’s JSONB does not preserve key insertion order. Affects byte-for-byte diffs of the stored blob (does not affect GetDiff, which works at the C# object level).
Numeric coercion	SQLite is dynamically typed — a JSON "42" string compares equal to integer 42. Other providers do not coerce.
Decimal precision	All providers serialize/deserialize via System.Text.Json, so decimal precision is preserved as JSON numbers — but Postgres JSONB re-parses, and very large numerics may round-trip through double in extracted scalars. Prefer SetProperty for exact-value updates.

When to pick which provider

Use case	Recommendation
Mobile / desktop / single-process	SQLite (or SQLCipher if data must be encrypted at rest)
ASP.NET app, owned database	PostgreSQL — JSONB indexability, mature concurrency
Existing SQL Server estate, on SQL Server 2025+	SQL Server — full feature parity except spatial and hot Backup()
Existing MySQL estate	MySQL — full feature parity except spatial
Embedded analytics, on-device aggregates, Parquet/CSV ingest alongside docs	DuckDB — native JSON type, server-side json_merge_patch, columnar engine
Globally distributed, serverless / pay-per-RU	CosmosDB — native spatial, automatic indexing, but watch RU cost on unindexed paths
Existing MongoDB estate / document-native workloads	MongoDB — typed BSON storage, full nested-field indexing via Mongo tooling, compensating transactions
Embedded, no native dependencies, Windows-friendly	LiteDB — client-side query evaluation, fine for small datasets
Blazor WebAssembly, client-only persistence	IndexedDB — zero native deps; if you need predicate push-down on large data, switch to SQLite-in-WASM