Lesson 13 of 19Professional22 min

CQRS

By the end of this lesson

Separate reads from writes, and recognise when this is overkill.

CQRS stands for Command Query Responsibility Segregation. Behind the name is one decision: the code that changes state and the code that answers questions are allowed to be different shapes.

It grew out of a weaker and much cheaper idea. Command Query Separation says a method either changes something or returns something, never both. That is a habit, it costs nothing, and it is worth keeping everywhere. CQRS goes further. The model you write through and the model you read through become separate models, with their own types, their own queries, and in the strongest version their own storage. That is not a habit. It is an architecture, and it sends you a bill every month.

One model asked to do both jobs

public sealed class Order
{
    public int Id { get; private set; }
    public int CustomerId { get; private set; }
    public OrderStatus Status { get; private set; }
    public DateTime PlacedUtc { get; private set; }
    public List<OrderLine> Lines { get; private set; } = [];
    public List<Invoice> Invoices { get; private set; } = [];

    public void AddLine(int productId, int quantity, decimal unitPrice) { /* rules */ }
    public void Approve(string approvedBy, DateTime utcNow) { /* rules */ }
}

// The finance team's open-orders screen: six columns, four tables
public async Task<List<OrderRow>> GetOpenOrdersAsync(CancellationToken token) =>
    await _db.Orders
        .Include(o => o.Lines)
        .Include(o => o.Invoices)
        .Include(o => o.Customer)
        .Where(o => o.Status == OrderStatus.Open)
        .OrderByDescending(o => o.PlacedUtc)
        .Select(o => new OrderRow(
            o.Id,
            o.Customer.Name,
            o.Lines.Count,
            o.Lines.Sum(l => l.UnitPrice * l.Quantity),
            o.Invoices.Any(i => i.PaidUtc != null),
            o.PlacedUtc))
        .ToListAsync(token);

The entity is shaped for writing. Private setters, methods that hold the rules, collections it needs in order to enforce them. That shape is what stops an order being approved twice.
The screen wants six values and no behaviour at all. It has no use for an invariant; it needs a flat row and a sort order.
The three Include calls do nothing in this query. Once a query ends in a Select that returns something other than the entity, EF Core builds its SQL from the projection and ignores the Includes. They are a common leftover, and they mislead the next reader into thinking the query is heavier than it is.
The friction is not this query. It is the next three. Each new screen adds a shape, and every shape is expressed through a model built for a different job, so the entity slowly grows navigation properties that exist only so a report can reach them.
Nothing here is broken, and that is worth saying plainly before the rest of the lesson. This code works. For a great many applications it should be left exactly as it is.

Two models, one database

// Write side — a command can be refused, and the entity holds the rules
public sealed record ApproveOrder(int OrderId, string ApprovedBy);

public sealed class ApproveOrderHandler(IOrderRepository orders, TimeProvider clock)
{
    public async Task HandleAsync(ApproveOrder command, CancellationToken token)
    {
        Order order = await orders.FindAsync(command.OrderId, token)
            ?? throw new OrderNotFoundException(command.OrderId);

        order.Approve(command.ApprovedBy, clock.GetUtcNow().UtcDateTime);
        await orders.SaveChangesAsync(token);
    }
}

// Read side — its own type, its own query, no entity anywhere
public sealed record OpenOrderRow(
    int OrderId,
    string CustomerName,
    int LineCount,
    decimal Total,
    bool Invoiced,
    DateTime PlacedUtc);

public sealed class OpenOrdersQuery(IDbConnection connection)
{
    private const string Sql = """
        SELECT o.Id AS OrderId,
               c.Name AS CustomerName,
               COUNT(l.Id) AS LineCount,
               SUM(l.UnitPrice * l.Quantity) AS Total,
               CAST(CASE WHEN EXISTS (SELECT 1 FROM Invoices i WHERE i.OrderId = o.Id)
                         THEN 1 ELSE 0 END AS bit) AS Invoiced,
               o.PlacedUtc
        FROM Orders o
        JOIN Customers c ON c.Id = o.CustomerId
        JOIN OrderLines l ON l.OrderId = o.Id
        WHERE o.Status = @status
        GROUP BY o.Id, c.Name, o.PlacedUtc
        ORDER BY o.PlacedUtc DESC
        """;

    public Task<IEnumerable<OpenOrderRow>> RunAsync(CancellationToken token) =>
        connection.QueryAsync<OpenOrderRow>(new CommandDefinition(
            Sql,
            new { status = (int)OrderStatus.Open },
            cancellationToken: token));
}

