# CQRS: EF Core writes, Dapper reads > Commands load and save aggregates through EF Core. Queries skip the domain model and run SQL with Dapper. Both go through Wolverine's IMessageBus, so every endpoint has the same shape. Source page: https://appointme.dev/docs/concepts/cqrs/ * **Writes:** EF Core aggregates, one `DbContext` per module * **Reads:** Dapper SQL through `IDbConnectionFactory` * **Dispatch:** `IMessageBus.InvokeAsync` for both * **Paging:** `PaginationFilter`, at most 100 rows ## How a command and a query differ **Both start at an endpoint and go through Wolverine. A command ends in `SaveChangesAsync`, and a query ends in a SQL statement.** *Figure 1. One bus, two data paths.* 1. **Endpoint** — `bus.InvokeAsync(command or query)` 2. **Wolverine handler** — checks the permission, gets `CompanyId` 3. Then, in parallel: * **Command** — EF loads the aggregate, the slice changes it, `SaveChangesAsync` * **Query** — a Dapper repository runs SQL and maps rows to a DTO The query side looks like this from the endpoint. The command side is the same call with a command object: src/CRM/AppointMe.Crm/Customers/GetCustomers/GetCustomersEndpoint.cs ```cs private static async Task GetCustomers([AsParameters] GetCustomersRequest request, IMessageBus bus, CancellationToken cancellationToken) { return await bus.InvokeAsync(new GetCustomerQuery { Search = request.Search, Pagination = request.ToPaginationFilter() }, cancellationToken); } ``` | Piece | Where | What it does | | ---------------------- | ---------------------------------- | ----------------------------------------------------------------------------------------------------------------------------- | | Module `DbContext` | `/Database/` | The write model. Tracks aggregates, applies the tenant and soft-delete filters, and hands raised events to Wolverine on save. | | `IDbConnectionFactory` | `AppointMe.Shared/Database` | Opens a `SqlConnection` for Dapper. `SqlConnectionFactory` is the only implementation. | | `ExtSqlBuilder` | `AppointMe.Shared/Database/Dapper` | Dapper’s `SqlBuilder` plus `AddPagination`. The `WhereSearch` extension is in the same file. | | Repository | `/Database/` | The Dapper queries for one feature: `CustomersRepository`, `AppointmentsRepository`, `TeamRepository` | | Pagination types | `AppointMe.Shared/Pagination` | `PaginationRequest` in, `PaginationFilter` to the SQL, `PagedResult` out | ## Writes: load, change, save **A command handler loads the aggregate with EF Core, calls the slice’s operation and saves. Wolverine wraps the handler in a transaction and publishes the aggregate’s events through the outbox.** src/CRM/AppointMe.Crm/Customers/UpdateCustomer/UpdateCustomerCommandHandler.cs ```cs public async Task HandleAsync(UpdateCustomerCommand command, IPrincipal principal, CancellationToken cancellationToken) { principal.Require(CustomerPermissions.Update); var customer = await dbContext.Customers.LoadAsync(new CustomerId(command.CustomerId), cancellationToken); customer.Update( name: PersonName.Create(command.FirstName, command.LastName), dateOfBirth: DateOfBirth.CreateOrNull(command.DateOfBirth, timeProvider), gender: Gender.ParseOrNull(command.Gender), email: Email.CreateOrNull(command.Email) ); await dbContext.SaveChangesAsync(cancellationToken); } ``` | Step | What happens | | ---------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `LoadAsync` | An EF query in `Customers/Database/CustomerQueries.cs`. It throws `NotFoundException`, which the API returns as `404`. The [company filter](/docs/concepts/multi-tenancy/#writes-ef-core-query-filters) applies, so another tenant’s customer is “not found” too. | | `customer.Update(...)` | The slice’s [extension method](/docs/concepts/vertical-slices/) changes the properties and raises `CustomerUpdatedEvent` | | `SaveChangesAsync` | Writes the row, checks the `Version` row version, and lets Wolverine store the raised events in its outbox in the same transaction | The transaction and the event hand-off come from the host’s Wolverine setup: src/AppointMe.Api/Wolverine/WolverineHostBuilderExtensions.cs ```cs options.UseEntityFrameworkCoreTransactions(); options.PublishDomainEventsFromEntityFrameworkCore(root => root.Events); options.Policies.UseDurableLocalQueues(); options.Policies.AutoApplyTransactions(); ``` Every entity configuration in the solution maps a `Version` column with `IsRowVersion()`. If someone else saved the row after you loaded it, `SaveChangesAsync` throws `DbUpdateConcurrencyException`, and the API answers `409` with `code: "concurrency_conflict"` (see [API conventions](/docs/concepts/api-conventions/)). ## Reads: SQL with Dapper **A query handler calls a Dapper repository, which fills a SQL template with `ExtSqlBuilder` and maps the rows to a DTO. There’s no change tracking and no aggregate.** The template has placeholders that the builder fills in: src/CRM/AppointMe.Crm/Customers/Database/CustomersRepository.cs ```cs private const string CustomersSqlTemplate = """ SELECT [Id], [FirstName], [LastName], [DateOfBirth], [Gender], [Email], [RegistrationDate] /**totalcount**/ FROM [crm].[Customers] /**where**/ ORDER BY [RegistrationDate] DESC, [Id] DESC /**pagination**/ """; ``` src/CRM/AppointMe.Crm/Customers/Database/CustomersRepository.cs ```cs public async Task> GetAll(string[] searchTokens, PaginationFilter pagination, CompanyId companyId, CancellationToken cancellationToken) { var builder = new ExtSqlBuilder() .AddPagination(pagination) .Where("[CompanyId] = @CompanyId", new { CompanyId = companyId.Value }) .Where("[IsDeleted] = 0") .WhereSearch("[SearchKey]", searchTokens); var template = builder.AddTemplate(CustomersSqlTemplate); using var connection = await connectionFactory.OpenConnectionAsync(cancellationToken); var (records, totalCount) = await connection .QueryWithPaginationAsync(template.RawSql, template.Parameters); return records.Select(record => record.ToDto()).ToPagedResult(pagination, totalCount); } ``` | Placeholder | Filled by | Becomes | | ------------------ | ----------------------- | ------------------------------------------------- | | `/**where**/` | Each `.Where(...)` call | `WHERE … AND …`, with parameters | | `/**totalcount**/` | `AddPagination` | `,COUNT(*) OVER () AS [TotalCount]` | | `/**pagination**/` | `AddPagination` | `OFFSET @Offset ROWS FETCH NEXT @Limit ROWS ONLY` | | `/**orderby**/` | `.OrderBy(...)` | `ORDER BY …` (used by `AppointmentsRepository`) | `QueryWithPaginationAsync` uses Dapper’s multi-mapping to split the `TotalCount` column off each row, so one round trip returns the page and the total. Rows land in `CustomerRecord`, a class of primitives, and `ToDto()` turns them into `CustomerDto`. Dapper doesn’t know about typed IDs or value objects, so parameters pass `.Value` (see [IDs & value objects](/docs/concepts/ids-and-value-objects/)). `AddSharedModule` registers two Dapper type handlers once at startup: `DateOnlyTypeHandler`, and `UtcDateTimeOffsetTypeHandler`, which reads both `datetime2` (Booking) and `datetimeoffset` (the other modules) back as UTC. ## Pagination and search **List endpoints take `?page=&limit=` and return a `PagedResult`. Search splits the text into words, and every word must match.** | Type | Does | | ------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------- | | `PaginationRequest` | Binds `limit` and `page` from the query string, defaulting to 10 and 1. Request classes inherit it: `GetCustomersRequest : PaginationRequest`. | | `PaginationFilter` | Clamps the values and computes the offset (below) | | `PagedResult` | `Items`, `TotalCount`, `Page`, `Limit`, and a computed `TotalPages` | src/AppointMe.Shared/Pagination/PaginationFilter.cs ```cs public PaginationFilter(int limit, int page) { Limit = Math.Clamp(limit, MinLimit, MaxLimit); Page = Math.Max(page, MinPage); } public int Limit { get; } public int Page { get; } public int Offset => Limit * (Page - 1); ``` `MinLimit` is 1 and `MaxLimit` is 100, so `?limit=500` quietly returns 100 rows. Search text goes through `Tokenize()` in `AppointMe.Shared/Utilities`: src/AppointMe.Shared/Utilities/SearchExtensions.cs ```cs public string[] Tokenize() { if (string.IsNullOrWhiteSpace(search)) { return []; } return [ .. search .Split(' ', StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries) .Select(token => token.ToLowerInvariant()) .Distinct() .Take(30) ]; } ``` `WhereSearch` then adds one `[SearchKey] LIKE '%' + @SearchToken0 + '%'` clause per token, joined with `AND`. `SearchKey` is a stored computed column that EF defines and Dapper reads: src/CRM/AppointMe.Crm/Customers/Database/CustomerTypeConfiguration.cs ```cs builder.Property("SearchKey") .HasComputedColumnSql( "LOWER(CONCAT([FirstName], ' ', ISNULL([LastName], ''), ' ', ISNULL([Email], '')))", stored: true); ``` So `ann smith` finds a customer whose name or email contains both `ann` and `smith`, in any order. Organizations’ `TeamRepository` searches the team list the same way. ## When to use which **Use EF Core when you change state or need the aggregate’s rules. Use Dapper when you shape data for a screen.** | You’re writing | Use | Example | | ------------------------------------- | --------------------------------------- | ------------------------------------------------------------------------------------------------ | | A command that changes an aggregate | EF Core: load, call the operation, save | `UpdateCustomerCommandHandler` | | A check inside a command | EF Core, so the tenant filter applies | `ScheduleAppointmentCommandHandler` checks the provider exists with `AnyAsync` | | A list or detail endpoint | A Dapper repository | `CustomersRepository.GetAll` | | A read that joins tables for a screen | Dapper | `AppointmentsRepository` joins `Appointments`, `ServiceProviders` and `Attendees` | | A contract another module calls | Either | CRM’s `CustomerRehydrationSource` uses EF with `IgnoreQueryFilters([CustomerFilters.CompanyId])` | Stay inside your schema A Dapper query may only name tables in its own module’s schema. Booking’s SQL reads `[booking].[Attendees]`, its projection of CRM customers, and never `[crm].[Customers]`. See [Modular monolith](/docs/concepts/modular-monolith/). ## Gotchas | Symptom | Cause | Fix | | -------------------------------------------------------- | ------------------------------------------------------------------------------------ | --------------------------------------------------------------------------------------------------------- | | A list shows another company’s rows | The Dapper query has no `[CompanyId] = @CompanyId`. EF’s filters don’t reach Dapper. | Add the predicate. See [Multi-tenancy](/docs/concepts/multi-tenancy/#reads-dapper-carries-the-predicate). | | Soft-deleted customers appear in a list | Soft delete is an EF filter too | Add `.Where("[IsDeleted] = 0")`, as `CustomersRepository` does | | `TotalCount` is 0 on a page past the end | The total comes from `COUNT(*) OVER ()` on the returned rows, and that page has none | Clamp the page to `TotalPages` on the client | | `409` with `code: "concurrency_conflict"` | The row’s `Version` changed since you loaded it | Reload and retry | | A paged query keeps running after the client disconnects | `QueryWithPaginationAsync` takes no `CancellationToken` | Add a token parameter to the extension and pass a `CommandDefinition` | | A renamed column breaks a list at runtime | EF owns the schema, and Dapper’s SQL is a string | Search the repositories for the old column name when you change a mapping | ### Why two data access libraries? EF Core is strong on the write side. Change tracking, the query filters, row-version checks, owned value objects, migrations and the event hand-off all hang off the `DbContext`. Reads that shape data for a screen are a different job: joins across projections, window functions and computed columns are shorter and more predictable as SQL you wrote, and Dapper runs that SQL and maps the rows. The cost is two views of one schema to keep in step. EF owns it, Dapper reads it by column name, and the safety nets EF gives for free (the tenant filter, soft delete) are manual in SQL. ### Why send queries through the bus too? Wolverine’s handler policies do the cross-cutting work: they supply the `CompanyId` and the principal (see [Identity & principal](/docs/concepts/identity-and-principal/)). A query that goes through `InvokeAsync` gets those for free, so query handlers and command handlers have the same shape. `InvokeAsync` runs the handler inline, in the same request, so the cost is one level of indirection, not a network hop. ## Next * [Multi-tenancy](/docs/concepts/multi-tenancy/) (Concept) * [IDs & value objects](/docs/concepts/ids-and-value-objects/) (Concept) * [Add an endpoint](/docs/guides/add-an-endpoint/) (Guide)