CQRS in a Single Database: Aggregates on the Write Side, Projections on the Read Side

Version note: The code in this post was built and run with C# 14, .NET 10, Entity Framework Core (EF Core) 10, Npgsql 10, Dapper 2.1 and PostgreSQL 17.

Guess First

In the previous post we defined CQRS (Command Query Responsibility Segregation) as "two objects instead of one" and drew its three levels. A teammate tells you they have implemented level 1: the "My Orders" screen now has its own query handler. Here it is:

using Bookstore.Infrastructure;
using Bookstore.Ordering;
using Bookstore.Ordering.Queries;
using Microsoft.EntityFrameworkCore;

namespace Bookstore.Before;

public sealed class GetCustomerOrdersHandler(OrderingDbContext db)
{
    public async Task<IReadOnlyList<CustomerOrderRow>> HandleAsync(GetCustomerOrders query, CancellationToken ct)
    {
        CustomerId customerId = new(query.CustomerId);
        List<Order> orders = await db.Orders
            .Where(o => o.CustomerId == customerId)
            .OrderByDescending(o => o.PlacedAt)
            .ToListAsync(ct);

        // The marketing team asked for "last seen"
        await db.Database.ExecuteSqlAsync($"""
            INSERT INTO ordering.customer_activity (customer_id, last_seen_at) VALUES ({query.CustomerId}, now())
            ON CONFLICT (customer_id) DO UPDATE SET last_seen_at = excluded.last_seen_at
            """, ct);

        return [.. orders
            .Skip((query.Page - 1) * query.PageSize)
            .Take(query.PageSize)
            .Select(o => new CustomerOrderRow(
                o.Id.Value, o.PlacedAt, o.Status.ToString(),
                o.Items.Sum(i => i.Quantity),
                o.Items.Sum(i => i.UnitPrice * i.Quantity),
                o.Items[0].Title))];
    }
}

This class has "query" in its name but breaks both of the basic promises of CQRS. Which two promises? How many rows does the SQL (Structured Query Language) this handler runs ask the database for, to show Ada's 20 orders? And what happens to the "last seen" record when a cache or a crawler repeats the same GET request?

The Promises of Level 1

At level 1 there is one database and the tables are the same; what gets separated is the write path and the read path. That separation makes two promises. First: a query does not change state. Second: a query does not use the write model (the aggregate and its change tracker); it produces the shape the screen needs directly from the data. The handler above breaks both. Let's look at the SQL EF Core generates:

-- @ada='0195f0a0-0000-7000-9000-00000000000a'
SELECT o.id, o.customer_id, o.placed_at, o.status, o.version, o0.id, o0.book_id, o0.order_id, o0.quantity, o0.title, o0.unit_price
FROM ordering.orders AS o
LEFT JOIN ordering.order_items AS o0 ON o.id = o0.order_id
WHERE o.customer_id = @ada
ORDER BY o.placed_at DESC, o.id

There is no LIMIT: all of the customer's orders come back joined with all of their lines, and paging happens in memory. Because the order lines are mapped as owned entities, EF Core includes them automatically in every query; the aggregate's consistency boundary means "bring everything" on the read side too. On top of that there is an INSERT: a GET request writes to the database. In this post we will build the same screen properly, and instead of leaving the second promise to code review alone, we will have the database enforce it. The examples use Bookstore's Ordering context on PostgreSQL; since we will also use PostgreSQL as the event store later in the series, we fix the engine from the start.

The Write Side: Command, Aggregate, and SaveChanges

The write side follows the layout you know from the DDD (Domain-Driven Design) series: the command is a record, and the handler creates or loads the aggregate, calls its behavior, and saves it.

using Bookstore.Infrastructure;
using Bookstore.Ordering;
using Microsoft.EntityFrameworkCore;

namespace Bookstore.Ordering.Commands;

public sealed record PlaceOrderLine(Guid BookId, int Quantity);
public sealed record PlaceOrder(Guid CustomerId, IReadOnlyList<PlaceOrderLine> Lines);