The command is written in the imperative because it can be refused. Approving a cancelled order throws. Compare that with the facts of the observer lesson, which had already happened and could not be argued with.
The read side never touches Order. It has a row type matching one screen and a query that returns exactly those columns, in one round trip.
Hand-written SQL on the read side is a choice rather than a lapse. A read model is a projection for a particular screen, SQL states that precisely, and the named parameter keeps it safe from injection. The write side keeps the ORM, where change tracking and unit of work earn their keep.
Both sides still use one database and one set of tables. Nothing here is eventually consistent: the row this query returns is as current as the transaction that committed a millisecond ago.
The cost so far is two folders and one extra type per screen. No read model can disagree with the write model, because there is still only one copy of the data. This is the version of CQRS most teams should stop at.

CQRS is not one thing. These are four separate decisions, often sold as a single package:

Separate methods (CQS): A method changes state or returns data, not both. Costs nothing, helps everywhere, and is not CQRS.
Separate models, one store: Commands go through entities; queries have their own row types and their own SQL. Costs a second type and a second query per screen. No consistency change at all, because the data is not copied.
Separate stores: Writes land in the transactional store; a projection keeps a second store in step, usually asynchronously. This is where eventual consistency becomes something your users can see. Costs projection code, rebuild tooling, lag monitoring and a second thing to operate.
Event sourcing: Storing the sequence of events as the source of truth instead of current state. Frequently confused with CQRS and genuinely independent of it. You can do either one without the other, and doing both at once as your first attempt is how teams end up rewriting.

Signals that read and write shapes have genuinely diverged. Look for numbers, not impressions:

The write path touches one order at a time; the busiest read aggregates thousands of rows across four tables.
Reads outnumber writes by a ratio you have measured. "Around 200 list loads per approval" is a reason. "Reads are more common" is not.
Screens need shapes the entity does not have, so mapping code keeps growing between them.
The entity has acquired properties that exist only so a report can read them, and they are now involved in write-side rules by accident.
Reads and writes contend for the same rows. Month-end invoicing runs and the dashboard starts timing out.
The two sides need different scaling. Reporting load has doubled twice this year while order volume is flat.

One shared model against separate models over the same database — the cheap version of the split:

	One shared model	Separate read and write models
Adding a screen	Another projection through the entity	A row type and a query, touching no entity
Enforcing a write rule	Competes with reporting needs in one class	Entity answers to writes only
Cost of a list query	Whatever the ORM produces from the entity graph	Exactly the columns the screen shows
Adding a field to both sides	One edit	Two edits and a migration
Types per feature	Fewer	Roughly double
Reading a feature end to end	One file	Two folders, and you need to know the convention

Trade-off

Two models to maintain, and when not to do this

Every field on both sides becomes two edits. A new discount column means the entity, the migration, the row type and the query. That is the mild version of the cost, and it is permanent.

The expensive version arrives with separate stores. A read store updated asynchronously is stale by an amount you do not fully control. The user approves an order, the list reloads, the status still says open. They press approve again. That is not a defect in your data layer; it is the design working as specified, and it has reached the screen.

So it becomes a product decision as much as a technical one. Either the interface shows the pending state from the command's own result, or it tells the user the list can lag, or it blocks until the projection catches up — which gives back the benefit you paid for. Someone who owns the product has to choose, before the code is written.

Most applications do not need any of this. A single model with a few hand-written projections serves an orders and invoicing application with tens of thousands of records without complaint. Separate stores earn their place when read load and write load genuinely differ in shape and volume, and when the business can live with a visible lag.

Do not split when both sides are the same shape. A maintenance screen that edits the six fields it displays gains two types and a mapping, and loses the ability to see the whole feature in one place.

Do not add a read store to fix a slow query you have not yet tried to index. An index takes an afternoon. A second store is yours to keep.

Common mistake

Common mistakes

Renaming the service layer and calling it CQRS. If both sides still load the same entities through the same DbContext, you have a folder convention and none of the separation.

Commands that return a view. Returning the new identifier is practical and fine. Returning the whole updated screen makes the command a query as well, and the write side is back to serving read shapes.

Reading through the write model because the mapping already exists. It is the path of least resistance and it is where the split quietly disappears.

Believing CQRS requires a message broker, two databases and event sourcing. It requires none of them, and starting with all three is the most reliable way to abandon the effort.

Building a read store with no way to rebuild it. The first projection bug leaves the read side wrong and no tool to correct it, so somebody writes UPDATE statements against production at 11pm. Write the rebuild before you need it.

