EF Core and Architecture: Repositories, the Read Side of CQRS and Moving to Dapper

Version note: The code in this post is written for EF Core 6 and runs unchanged on EF Core 10.

Guess First: How Many Rows Does This Repository Fetch?

Here is a generic repository, the kind many projects write first, arriving with the promise of "one repository for every entity":

public interface IRepository<T> where T : class
{
    IEnumerable<T> GetAll();
    T? GetById(int id);
    void Add(T entity);
}

public class Repository<T> : IRepository<T> where T : class
{
    private readonly BookstoreContext _db;

    public Repository(BookstoreContext db) => _db = db;

    public IEnumerable<T> GetAll() => _db.Set<T>();
    public T? GetById(int id) => _db.Set<T>().Find(id);
    public void Add(T entity) => _db.Set<T>().Add(entity);
}

In a table of four books, we want the ones cheaper than 10 dollars:

var books = new Repository<Book>(db);
var cheap = books.GetAll().Where(b => b.Price < 10m).ToList();

How many rows come back from the database? Two, or four? The log is the same on EF Core 6 and 10:

SELECT "b"."Id", "b"."AuthorId", "b"."Price", "b"."Stock", "b"."Title"
FROM "Books" AS "b"

There is no WHERE in the SQL. All four rows came back, the filter ran in memory, and cheap.Count was 2. The reason is the return type of GetAll. Because it is IEnumerable<T>, even though the DbSet behind it is an IQueryable, .Where is now Enumerable.Where, in other words LINQ to Objects. The IQueryable versus IEnumerable trap we saw in the LINQ post is now buried inside an architectural decision. With four books it doesn't matter; with four million orders it brings the application to a halt.

In the last post of the core block we step back from individual lines of code and look at EF Core from an architectural angle: the repository and unit of work debate, the read side of CQRS, and when it is time to move to Dapper.

DbContext Is Already a Unit of Work

Let's start with Martin Fowler's definitions. A repository is a layer that lets you access domain objects as if they were an in-memory collection. A unit of work keeps track of the changes made during a business operation and writes them all at once. Now look at DbContext: DbSet<Book> behaves like a queryable collection, the change tracker tracks every change, and SaveChanges writes them all in one transaction. EF Core already implements both patterns.

That is why writing an IUnitOfWork interface on top of DbContext that just calls SaveChangesAsync usually does nothing more than add one more layer. A generic repository is in the same position: Add, Find and Remove are already on DbSet. On top of that, the generic repository's signature hides EF Core's most powerful tools. You can't express things like Include, AsNoTracking, projection or AsSplitQuery through a GetAll() signature.

The moment you say "then let GetAll return IQueryable<T>", the abstraction springs a leak. The calling code still writes LINQ, still has to know what EF Core can and cannot translate, and you still have to supply an IQueryable when testing. The isolation the repository promised is gone; only the name remains.

Picture a driver: the car has a steering wheel, pedals and a gear stick. Someone mounts a second steering wheel on top as a "vehicle control interface", and the second wheel turns the first. But the gear stick isn't part of this interface. In the end everyone either reaches past the second wheel to touch the real gear stick, or drives the car in first gear forever.

When Does a Repository Make Sense?

That doesn't mean a repository is never useful. A repository specific to an aggregate, with a clear intent, instead of a generic one, is a different story:

public interface IOrderRepository
{
    Task<Order?> GetWithItemsAsync(int id, CancellationToken ct = default);
    void Add(Order order);
}

public class OrderRepository : IOrderRepository
{
    private readonly BookstoreContext _db;

    public OrderRepository(BookstoreContext db) => _db = db;

    public Task<Order?> GetWithItemsAsync(int id, CancellationToken ct = default)
        => _db.Orders
            .Include(o => o.Items)
            .ThenInclude(i => i.Book)
            .SingleOrDefaultAsync(o => o.Id == id, ct);

    public void Add(Order order) => _db.Orders.Add(order);
}

var orders = new OrderRepository(db);
var order = await orders.GetWithItemsAsync(1);
Console.WriteLine($"order 1: {order!.Items.Count} items, first: {order.Items[0].Book.Title}");

The output is order 1: 2 items, first: Dune. Here the repository adds something. Which parts an order is loaded with lives in one place, under a name. Domain code doesn't know about the Include chain; it just asks for "the order with its items". The repository doesn't leak IQueryable; it returns concrete results only.

Consider writing a repository in these cases. When you work with domain-driven design and want to make an aggregate's boundary visible in code. When the same query repeats in many places and you want to give it a name. Or, as we saw in the previous post, when you want to test business rules completely independently of the database; the EF documentation also says it recommends a repository layer when a test double is needed. Leave saving to the end of the workflow, not to the repository: one request, one context, one SaveChangesAsync at the end.