public sealed class PlaceOrderHandler(OrderingDbContext db, TimeProvider clock)
{
    // Returns the new order's id, not screen data
    public async Task<OrderId> HandleAsync(PlaceOrder command, CancellationToken ct)
    {
        Guid[] bookIds = [.. command.Lines.Select(l => l.BookId).Distinct()];
        Dictionary<Guid, Book> books = await db.Books.AsNoTracking()
            .Where(b => bookIds.Contains(b.Id))
            .ToDictionaryAsync(b => b.Id, ct);

        CartLine[] lines = [.. command.Lines.Select(l => books.TryGetValue(l.BookId, out Book? book)
            ? new CartLine(new BookId(book.Id), book.Title, book.ListPrice, l.Quantity)
            : throw new DomainException("order.unknown-book", $"Book {l.BookId} is not in the catalog."))];

        Order order = Order.Place(new CustomerId(command.CustomerId), lines, clock);
        db.Orders.Add(order);
        await db.SaveChangesAsync(ct);
        return order.Id;
    }
}

public sealed record CancelOrder(OrderId OrderId, CancellationReason Reason);

public sealed class CancelOrderHandler(OrderingDbContext db)
{
    // false: no such order; rule violations bubble up as DomainException
    public async Task<bool> HandleAsync(CancelOrder command, CancellationToken ct)
    {
        Order? order = await db.Orders.SingleOrDefaultAsync(o => o.Id == command.OrderId, ct);
        if (order is null)
            return false;

        order.Cancel(command.Reason);
        await db.SaveChangesAsync(ct);
        return true;
    }
}

PlaceOrderHandler reads book titles and prices from the Catalog table. Is that a query? No; reading the data needed to make a decision is part of the command. CQRS does not say a command handler never reads, it says it does not return the read model. The book title and unit price are written to the order as they are at that moment, so a later catalog price change does not alter an old order's total.

On the EF Core side the aggregate's mapping is the same as in the DDD series post "Persisting Aggregates with EF Core 10: Backing Fields, Owned/Complex Types, and Concurrency": value converters for strongly typed ids, order lines in a separate table via OwnsMany, and Version as a concurrency token. If two requests try to cancel the same order at the same time, the second one gets a DbUpdateConcurrencyException; the details are in the EF Core series post "Concurrency in EF Core: Tokens, rowversion and DbUpdateConcurrencyException".

// Write side: the DbContext that knows the aggregate and the change tracker
public sealed class OrderingDbContext(DbContextOptions<OrderingDbContext> options) : DbContext(options)
{
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<Book> Books => Set<Book>();

    protected override void OnModelCreating(ModelBuilder model)
    {
        model.Entity<Book>().ToTable("books", "catalog");

        model.Entity<Order>(order =>
        {
            order.ToTable("orders", "ordering");
            order.Property(o => o.Id).HasConversion(id => id.Value, value => new OrderId(value));
            order.Property(o => o.CustomerId).HasConversion(id => id.Value, value => new CustomerId(value));
            order.Property(o => o.Status).HasConversion<string>().HasMaxLength(20);
            order.Property(o => o.Version).IsConcurrencyToken();
            order.HasIndex(o => new { o.CustomerId, o.PlacedAt });
            order.OwnsMany(o => o.Items, item =>
            {
                item.ToTable("order_items", "ordering");
                item.WithOwner().HasForeignKey("OrderId");
                item.Property<int>("Id");
                item.HasKey("Id");
                item.Property(i => i.BookId).HasConversion(id => id.Value, value => new BookId(value));
                item.Property(i => i.Title).HasMaxLength(200);
                item.Property(i => i.UnitPrice).HasPrecision(10, 2);
            });
            order.Navigation(o => o.Items).HasField("_items");
        });
    }
}

What Should a Command Return?

Under Meyer's strict CQS (Command-Query Separation) a command returns nothing. In practice there are three reasonable answers. Void: commands like cancellation, which change a resource the client already knows. The new id: commands that create something, because the client has to know the resource's address. A result: a small value saying whether the operation hit a business rule (a result code or the new version number). What it should not return is a screen DTO (Data Transfer Object): "the latest state of the order" is a read concern, and the day you start returning it from a command, the shape of the read model leaks into the write side.