Hiding the lag. If the read store can be behind, the interface has to account for it. Silence means users invent explanations, and the explanations are worse than the truth.

Measuring nothing. Without projection lag on a dashboard, "the list is wrong" arrives as a bug report rather than an alert.

Summary

Command query separation is a free habit; CQRS is an architecture with an ongoing cost
Separate models over one database buys shape and query control with no consistency change
Separate stores make eventual consistency visible to users, which is a product decision as much as a technical one
Justify the split with measured divergence in read and write shape and volume
Most applications are better served by one model and a few hand-written projections

Practice

Attempt each one before opening the solution. Getting it wrong first is how the idea sticks.

Think about it

Do your reads and writes actually diverge?

An employee expenses application has 400 users. They submit claims, a manager approves them, and finance exports a monthly summary. The team is proposing CQRS with a separate read database because the approvals list is slow.

What would you want to know before agreeing, and what is the most likely correct answer?

Show solution

Ask for the numbers first. How many claims per day, how many list loads per day, how long the list query takes now, and what its execution plan says. A list that takes four seconds for 400 users is usually one missing index on a foreign key, or a query loading full entity graphs to display five columns.

Then ask what the slowness costs. A four-second page that finance loads twice a month is an irritation. The same page loaded every thirty seconds by every manager is a different problem.

For 400 users the likely answer is no. Read and write volumes are both small, the shapes differ only mildly, and a second store would add projection code, lag and rebuild tooling to solve something an index solves. The proposal is treating CQRS as the cure for a query plan.

There is a defensible middle. Move the approvals list to its own row type and its own query against the same database. That gets the shape and the query cost under control, keeps one copy of the data, and involves no consistency change. If the read load later grows enough to justify a separate store, the read side is already isolated and the move is smaller.

The wider point: CQRS is a response to divergence between reads and writes. If you cannot demonstrate the divergence, you are buying the cost without the benefit.

Try it yourself

Split one screen, and keep one database

Pick the slowest list screen in an application you know. Write a row type that matches its columns exactly, and one query that returns it, without touching any entity.

Measure before and after: number of columns fetched, number of round trips, and elapsed time. Then decide whether a separate read store would add anything.

Show solution

The measurement is the exercise. A projection that fetches six columns instead of four entity graphs commonly moves a list from hundreds of milliseconds to tens, and it needs no new infrastructure.

Keeping one database is the deliberate part. You get the shape separation, the query control and the smaller payload while the data stays in one place, so no read model can be stale and no rebuild tooling is needed.

Whether the query is written in SQL or as an ORM projection matters less than whether it is written for the screen rather than derived from the entity. Both work. SQL gives more control over joins and aggregates; a projection keeps you inside one toolset.

Having measured the improvement, you are usually in a position to answer the separate-store question honestly, and the answer is usually not yet. That is a good outcome, not a failed exercise.

public sealed record InvoiceListRow(
    int InvoiceId,
    string Number,
    string CustomerName,
    decimal Total,
    DateTime DueUtc,
    bool Overdue);

// One statement, six columns, no entity materialised
public Task<IEnumerable<InvoiceListRow>> GetOutstandingAsync(
    DateTime asOfUtc, CancellationToken token) =>
    _connection.QueryAsync<InvoiceListRow>(new CommandDefinition(
        """
        SELECT i.Id AS InvoiceId,
               i.Number,
               c.Name AS CustomerName,
               i.Total,
               i.DueUtc,
               CAST(CASE WHEN i.DueUtc < @asOfUtc THEN 1 ELSE 0 END AS bit) AS Overdue
        FROM Invoices i
        JOIN Customers c ON c.Id = i.CustomerId
        WHERE i.PaidUtc IS NULL
        ORDER BY i.DueUtc
        """,
        new { asOfUtc },
        cancellationToken: token));

Knowledge check

Nothing is recorded and there is no score. The explanation appears either way.

Which observation most justifies separate read and write models?

The team wants to follow a recognised patternThe busiest read needs a shape and a volume the write model was not built for, and both have been measuredThe application has more than twenty database tablesThe codebase already uses dependency injection and a repository

Reads move to a separate store fed by a background projection. A user approves an order, the list reloads, and it still shows as open. What is this?

A bug in the projection that needs fixing before releaseExpected behaviour of the design, and a product decision about how the interface should present itProof that the read store needs a stronger transaction isolation levelA caching problem in the browser

Saved in this browser only.