Separate commands and queries. Keep the architecture simple. Introduce complexity only when the requirements justify it.
Introduction
Imagine you're developing an e-commerce application using ASP.NET Core Web API, Entity Framework Core, and SQL Server.
Your application supports two important operations:
Place an Order: Validate product availability, calculate prices, enforce business rules, and save the order.
View Order History: Retrieve previous orders with filtering, sorting, pagination, and selected fields.
Both operations work with order data.
But do they have the same responsibilities?
Not really.
Placing an order focuses on business rules and maintaining data consistency.
Viewing order history focuses on retrieving information efficiently.
This is where CQRS (Command Query Responsibility Segregation) becomes useful.
A common misconception is that implementing CQRS requires two databases: one for reads and another for writes.
It doesn't.
In many applications, starting with separate command and query models while keeping a single database is a practical architectural choice.
Let's understand why.
1. What Is CQRS?
CQRS stands for Command Query Responsibility Segregation.
It is an architectural pattern that separates operations that change application state from operations that retrieve data.
There are two main responsibilities.
Commands — Write Operations
Commands represent an intention to change the system's state.
Examples:
PlaceOrderCommandUpdateProductCommandCancelOrderCommandRegisterStudentCommand
A command handler typically validates business rules, performs the required operation, and persists changes.
Queries — Read Operations
Queries retrieve information without intentionally modifying application state.
Examples:
GetOrderByIdQueryGetOrderHistoryQueryGetProductsQueryGetStudentDetailsQuery
A query handler focuses on retrieving and shaping data for a particular use case.
The core principle:
Commands change state. Queries return data.
The separation concerns responsibilities and models, not necessarily databases or microservices.
2. Traditional Architecture vs. CQRS
In a conventional application, a single service might handle both read and write operations.
public class OrderService
{
public Task<int> PlaceOrderAsync(...)
{
// Business rules and persistence
}
public Task<OrderDto> GetOrderAsync(...)
{
// Retrieve order
}
public Task<List<OrderDto>> GetHistoryAsync(...)
{
// Filtering and pagination
}
}This isn't inherently bad.
For small applications, a single service can be perfectly reasonable.
However, as business requirements grow, read and write logic may become increasingly complex.
For example, changing the order-history response shouldn't require modifying order-placement logic.
CQRS provides a way to separate these responsibilities.
Orders
|
+-- Commands
| |
| +-- PlaceOrderCommand
| +-- CancelOrderCommand
| +-- UpdateOrderCommand
|
+-- Queries
|
+-- GetOrderByIdQuery
+-- GetOrderHistoryQuery
+-- GetOrdersByCustomerQueryEach operation has a clear purpose and can evolve independently.
3. Why Start CQRS with One Database?
One of the most important architectural decisions is choosing how to store data.
Some developers associate CQRS with separate read and write databases.
But CQRS doesn't require that separation.
We can use:
Separate command handlers
Separate query handlers
Different request and response models
Different business and validation logic
The same EF Core DbContext
The same SQL Server database
Simple Architecture
ASP.NET Core API
|
Application Layer
|
+---------+---------+
| |
Commands Queries
| |
Command Handler Query Handler
| |
Business Rules Read Projection
| |
+---------+---------+
|
EF Core
|
SQL Server
One DatabaseThis provides logical separation without introducing the operational overhead of multiple databases.
Benefits
Lower complexity: No need to implement synchronization between separate databases.
Simpler deployment: One database to configure, monitor, back up, and maintain.
Immediate visibility: Once a transaction commits, queries against the same primary database can generally read the committed data, subject to transaction isolation and caching.
Easier debugging: Commands and queries use the same source of truth.
Independent code evolution: Read and write models can change separately.
For many business applications, these benefits make single-database CQRS a strong starting point.
4. Implementing CQRS in ASP.NET Core
Let's build a simple example using:
ASP.NET Core Web API
C#
Entity Framework Core
SQL Server
MediatR-style request handlers
We'll implement two operations:
Place an order.
Retrieve order history.
Step 1: Define the Order Entity
public class Order
{
public int Id { get; set; }
public int CustomerId { get; set; }
public DateTime CreatedAt { get; set; }
public decimal TotalAmount { get; set; }
public string Status { get; set; } = "Pending";
}The entity represents order data stored in SQL Server.
In a more complete application, an order would also contain order items, pricing information, and other business properties.
Step 2: Create a Command
using MediatR;
public record PlaceOrderCommand(
int CustomerId,
decimal TotalAmount
) : IRequest<int>;This command represents a request to place an order.
Step 3: Create the Command Handler
public class PlaceOrderHandler
: IRequestHandler<PlaceOrderCommand, int>
{
private readonly AppDbContext _context;
public PlaceOrderHandler(AppDbContext context)
{
_context = context;
}
public async Task<int> Handle(
PlaceOrderCommand request,
CancellationToken cancellationToken)
{
var order = new Order
{
CustomerId = request.CustomerId,
TotalAmount = request.TotalAmount,
CreatedAt = DateTime.UtcNow,
Status = "Pending"
};
_context.Orders.Add(order);
await _context.SaveChangesAsync(
cancellationToken);
return order.Id;
}
}The handler creates an order and persists it to SQL Server.
Production consideration: This is a simplified persistence example. In a real e-commerce application, never trust a client-supplied total amount. The server should calculate prices, validate inventory, enforce authorization, and handle concurrent stock updates within an appropriate transaction.
Step 4: Create a Query
Now we need to retrieve order history.
public record OrderDto(
int Id,
decimal TotalAmount,
string Status,
DateTime CreatedAt
);
public record GetOrderHistoryQuery(
int CustomerId
) : IRequest<List<OrderDto>>;Notice that the query returns a DTO rather than the complete database entity.
This is important because read operations often require only a subset of the available fields.
Step 5: Create the Query Handler
public class GetOrderHistoryHandler
: IRequestHandler<
GetOrderHistoryQuery,
List<OrderDto>>
{
private readonly AppDbContext _context;
public GetOrderHistoryHandler(
AppDbContext context)
{
_context = context;
}
public async Task<List<OrderDto>> Handle(
GetOrderHistoryQuery request,
CancellationToken cancellationToken)
{
return await _context.Orders
.AsNoTracking()
.Where(o =>
o.CustomerId == request.CustomerId)
.OrderByDescending(o => o.CreatedAt)
.ThenByDescending(o => o.Id)
.Take(20)
.Select(o => new OrderDto(
o.Id,
o.TotalAmount,
o.Status,
o.CreatedAt))
.ToListAsync(cancellationToken);
}
}Here we're using several EF Core optimizations:
AsNoTracking() avoids unnecessary change tracking for read-only data.
Where() filters records for the requested customer.
OrderByDescending() sorts recent orders first.
Join the conversation! Your thoughts help the community grow.