On the HTTP (Hypertext Transfer Protocol) side these three answers map onto familiar status codes. Commands are POST, queries are GET:

using Bookstore.Ordering;
using Bookstore.Ordering.Commands;
using Bookstore.Ordering.Queries;
using Microsoft.AspNetCore.Diagnostics;
using Microsoft.EntityFrameworkCore;

namespace Bookstore.Web;

public static class OrderingEndpoints
{
    public static void MapOrdering(this IEndpointRouteBuilder app)
    {
        // Commands: POST, returning only an id or a status code
        app.MapPost("/orders", async (PlaceOrder command, PlaceOrderHandler handler, CancellationToken ct) =>
        {
            OrderId id = await handler.HandleAsync(command, ct);
            return Results.Created($"/orders/{id.Value}", new { orderId = id.Value });
        });

        app.MapPost("/orders/{orderId:guid}/cancel", async (Guid orderId, CancelOrderHandler handler, CancellationToken ct) =>
            await handler.HandleAsync(new CancelOrder(new OrderId(orderId), CancellationReason.CustomerRequest), ct)
                ? Results.NoContent()
                : Results.NotFound());

        // Queries: GET, never touch state
        app.MapGet("/customers/{customerId:guid}/orders", (Guid customerId, int? page, GetCustomerOrdersHandler handler, CancellationToken ct) =>
            handler.HandleAsync(new GetCustomerOrders(customerId, page ?? 1, 20), ct));

        app.MapGet("/reports/daily-sales", (DateOnly from, DateOnly to, GetDailySalesHandler handler, CancellationToken ct) =>
            handler.HandleAsync(new GetDailySales(from, to), ct));
    }
}

public sealed class DomainExceptionHandler(IProblemDetailsService problems) : IExceptionHandler
{
    public async ValueTask<bool> TryHandleAsync(HttpContext context, Exception exception, CancellationToken ct)
    {
        (int status, string code)? mapped = exception switch
        {
            DomainException domain => (StatusCodes.Status409Conflict, domain.Code),
            DbUpdateConcurrencyException => (StatusCodes.Status409Conflict, "order.concurrency"),
            _ => null
        };
        if (mapped is not { } m)
            return false;

        context.Response.StatusCode = m.status;
        return await problems.TryWriteAsync(new ProblemDetailsContext
        {
            HttpContext = context,
            ProblemDetails = { Status = m.status, Title = exception.Message, Type = m.code }
        });
    }
}

A rule violation bubbles up as a DomainException, and ASP.NET Core's IExceptionHandler, added in .NET 8, turns it into a 409 in the problem details format of RFC (Request for Comments) 9457. Status codes and ProblemDetails are covered in detail in the ASP.NET Core Web API (Application Programming Interface) series post "Status Codes and Responses in ASP.NET Core: Ok, CreatedAtAction and ProblemDetails". A real run:

POST /orders -> 201, Location: /orders/01a1b83a-3456-78e1-9e26-3f9b88b1b76c
POST cancel -> 204
POST cancel again -> 409 {"type":"order.cancelled","title":"Order is already cancelled.","status":409,"traceId":"0HNPT49CK0LT5:00000008"}

The order id is a Guid v7, so it sorts by time; its first 48 bits are the creation timestamp in milliseconds. If the client wants to refresh its screen, it sends a GET to the address in Location or to the list.

The Read Side: A Flat EF Core Model over the Same Tables

The read side has no need for the aggregate. Value converters, backing fields, the concurrency token: none of them are needed to fill a list. So we give the read side a separate, flat DbContext that looks at the same tables:

using Microsoft.EntityFrameworkCore;

namespace Bookstore.Ordering.Queries;

// The read side's own flat model: same tables, no domain types
public sealed class OrderRow
{
    public Guid Id { get; init; }
    public Guid CustomerId { get; init; }
    public DateTimeOffset PlacedAt { get; init; }
    public string Status { get; init; } = "";
    public List<OrderItemRow> Items { get; init; } = [];
}

