CQRS Read Models, Raw SQL/Dapper Interop, Bulk Workloads, and Choosing the Right Data-Access Tool per Path

Learning outcomes

1. CQRS starts with responsibility separation, not microservices

Command Query Responsibility Segregation (CQRS) separates operations that change state from operations that return data. ServiceHub can apply CQRS inside one process and one database: aggregate writes use EF tracking/concurrency/invariants, while read endpoints return purpose-built DTOs. Separate databases, event sourcing and asynchronous projections are optional later choices, not prerequisites.

2. EF Core is often already enough for the read side

public sealed record WorkOrderQueueRow(    int Id, string Number, string Summary, int Priority, string City);var rows = await db.WorkOrders    .AsNoTracking()    .Where(w => w.Priority >= 3)    .OrderByDescending(w => w.Priority)    .ThenBy(w => w.OpenedUtc)    .Take(100)    .Select(w => new WorkOrderQueueRow(        w.Id, w.WorkOrderNumber, w.Summary,        w.Priority, w.ServiceAddress.City))    .ToListAsync(ct);

This avoids materializing full tracked aggregates and keeps tenant filters. Chapter 17’s measurement rules still apply: inspect columns, rows, bytes, plans and end-to-end latency before introducing another tool.

3. Raw SQL/keyless types are an EF escape hatch before a second ORM

var tenant = db.TenantId;var rows = await db.Database.SqlQuery<WorkOrderQueueRow>($"""    SELECT work_order_id AS Id,           work_order_number AS Number,           summary AS Summary,           priority AS Priority,           service_city AS City    FROM work_orders    WHERE tenant_id = {tenant}      AND is_deleted = 0    ORDER BY priority DESC, opened_utc    LIMIT 100    """).ToListAsync(ct);

Raw SQL is not automatically faster. It trades LINQ translation for hand-authored SQL and provider coupling. Keep parameterization, tenant predicates, bounded results and tests.

4. Optional Dapper 2.1.79: use it where direct SQL mapping is measurably valuable

dotnet add package Dapper --version 2.1.79

var connection = db.Database.GetDbConnection();await db.Database.OpenConnectionAsync(ct);const string sql = """SELECT work_order_id AS Id, work_order_number AS Number,       summary AS Summary, priority AS Priority, service_city AS CityFROM work_ordersWHERE tenant_id = @tenant AND is_deleted = 0ORDER BY priority DESC, opened_utcLIMIT @take;""";var rows = (await connection.QueryAsync<WorkOrderQueueRow>(    new CommandDefinition(sql,        new { tenant = db.TenantId, take = 100 },        cancellationToken: ct))).AsList();

Dapper is a micro-ORM over ADO.NET. It does not provide EF tracking, model metadata, query filters or migrations. The SQL is your responsibility. Prefer it only if the read path benefits from its direct mapping/SQL control enough to justify a second data-access dependency.

5. Mixed writes must share the same transaction deliberately

await using var tx = await db.Database.BeginTransactionAsync(ct);order.ReviseSummary(summary, clock);await db.SaveChangesAsync(ct);var connection = db.Database.GetDbConnection();await connection.ExecuteAsync(new CommandDefinition(    "UPDATE reporting_watermark SET last_work_order_id = @id",    new { id = order.Id },    transaction: tx.GetDbTransaction(),    cancellationToken: ct));await tx.CommitAsync(ct);

If Dapper opens a different connection or runs without the EF transaction, the operations are not atomic. Cross-tool coordination is a relational connection/transaction problem, not a CQRS slogan.

6. Bulk workloads: choose semantics before throughput

var affected = await db.WorkOrders    .Where(w => w.TenantId == db.TenantId && !w.IsActive)    .ExecuteUpdateAsync(s => s        .SetProperty(w => w.IsDeleted, true), ct);

Because ExecuteUpdateAsync bypasses tracked aggregate methods, it also bypasses per-entity domain-event creation and manual Revision advancement unless you set those columns explicitly. Use it for operations whose semantics are truly set-based.

7. Deliberate failure: “Dapper is faster, so use it everywhere”

A blanket rewrite can remove tenant filters, concurrency predicates, relationship fixup, migrations and transaction consistency while duplicating mapping. The opposite dogma—“never use SQL outside EF”—can make specialized reports or bulk operations harder than necessary. Measure the path and document the ownership boundary.

8. Mandatory lab: one write path, three read paths, one evidence table

1. Write a WorkOrder change through the aggregate + EF and verify the Revision predicate.
2. Implement the same bounded queue read with EF projection and SqlQuery.
3. Optionally install Dapper 2.1.79 and implement the same read using the same SQLite connection.
4. Compare returned rows, SQL, parameterization, allocations/timing in a controlled local benchmark; do not invent universal winners.
5. Run a mixed EF+Dapper transaction and force the Dapper statement to fail; prove EF changes roll back.
6. Document which path owns tenant filtering and why.

9. Production judgment and bridge

Use EF for aggregate persistence and ordinary projections when it meets the requirements. Add raw SQL, Dapper, stored procedures or native bulk paths only where their explicit SQL/control is worth the added provider/testing/operational surface. The next lesson turns these choices into a deployment and incident model.

Authoritative references

• CQRS pattern — command/query separation, benefits and tradeoffs
• CQRS read implementation — Microsoft example of independent read DTOs and Dapper
• SQL queries in EF Core — FromSql/SqlQuery parameterized escape hatches
• ExecuteUpdate and ExecuteDelete — set-based DML semantics and tracker boundaries
• Transactions in EF Core — sharing connections/transactions across data-access APIs
• Dapper 2.1.79 — current optional micro-ORM package and license/version information