The Read Side of CQRS: Projection, Straight Up

CQRS suggests separating the write (command) side from the read (query) side. When writing, you load aggregates, run business rules and rely on the change tracker. When reading, you often don't even need an entity; you want to fetch the DTO going to the screen straight from the database. That is why in EF Core the read side can be written without a repository, with a projection inside a query class:

public record BookSummary(int Id, string Title, string AuthorName, decimal Price, int SoldCopies);

public class GetBestSellersQuery
{
    private readonly BookstoreContext _db;

    public GetBestSellersQuery(BookstoreContext db) => _db = db;

    public Task<List<BookSummary>> ExecuteAsync(int top, CancellationToken ct = default)
        => _db.Books
            .AsNoTracking()
            .OrderByDescending(b => b.OrderItems.Sum(i => i.Quantity))
            .Take(top)
            .Select(b => new BookSummary(
                b.Id,
                b.Title,
                b.Author.Name,
                b.Price,
                b.OrderItems.Sum(i => i.Quantity)))
            .ToListAsync(ct);
}

var best = await new GetBestSellersQuery(db).ExecuteAsync(top: 3);
foreach (var s in best)
    Console.WriteLine($"{s.Title,-14} {s.AuthorName,-14} {s.SoldCopies}");

Persuasion     Jane Austen    4
Dune           Frank Herbert  3
Dune Messiah   Frank Herbert  2

The SQL EF Core 10 generates:

SELECT "b0"."Id", "b0"."Title", "a"."Name", "b0"."Price", (
    SELECT COALESCE(SUM("o0"."Quantity"), 0)
    FROM "OrderItem" AS "o0"
    WHERE "b0"."Id" = "o0"."BookId")
FROM (
    SELECT "b"."Id", "b"."AuthorId", "b"."Price", "b"."Title", (
        SELECT COALESCE(SUM("o"."Quantity"), 0)
        FROM "OrderItem" AS "o"
        WHERE "b"."Id" = "o"."BookId") AS "c"
    FROM "Books" AS "b"
    ORDER BY (
        SELECT COALESCE(SUM("o"."Quantity"), 0)
        FROM "OrderItem" AS "o"
        WHERE "b"."Id" = "o"."BookId") DESC
    LIMIT @p
) AS "b0"
INNER JOIN "Authors" AS "a" ON "b0"."AuthorId" = "a"."Id"
ORDER BY "b0"."c" DESC

EF Core 6 produces the same structure; the differences are in the aliases ("t" instead of "b0") and the parameter name (@__p_0 instead of @p). Only the columns the screen needs come back, no entity objects are created, and the change tracker never gets involved. The "project to a DTO" rule from the performance basics post turns into an architectural decision here: a thin read side, a rich write side.

Take a critical look at the SQL as well. The same subquery for the sales total is written three times. It's not a problem for this table, but as reports like this grow, reading and steering the SQL that LINQ generates gets harder. Which brings us to the last question.

When Should You Move to Dapper?

Dapper is a thin layer where you write the SQL and it maps the results to objects. No change tracking, no LINQ translation, no migrations; just SQL and fast materialization. We cover Dapper itself in a series of its own; the question here is: in a project that uses EF Core, when should you reach for Dapper?

These are the questions I ask myself. Can the query not be expressed in LINQ, or does expressing it produce unreadable SQL? Do you need a window function, a recursive CTE or a database-specific feature? Is this a reporting screen, meaning the result will never be updated as an entity? Does the profiler really show the materialization cost of this query? If you answer yes to most of these, Dapper is the right tool for that query. If you answer no to all of them, stay with EF Core. Remember: in EF Core 6 you can also write raw SQL with FromSqlRaw and keyless entities; we saw that in the raw SQL post.

Living side by side is easy, because the two can share the same connection. Here is a report with a window function that ranks each author's best-selling books:

using System.Data.Common;
using Dapper;