public sealed class OrderItemRow
{
    public int Id { get; init; }
    public Guid OrderId { get; init; }
    public string Title { get; init; } = "";
    public decimal UnitPrice { get; init; }
    public int Quantity { get; init; }
}

public sealed class OrderingReadDbContext : DbContext
{
    public OrderingReadDbContext(DbContextOptions<OrderingReadDbContext> options) : base(options) =>
        ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;

    public DbSet<OrderRow> Orders => Set<OrderRow>();

    protected override void OnModelCreating(ModelBuilder model)
    {
        model.Entity<OrderRow>().ToTable("orders", "ordering");
        model.Entity<OrderItemRow>().ToTable("order_items", "ordering");
        model.Entity<OrderRow>().HasMany(o => o.Items).WithOne().HasForeignKey(i => i.OrderId);
    }

    // Nothing is saved through the read model
    public override int SaveChanges(bool acceptAllChangesOnSuccess) =>
        throw new InvalidOperationException("OrderingReadDbContext is read-only.");

    public override Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken ct = default) =>
        throw new InvalidOperationException("OrderingReadDbContext is read-only.");
}

Two details matter. The context turns tracking off in its constructor, so every query behaves like AsNoTracking. Both SaveChanges overloads throw, so if someone accidentally adds an entity to the read context and tries to save it, it stops at run time. Migrations are generated only from the write context; the read context does not own the schema, it uses it. The cost is clear: if a column is renamed, two mappings have to be updated.

The handler is a projection that has SQL compute everything the screen needs:

using Dapper;
using Microsoft.EntityFrameworkCore;
using Npgsql;

namespace Bookstore.Ordering.Queries;

public sealed record GetCustomerOrders(Guid CustomerId, int Page, int PageSize);

public sealed record CustomerOrderRow(
    Guid OrderId, DateTimeOffset PlacedAt, string Status, int ItemCount, decimal Total, string FirstTitle);

public sealed class GetCustomerOrdersHandler(OrderingReadDbContext db)
{
    public async Task<IReadOnlyList<CustomerOrderRow>> HandleAsync(GetCustomerOrders query, CancellationToken ct) =>
        await Build(query).ToListAsync(ct);

    // The columns the screen needs are computed in SQL; no entity is materialized
    public IQueryable<CustomerOrderRow> Build(GetCustomerOrders query) =>
        db.Orders
            .Where(o => o.CustomerId == query.CustomerId)
            .OrderByDescending(o => o.PlacedAt)
            .Skip((query.Page - 1) * query.PageSize)
            .Take(query.PageSize)
            .Select(o => new CustomerOrderRow(
                o.Id, o.PlacedAt, o.Status,
                o.Items.Sum(i => i.Quantity),
                o.Items.Sum(i => i.UnitPrice * i.Quantity),
                o.Items.OrderBy(i => i.Id).Select(i => i.Title).First()));
}

-- @query_CustomerId='0195f0a0-0000-7000-9000-00000000000a'
-- @p2='20'
-- @p='0'
SELECT o.id, o.placed_at, o.status, (
    SELECT COALESCE(sum(o0.quantity), 0)::int
    FROM ordering.order_items AS o0
    WHERE o.id = o0.order_id), (
    SELECT COALESCE(sum(o1.unit_price * o1.quantity::numeric), 0.0)
    FROM ordering.order_items AS o1
    WHERE o.id = o1.order_id), (
    SELECT o2.title
    FROM ordering.order_items AS o2
    WHERE o.id = o2.order_id
    ORDER BY o2.id
    LIMIT 1)
FROM ordering.orders AS o
WHERE o.customer_id = @query_CustomerId
ORDER BY o.placed_at DESC
LIMIT @p2 OFFSET @p

Paging now happens in the database (LIMIT/OFFSET), quantity, total and first title are computed with correlated subqueries, and no entity is materialized. We critiqued this kind of SQL and covered the performance side of projections in the EF Core series posts "EF Core Performance Basics: N+1, Projection, AsNoTracking and Compiled Queries" and "EF Core and Architecture: Repositories, the Read Side of CQRS and Moving to Dapper"; what matters here is that the query is not even aware that the Order class exists. Ada's screen:

