Read Queries

Nagare.ReadQueries is a typed query API for projection-backed read models. Two tiers: an inline interpolated-SQL fluent surface for one-line queries, and a [ReadQuery] partial-method attribute for multi-line SQL with custom result shapes, streaming, and pagination. SQL is always the SQL — no LINQ-to-SQL translation, no expression trees, paste-able into psql/sqlite3 to debug.

Choosing your approach

Four orthogonal decisions cover most projection-query work. Get the first right and the rest follow.

1. Tier 1 fluent vs Tier 2 [ReadQuery] vs raw Dapper

Pick	When
Tier 1 — store.Where($"…").OrderBy(…).ToList()	One-line WHERE + ORDER BY against a single table, no joins. Stays inline with the projection class.
Tier 2 — [ReadQuery("…SQL…")] on a partial method	Multi-line SQL, joins, subqueries, aggregates, custom result shapes, streaming, pagination. SQL sits next to the method signature and pastes straight into a DB tool.
Raw Dapper — _connection.QueryAsync<T>(…)	Sprocs, dynamic SQL composition, anything ad-hoc. The escape hatch is always open; the typed layer doesn't fight you for skipping it.

Switch from Tier 1 to Tier 2 when: the WHERE clause grows past one line, you need a JOIN, the result shape differs from the row type, you want pagination/streaming, or you find yourself writing helper methods to compose interpolated strings. Tier 2 also gets a richer build-time check — the whole SQL body can be parsed against your declared columns, not just the OrderBy / Where arguments Tier 1 sees.

Stay on Tier 1 for: lookup-by-id, simple status filters, the kind of read that would otherwise be a 3-line Dapper call.

2. Columnar vs JSON projection vs hybrid

Pick	When
Columnar — one column per attribute	Stable schema; multiple fields participate in WHERE/ORDER BY; you read more than 2-3 fields per query. The 80% default.
JSON payload — single payload column (JSONB / TEXT / JSON)	Variable shape across records; sparse fields; deep nesting; the projection evolves faster than column migrations are willing to ship.
Hybrid — indexed columns for the hot path + JSON for the long tail	Most stabilised projections end up here. Index the 1-3 columns you actually filter on; keep the rest in JSON for evolvability.

Reach for JSON when migration friction outweighs query precision. See Scaling JSON projections for per-dialect index recipes — without an index, JSON filters scan the whole table.

3. Buffered (Task<IReadOnlyList<T>>) vs streaming (IAsyncEnumerable<T>)

Pick	When
Buffered	UI lists, paged tables, anything that fits comfortably in memory. The default.
Streaming	Export jobs, ETL, audit dumps, long-tail reports. Memory must not spike with table growth.

Rule of thumb: if the result set scales with table size (not page size), stream. Streaming holds the reader open for the whole enumeration — wrap consumers in await foreach and thread a CancellationToken for early-exit.

4. Offset pagination vs keyset pagination

Pick	When
Offset — .Limit(n).Offset(skip) (Tier 1) or LIMIT @n OFFSET @m (Tier 2)	Static datasets, internal tools, the first handful of pages. Cheap to write.
Keyset — Page<T> with ToPage(size, r => [r.Id])	Live data (rows arrive between page loads); deep pages; you need stable ordering across requests.

Keyset stays fast on large tables because it skips OFFSET 50000-style scans. Offset is fine while pages are shallow and the dataset isn't changing under you.

Declare the read model

using Nagare.ReadQueries;

[ReadModel("loan_status")]
public partial record LoanRow(string Id, string PatronId, string BookId, string Status);

The source generator emits static ReadModelTable Table (column list with CLR types) and static LoanRow Read(DbDataReader r) (reflection-free hydration). PascalCase parameters map to snake_case columns. Handled types: primitives, Guid, DateTime, DateTimeOffset, DateOnly, TimeOnly, byte[], enums, nullable variants.

Register at startup