DbConnection connection = db.Database.GetDbConnection();
var ranking = await connection.QueryAsync<AuthorRank>(@"
    SELECT a.Name AS AuthorName,
           b.Title,
           SUM(i.Quantity) AS Sold,
           RANK() OVER (PARTITION BY a.Id ORDER BY SUM(i.Quantity) DESC) AS RankInAuthor
    FROM OrderItem i
    JOIN Books b ON b.Id = i.BookId
    JOIN Authors a ON a.Id = b.AuthorId
    GROUP BY a.Id, a.Name, b.Id, b.Title
    ORDER BY a.Name, RankInAuthor");

public record AuthorRank(string AuthorName, string Title, long Sold, long RankInAuthor);

Frank Herbert  #1 Dune           3
Frank Herbert  #2 Dune Messiah   2
Jane Austen    #1 Persuasion     4
Jane Austen    #2 Emma           1

GetDbConnection() gives you the connection EF uses; adding Dapper is just dotnet add package Dapper. The long types in the record are no accident: SQLite returns integers as 64-bit, and if you write int, Dapper objects with A parameterless default constructor or one matching signature (System.String AuthorName, System.String Title, System.Int64 Sold, System.Int64 RankInAuthor) is required for AuthorRank materialization. You can tie write operations to the same transaction as well:

using Microsoft.EntityFrameworkCore.Storage;

await using (var tx = await db.Database.BeginTransactionAsync())
{
    var affected = await connection.ExecuteAsync(
        "UPDATE Books SET Stock = Stock + @amount WHERE AuthorId = @authorId",
        new { amount = 5, authorId = 1 },
        tx.GetDbTransaction());
    db.Orders.Add(new Order { CustomerEmail = "grace@example.com", OrderedAt = new DateTime(2026, 11, 3) });
    await db.SaveChangesAsync();
    await tx.CommitAsync();
    Console.WriteLine($"restocked rows: {affected}");
}

The output is restocked rows: 2. GetDbTransaction() gives you the ADO.NET object underneath the transaction EF opened; Dapper's UPDATE and EF's INSERT are committed in the same transaction. The rules from the transactions post apply here too. One notable detail: even though I left command logging on, EF's log showed only its own INSERT. Dapper's UPDATE never went through EF's pipeline.

Common Pitfalls

The first pitfall follows from that detail. EF knows nothing about rows you change with Dapper. If you update the row behind a tracked entity with Dapper, EF keeps showing you the old value:

var trackedDune = await db.Books.SingleAsync(b => b.Title == "Dune");
Console.WriteLine($"before: {trackedDune.Stock}");
await connection.ExecuteAsync("UPDATE Books SET Stock = 0 WHERE Title = 'Dune'");
var again = await db.Books.SingleAsync(b => b.Title == "Dune");
Console.WriteLine($"after dapper, tracked: {again.Stock}, fresh: {await db.Books.AsNoTracking().Where(b => b.Title == "Dune").Select(b => b.Stock).SingleAsync()}");

before: 15
after dapper, tracked: 15, fresh: 0

Identity resolution at work again: the second query sent its SQL, but EF returned the object it was already tracking. For the same reason, the audit fields from the interceptor post and the filters from the query filter post don't apply to Dapper queries either. Use Dapper either on the read side or on data EF isn't tracking at that moment; when you mix them, refresh the change tracker with ChangeTracker.Clear() or Reload().

The second pitfall is on the read side. Sorting by a property of a positional record after projecting into it can't be translated:

var bad = await db.Books
    .Select(b => new BookSummary(b.Id, b.Title, b.Author.Name, b.Price, b.OrderItems.Sum(i => i.Quantity)))
    .OrderByDescending(s => s.SoldCopies)
    .Take(3)
    .ToListAsync();

On EF Core 6 and 10 the result is an InvalidOperationException whose message contains could not be translated. EF can't connect the arguments passed to the constructor with the SoldCopies property. Write the sorting and Take before Select, as we did in GetBestSellersQuery.

The third pitfall is passing IQueryable around between layers. With a repository that carries IQueryable all the way up to the controller, nobody knows when and with which context the query will run; a query enumerated after the context has been disposed blows up with an ObjectDisposedException. Finish the query inside the boundary and hand out a list or a DTO. The fourth is turning architecture into dogma. In a small CRUD application, using DbContext directly is entirely legitimate; before adding a layer, you should be able to say in one sentence which problem it solves.

A Small Experiment

★☆☆ Change the return type of GetAll in the generic repository to IQueryable<T> and capture the log of the same Where query again. See the WHERE make it into the SQL, then think about what this change does to the repository's testability.

★★☆ Rewrite GetBestSellersQuery with Dapper: write the SQL that gives the same result yourself and compare the output of both versions. Which one is easier to read, and which one easier to change?

★★★ Write a PlaceOrderCommand handler: it should create the order through IOrderRepository, decrease the stock with EF Core, then add a row to a SalesLog table with Dapper inside the same transaction. Throw an error and verify that both are rolled back.

With this post the core block is complete. Over 23 posts we used EF Core 6's API surface to build models, relationships, migrations, queries, change tracking, transactions and concurrency, performance, interceptors and tests; every line we wrote also ran unchanged on EF Core 10. Now it's time for evolution across versions. In the first post of Block B we go back in time a little: what you will run into when moving a project still on EF Core 3.1 or 5 to EF Core 6, the LINQ revolution in 3.0, and what 5 brought. After that, with EF Core 7, 8, 9 and 10, we will say for each version "this is how we did it in EF Core 6, and this is how we do it now".