2026-11-04 16:00  Placed     qty=3   29.70 EUR  Emma
2026-11-03 14:00  Placed     qty=3   40.50 EUR  The Hobbit
2026-11-02 14:00  Cancelled  qty=1   11.20 EUR  Middlemarch
2026-11-02 09:00  Placed     qty=3   32.30 EUR  Dune

The Read Side: A SQL View and Dapper

The daily sales screen in the back office is a different read need: orders grouped by day, cancellations excluded. You could write it in LINQ (Language Integrated Query), but there is nothing wrong with what Greg Young calls the "Thin Read Layer" being tied to the database. We put the report logic in a view:

CREATE VIEW ordering.daily_sales AS
SELECT (o.placed_at AT TIME ZONE 'UTC')::date AS day,
       count(DISTINCT o.id)                   AS orders,
       sum(i.quantity)                        AS copies,
       sum(i.unit_price * i.quantity)         AS revenue
FROM ordering.orders o
JOIN ordering.order_items i ON i.order_id = o.id
WHERE o.status <> 'Cancelled'
GROUP BY 1;

Dapper reads from that view and maps the rows straight into a record:

public sealed record GetDailySales(DateOnly From, DateOnly To);

public sealed record DailySalesRow(DateOnly Day, long Orders, long Copies, decimal Revenue);

public sealed class GetDailySalesHandler(NpgsqlDataSource readSource)
{
    // The report SQL lives in a database view; Dapper only reads
    private const string Sql = """
        SELECT day, orders, copies, revenue
        FROM ordering.daily_sales
        WHERE day BETWEEN @From AND @To
        ORDER BY day
        """;

    public async Task<IReadOnlyList<DailySalesRow>> HandleAsync(GetDailySales query, CancellationToken ct)
    {
        await using NpgsqlConnection connection = await readSource.OpenConnectionAsync(ct);
        IEnumerable<DailySalesRow> rows = await connection.QueryAsync<DailySalesRow>(
            new CommandDefinition(Sql, query, cancellationToken: ct));
        return [.. rows];
    }
}

2026-11-02  orders=2  copies=4  revenue=46.30
2026-11-03  orders=2  copies=5  revenue=61.60
2026-11-04  orders=1  copies=3  revenue=29.70

Because Ada's Middlemarch order was cancelled on 2 November, that day shows two orders. The view is still level 1: it is recomputed from the tables on every query and nothing is stored separately. Turning it into a materialized view or a denormalized table when read cost grows means moving to level 2, which is the topic of the next post. Dapper's parameter, mapping and connection management details are in the Dapper series; here it is enough that the record's primary constructor matches the column names. Npgsql 10 also converts the date column to DateOnly without trouble.

Queries Don't Change State: Make the Database Say So

Shutting down SaveChanges is a good start, but it is not the only door. ExecuteUpdate, added in EF Core 7, sends an UPDATE directly without using the change tracker or SaveChanges at all; Dapper and raw SQL know nothing about the override either. The real guarantee comes from the database. The read side's connection turns on default_transaction_read_only when it opens the PostgreSQL session:

string writeConnection = builder.Configuration.GetConnectionString("Ordering")!;
// Read side: same database, but read-only transactions at the session level
NpgsqlDataSource readSource = new NpgsqlDataSourceBuilder(
    writeConnection + ";Options=-c default_transaction_read_only=on").Build();