builder.Services.AddSqliteReadQueries();
// or AddPostgresReadQueries / AddMySqlReadQueries / AddSqlServerReadQueries

builder.Services.AddReadQueries<LoanRow>(
    owningProjection: LoanStatusProjection.Identifier.Value);

owningProjection is optional and only affects the dashboard's "owning projection" label.

Tier 1 — inline fluent

Inject IReadQueries<LoanRow> wherever you'd put a repository; the projection itself is the usual home:

public class LoanStatusProjection
{
    private readonly IReadQueries<LoanRow> _store;

    public Task<LoanRow?> GetById(string id) =>
        _store.Where($"id = {id}").Single();

    public Task<IReadOnlyList<LoanRow>> Active() =>
        _store.Where($"status = {"Active"}").OrderBy("id").ToList();
}

Every {value} hole becomes a @p0/@p1/... parameter — strings included. SQL injection is impossible by construction.

Method	Effect
Where(handler) / All()	Start a query (with WHERE clause / unfiltered)
And(handler)	Append another WHERE clause, joined with AND
OrderBy(col) / OrderByDesc(col)	Sort by a column name (validated by NAG1001)
Limit(n) / Offset(n)	Paginate
ToList(ct)	Run, return IReadOnlyList<TRow>
Single(ct)	Single match or null; throws on > 1
FirstOrDefault(ct)	First match or null
Count(ct)	COUNT(*), returns int

Tier 2 — [ReadQuery] partial methods

For multi-line SQL, declare a partial method on a partial class and annotate with [ReadQuery("…SQL…")]. The generator emits the body; the return type drives dispatch.

public partial class LoanStatusProjection
{
    private readonly IReadQueries<LoanRow> _store;

    [ReadQuery("""
        SELECT id, patron_id, book_id, status
        FROM loan_status
        WHERE status = @status
        ORDER BY id ASC
        """)]
    public partial Task<IReadOnlyList<LoanRow>> ByStatus(string status);

    [ReadQuery("SELECT * FROM loan_status WHERE id = @id")]
    public partial Task<LoanRow?> ById(string id);

    [ReadQuery("SELECT COUNT(*) FROM loan_status WHERE status = @status")]
    public partial Task<int> CountByStatus(string status);
}

Method parameter names bind to SQL @param references case-insensitively. Optional last parameter CancellationToken ct is supported and threaded through.

Custom result shapes

When the SELECT doesn't match the row type, return a different record. The generator detects the mismatch, generates a hydrator from the projection record's primary-constructor parameters (positional, like Sqlx's query_as!), and dispatches via ExecuteProjection:

public sealed record LoanCountByStatus(string Status, int Count);

[ReadQuery("SELECT status, COUNT(*) AS count FROM loan_status GROUP BY status ORDER BY count DESC")]
public partial Task<IReadOnlyList<LoanCountByStatus>> StatusBreakdown();

The projection record needs a primary constructor — [ReadModel] is not required (it's not backing a table). Column order in the SELECT must match parameter order in the record.

Streaming with IAsyncEnumerable<T>

For large result sets, return IAsyncEnumerable<T> instead of Task<IReadOnlyList<T>>. The reader stays open for the duration of the enumeration; rows yield without buffering. Pair with CancellationToken for early-exit.

[ReadQuery("SELECT id, patron_id, book_id, status FROM loan_status ORDER BY id")]
public partial IAsyncEnumerable<LoanRow> StreamAll(CancellationToken ct = default);

Works for projection shapes too — IAsyncEnumerable<LoanCountByStatus> routes through ExecuteProjectionStream.

Keyset / cursor pagination

Page<T> carries items + a NextCursor token. The generator stays simple; you wrap a regular [ReadQuery] with PageExtensions.ToPage:

[ReadQuery("""
    SELECT id, patron_id, book_id, status
    FROM loan_status
    WHERE (@afterId IS NULL OR id > @afterId)
    ORDER BY id ASC
    LIMIT @pageSize
    """)]
private partial Task<IReadOnlyList<LoanRow>> ByIdPagedRaw(string? afterId, int pageSize);

public async Task<Page<LoanRow>> ByIdPaged(string? afterId, int pageSize)
{
    var rows = await ByIdPagedRaw(afterId, pageSize);
    return rows.ToPage(pageSize, r => [r.Id]);
}

Cursor.Encode(params object?[]) and Cursor.Decode(cursor) round-trip cursor values via base64-encoded JSON. Use Cursor.DecodeAs<T>(cursor, index) for typed extraction on the next call.

What the generator checks

The generator wires every [ReadQuery] method to the SQL and refuses to build if the wiring is off. The errors are loud on purpose — you find out at compile time, not when the projection runs in production:

• The enclosing class has to expose an IReadQueries<TRow> (field, property, or constructor parameter) so the generator knows which table to query.
• Every C# parameter has to appear as @name in the SQL, and every @name in the SQL has to have a matching C# parameter. Mismatches are an error either way.
• The return type has to be one of the supported shapes (Task<IReadOnlyList<T>>, Task<T?>, IAsyncEnumerable<T>, or a scalar like Task<int>).
• Custom result records need a primary constructor — the generator hydrates positionally from the SELECT columns.

Errors carry codes NAG3001–NAG3005 if you ever need to look one up.

Catching SQL typos at build time (opt-in)

Tell each [ReadQuery] which dialect it targets and Nagare will parse every column reference at build time. Typos fail the build:

[ReadQuery("""
    SELECT id, patron_id, book_id, status
    FROM loan_status
    WHERE status = @status
    """,
    Dialect = SqlDialect.Postgres)]
public partial Task<IReadOnlyList<LoanRow>> ByStatus(string status);

Misspell a column and you get:

error NAG4001: Column 'statux' referenced in [ReadQuery] SQL on '__ByStatus_Query_0'
               is not declared on the row/projection type
               (known columns: id, patron_id, book_id, status)

Set Dialect to Postgres, Sqlite, MySql, or SqlServer — each parses with the right dialect rules (quoted identifiers, JSON operators, vendor functions). SqlDialect.None is the default and skips validation.

A few honest caveats:

• The validator runs against SqlParserCS. If it can't parse a vendor-specific construct (PG arrays, SQL Server WITH (NOLOCK), etc.), it skips that query with a build warning — your runtime behaviour is unchanged.
• JSON path strings ('$.status', 'name') are opaque values, not column refs — the validator checks the column the JSON function reads from, not the keys inside the JSON.
• Disable per-project via <NagareValidateReadQueriesDisable>true</NagareValidateReadQueriesDisable> if you ever need to.

Catching column typos at build time

Typos in column names fail the build, not production. Three analyzers run in your IDE and in CI:

• OrderBy column names get checked strictly against the [ReadModel]. Misspell created_at as craeted_at and you see a build error before the test even runs:

  error NAG1001: Column 'craeted_at' is not declared on read model 'LoanRow'
                 (known columns: id, patron_id, book_id, status)

• Columns in interpolated Where / And clauses get a best-effort scan — anything that looks like col = …, col IN …, col LIKE … is checked. This one is a warning (not error) because it can't always tell a column from a table-prefixed (t.col) or function-wrapped (LOWER(col)) reference and skips those to stay quiet. Suppress per-project with <NoWarn>$(NoWarn);NAG1002</NoWarn> if you hit a false positive.
• [TenantScoped] projections get their INSERT and UPDATE statements scanned for the tenant column. Forget the column on a multi-tenant write and you get a warning before tenant data leaks across rows.

Querying JSON document projections

Sometimes a projection is more naturally a JSON document than a fixed set of columns — when the shape varies per record, or you'd rather add fields without a schema migration. Nagare projections already support this on every dialect (Postgres / MySQL JSONB, SQLite json1, SQL Server JSON). To query one, declare a [ReadModel] with a payload column and write the JSON operator in the query body:

[ReadModel("loan_documents")]
public partial record LoanDocumentRow(string Id, string Payload);

public partial class LoanDocumentQueries(IReadQueries<LoanDocumentRow> store)
{
    // Postgres: JSON arrow operators in WHERE
    [ReadQuery("""
        SELECT id, payload
        FROM loan_documents
        WHERE payload @> '{"status":"active"}'
        """, Dialect = SqlDialect.Postgres)]
    public partial Task<IReadOnlyList<LoanDocumentRow>> ActivePg();

    // SQLite: json_extract function (json1 extension, default in modern SQLite)
    [ReadQuery("""
        SELECT id, payload
        FROM loan_documents
        WHERE json_extract(payload, '$.status') = 'active'
        """, Dialect = SqlDialect.Sqlite)]
    public partial Task<IReadOnlyList<LoanDocumentRow>> ActiveSqlite();

    // Project JSON fields into a typed shape (custom projection)
    [ReadQuery("""
        SELECT id, json_extract(payload, '$.patron_id') AS patron_id, json_extract(payload, '$.amount') AS amount
        FROM loan_documents
        """, Dialect = SqlDialect.Sqlite)]
    public partial Task<IReadOnlyList<LoanSummary>> Summarize();
}

public sealed record LoanSummary(string Id, string PatronId, decimal Amount);

What the validator handles

Construct	Status	Notes
PG payload->'name' (JSON arrow → JSONB)	✅ Validated	The column ref is checked
PG payload @> '{...}' (containment)	✅ Validated
PG payload @? '$.path' (jsonb_path_exists)	✅ Validated
PG jsonb_extract_path_text(payload, 'name')	✅ Validated	Recommended over ->> (below)
PG payload->>'name' (JSON arrow → text, with string literal)	✅ Validated	SqlParserCS 0.6.x's tokenizer trips on this — the validator preprocesses Postgres SQL to rewrite col->>'literal' to jsonb_extract_path_text(col, 'literal') before parsing. Same semantics, column ref still checked. Runtime executes the original SQL unchanged.
SQLite json_extract(payload, '$.path')	✅ Validated	The default json1 extension ships with modern SQLite
MySQL JSON_EXTRACT(payload, '$.path')	✅ Validated
MySQL payload->'$.path' (JSON arrow)	✅ Validated
SQL Server JSON_VALUE(payload, '$.path')	✅ Validated

Column refs inside any of these (e.g. JSON_VALUE(typo_col, '$.x')) are caught — the validator walks into function args.

The RelationalDocumentStoreProjection base class (under Nagare.Subscriptions.Projections) is the canonical way to write the JSON column from events. The query side composes naturally — they don't need to know about each other. The decision of when to reach for JSON vs columnar lives up in Choosing your approach.

Scaling JSON projections — indexing strategy

Without an index, every WHERE payload->>'status' = ... (PG) or WHERE JSON_EXTRACT(payload, '$.status') = ... (MySQL) scans the whole table. The validator catches column typos; it doesn't catch missing indexes. Per-dialect recipes:

PostgreSQL — three index choices, pick by query shape:

Query	Index	Why
WHERE payload->>'status' = 'active' (one hot path)	CREATE INDEX … ON t ((payload->>'status')) — B-tree on the extracted text	Smallest, supports range/ORDER BY, exact match on hot key
WHERE payload @> '{"status":"active"}' (containment, varied keys)	CREATE INDEX … ON t USING gin (payload jsonb_path_ops)	Smaller GIN, containment-only, faster than default jsonb_ops
WHERE payload ? 'tag' OR payload @> {...} (any-key, multiple operators)	CREATE INDEX … ON t USING gin (payload) — default jsonb_ops	Bigger index, more operator coverage

GIN has noticeable write overhead — match the operator class to the queries you actually run. Pair with partial indexes when only a fraction of rows match (e.g. WHERE archived_at IS NULL).

MySQL — generated column + B-tree:

JSON columns can't be indexed directly. Pull the hot path into a VIRTUAL generated column and index that:

ALTER TABLE book_documents
    ADD COLUMN status_v VARCHAR(64) AS
    (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.status'))) VIRTUAL;
CREATE INDEX idx_book_status ON book_documents (status_v);

The optimizer uses the index only when the WHERE clause references the generated column, not the raw JSON path. So queries become WHERE status_v = 'active', not WHERE JSON_EXTRACT(payload, '$.status') = .... The [ReadModel] declaration includes the generated column the same as any other; the validator checks it like a normal column. For arrays, look at MySQL 8.0.17's multi-valued indexes (CAST(... AS UNSIGNED ARRAY)).

SQLite — expression index via json1:

CREATE INDEX idx_book_author ON book_documents (json_extract(payload, '$.author'));

Works natively, no virtual column dance. The Library sample's BookDocumentProjection demonstrates this.

Verify with EXPLAIN — every dialect's plan should show Index Scan using idx_… (PG) / using index_name (MySQL) / SEARCH not SCAN (SQLite). The dashboard's EXPLAIN button surfaces the plan with one click; if it shows a seq scan on your hot query, you're missing the index.

Multi-tenant filter

Mark a read model as tenant-scoped and the runtime auto-appends AND tenant_id = @tenant_id (or your configured column) to every Tier 1 query — pulled from ITenantContext.TenantId at execution time.

// 1. Register a tenant context (per-request, scoped).
builder.Services.AddScoped<ITenantContext, HttpTenantContext>();

// 2. Configure the read model with the filter.
builder.Services.AddReadQueries<LoanRow>(
    owningProjection: LoanStatusProjection.Identifier.Value,
    tenantFilter: new TenantFilter(Column: "tenant_id"));

// 3. Queries auto-scope. No code change at the call site.
public Task<IReadOnlyList<LoanRow>> Active() =>
    _store.Where($"status = {"Active"}").OrderBy("id").ToList();
// Rendered: SELECT … WHERE status = @p0 AND tenant_id = @tenant_id ORDER BY "id"

When ITenantContext.TenantId is null:

• Default — the read throws with a clear error. A missing tenant for a scoped read is almost always a bug.
• Override — TenantFilter(ThrowWhenTenantMissing: false) allows unscoped reads (system jobs, replay workloads, public paths).

Tier 2 [ReadQuery] bodies are not auto-filtered — the SQL is opaque text. Include the predicate explicitly:

[ReadQuery("""
    SELECT id, patron_id, book_id, status
    FROM loan_status
    WHERE status = @status AND tenant_id = @tenant
    """, Dialect = SqlDialect.Sqlite)]
public partial Task<IReadOnlyList<LoanRow>> ByStatus(string status, string tenant);

How the auto-stamp connects

Registering ITenantContext in DI also wires the command side: Aggregate.Ask reads from the context and auto-stamps EventMetadata.TenantId on every emitted event, the same way CommandSource and CommandType already auto-populate. You don't pass tenant through every command handler — it rides on the metadata once you set the context at the request boundary (HTTP middleware, message-pump entry, scheduler).

// Request boundary (HTTP middleware or similar):
public sealed class HttpTenantContext(IHttpContextAccessor http) : ITenantContext
{
    public string? TenantId => http.HttpContext?.Items["tenant_id"] as string;
}

builder.Services.AddHttpContextAccessor();
builder.Services.AddScoped<ITenantContext, HttpTenantContext>();

// Command handler — no tenant code needed:
await repo.Ask(loanId, new RequestLoan(patron, book));
// → event.Metadata.TenantId is auto-stamped from HttpTenantContext.

// Projection — implement ITenanted on your state, framework auto-copies the value:
public sealed class LoanState : IJsonable, IProjectionState, ITenanted
{
    public string? TenantId { get; set; }   // framework writes this from envelope.Metadata
    // ... your other fields
}

// Read store — auto-scoped (above).

The ITenanted interface lives in Nagare.Store. When RelationalDocumentStoreProjection upserts a state that implements it, the framework copies envelope.Metadata.TenantId into the state before persisting. Explicit values set in your Apply handler are preserved — the auto-fill never overwrites a non-null tenant.

For columnar projections that don't inherit RelationalDocumentStoreProjection, copy envelope.Metadata?.TenantId into your INSERT manually and mark the class [TenantScoped] — the NAG1003 analyzer scans every INSERT/UPDATE in the class and warns if the tenant column is missing.

Transaction-aware reads

store.WithTransaction(tx) returns a view bound to an open DbTransaction. Queries on the view run on the transaction's connection with cmd.Transaction = tx set — so they see uncommitted writes from the same workflow. The connection is borrowed; disposing the view doesn't close the caller's transaction.

await using var conn = factory();
await conn.OpenAsync(ct);
await using var tx = await conn.BeginTransactionAsync(ct);

await conn.ExecuteAsync(
    "INSERT INTO loan_status (id, ...) VALUES (...)", new { ... }, tx);

var txStore = _store.WithTransaction(tx);
var loan = await txStore.Where($"id = {newId}").Single();   // sees the insert

await tx.CommitAsync(ct);

Tenant scoping is preserved through WithTransaction automatically. Use this for read-your-own-write inside a single workflow. Cross-request consistency after a command is a different problem — it belongs in command-dispatch / projection-checkpoint layers, not here. See eventual consistency UX patterns.

Read-replica routing

By default the read queries reuses the same DbConnection factory the write side uses. To route reads at a separate host (Azure SQL ApplicationIntent=ReadOnly, a Postgres hot standby, or a dedicated replica entirely), pass a connection factory:

builder.Services.AddReadQueries<LoanRow>(
    connectionFactory: sp =>
    {
        var cfg = sp.GetRequiredService<IConfiguration>();
        return new NpgsqlConnection(cfg.GetConnectionString("ReadReplica"));
    },
    owningProjection: LoanStatusProjection.Identifier.Value);

Each query opens a fresh connection from the factory — keep it cheap, ideally with a pool behind it. The write side keeps using whatever DbConnection is registered via AddNagare{Provider}Storage. There's no read-after-write guarantee against a replica with replication lag, so don't route queries you issue immediately after a command — those should still hit the primary.

Dialect support

Provider	Identifier quoting	Pagination
AddPostgresReadQueries	"name" (ANSI)	LIMIT n OFFSET m
AddSqliteReadQueries	"name" (ANSI)	LIMIT n OFFSET m
AddMySqlReadQueries	`name`	LIMIT n OFFSET m
AddSqlServerReadQueries	[name]	OFFSET n ROWS FETCH NEXT m ROWS ONLY (implicit ORDER BY (SELECT NULL) when no order specified)

All four implement IQueryDialect. Override per-deployment by registering your own before the provider extension; the registration uses TryAddSingleton.

Dashboard

The dashboard auto-discovers every [ReadModel] registered via AddReadQueries<T>():

• Read Models tab — list of registered models, owning projection, column count
• Schema view — per model, columns with CLR types and nullable flags
• Query log — last 100 dispatches per model: SQL, parameters, duration, row count
• EXPLAIN button — re-runs any logged query as EXPLAIN (or provider equivalent — SET SHOWPLAN_ALL on SQL Server)

Endpoints (gated by INagareDashboardAuthorizationFilter with Browse):

GET  /_nagare/api/read-models
GET  /_nagare/api/read-models/{name}
GET  /_nagare/api/read-models/{name}/queries?limit=50
POST /_nagare/api/read-models/{name}/queries/{id}/explain

See also

• samples/Nagare.Samples.Library — LoanStatusProjection exercises inline + multi-line + custom projection + streaming + pagination; BookDocumentProjection shows the JSON document path with json_extract queries and an expression index in Prepare().
• Projections & Subscriptions — the write side.
• Dashboard — the operational surface that consumes the read-queries registry.