Npgsql's Options connection string parameter passes these -c settings to the server at session start. Now let's try writing from the read side in three different ways:

        // ExecuteUpdate never calls SaveChanges; the override cannot stop it
        try
        {
            await readDb.Orders.Where(o => o.CustomerId == Setup.Ada)
                .ExecuteUpdateAsync(s => s.SetProperty(o => o.Status, "Paid"));
        }
        catch (PostgresException ex)
        {
            Console.WriteLine($"ExecuteUpdate: {ex.SqlState} {ex.MessageText}");
        }

        // A handler that tries to record last-seen time inside the "My Orders" query
        try
        {
            await using NpgsqlConnection connection = await readSource.OpenConnectionAsync();
            await connection.ExecuteAsync("""
                INSERT INTO ordering.customer_activity (customer_id, last_seen_at) VALUES (@Ada, now())
                ON CONFLICT (customer_id) DO UPDATE SET last_seen_at = excluded.last_seen_at
                """, new { Setup.Ada });
        }
        catch (PostgresException ex)
        {
            Console.WriteLine($"Dapper: {ex.SqlState} {ex.MessageText}");
        }

SaveChanges: OrderingReadDbContext is read-only.
ExecuteUpdate: 25006 cannot execute UPDATE in a read-only transaction
Dapper: 25006 cannot execute INSERT in a read-only transaction

25006 is PostgreSQL's read_only_sql_transaction error code. The "last seen" record from the opening would have failed the same way had it gone through the read connection. This setting is a guardrail, not a security boundary: code can get around it with SET TRANSACTION READ WRITE. If you want a real boundary, give the read side a separate database role with only SELECT permission; and when you later move the read load to a read replica, the read connection already points at the right place. If a need like "last seen" is real, it is a separate command: the client sends POST /customers/{id}/activity when the screen opens, or the information is derived from request logs.

Common Pitfalls

Repositories and aggregates on the read side

This is the real mistake of the opening handler. A repository exists to bring back the aggregate as a whole; used on the read side, paging slides into memory, computation slides into C#, and getters get added to the aggregate for the screen. A query handler's dependencies should be a read context, an NpgsqlDataSource or a view, not an IOrderRepository.

Side effects in query handlers

"Last seen", "view count", "mark as read"... all innocent-looking writes. Every layer that assumes GET is safe (HTTP caches, browser prefetching, a retrying client) repeats that write an unexpected number of times. A write is a command, with its own endpoint and its own handler.

The same DTO for reads and writes

Using CustomerOrderRow as a command's input is tempting: the fields are already there. After a while an order update request appears in which the client sends the Status field, and CRUD (Create, Read, Update, Delete) is back. Commands describe intent (CancelOrder), query results describe screens; they don't share a record.

Mistaking a mediator pipeline for CQRS

Sending commands and queries through the same IRequest<T> interface and the same pipeline behaviors (transaction, validation) reduces the separation to a naming convention. A behavior that wraps queries in a transaction also breaks a handler that works with a read-only connection. Make the separation with types and dependencies: the write handler takes OrderingDbContext, the read handler takes the read context or NpgsqlDataSource.

When Not to Use It

Level 1 is cheap but not free: a second DbContext, a second mapping, and both sides tied to the same schema. If what the screen shows is the same as what the aggregate holds (a category list in Catalog, with its name and order), one DbContext and an AsNoTracking query are enough; a separate read context is a needless layer. Split off the read context when read screens multiply and getters start appearing on the aggregate for screens. A read-only connection, on the other hand, is almost always a cheap win; it is one connection string difference.

A Small Experiment

★☆☆ Remove Options=-c default_transaction_read_only=on from the demo's read connection string and run the program again. Which two lines change, and what do you see in the customer_activity table?

★★☆ Write a GetOrderDetails(Guid OrderId) query: the order header and all lines in a single round trip. Write it first with the read context using Include, then with Dapper's QueryMultipleAsync, and compare the SQL the two produce.

★★★ Create a separate PostgreSQL role for the read side (CREATE ROLE bookstore_reader LOGIN, USAGE on the schemas, only SELECT on the tables and the view). Open the read connection with that role and try to get around the guardrail with SET TRANSACTION READ WRITE. Which error code do you get this time?

Our list screen now runs three correlated subqueries on every request. If that computation gets expensive on a screen opened hundreds of times a minute during a campaign, it has to move from read time to write time. That is exactly what we will do in the next post: a separate read model table, a projector that feeds it from domain events, the difference between updating it in the same transaction and asynchronously, and living with the read-your-own-writes problem.