13 February 2019
Introduction
I often come across questions about the implementation of the CQRS pattern. Even more often I see discussions about access to the database in the context of what is better – ORM or plain SQL.
In this post I wanted to show you how you can quickly implement simple REST API application with CQRS using the .NET Core. I immediately point out that this is the CQRS in the simplest edition – the update through the Write Model immediately updates the Read Model, therefore we do not have here the eventual consistency. However, many applications do not need eventual consistency, while the logical division of writing and reading using two separate models is recommended and more effective in most solutions.
Especially for this article I prepared sample, fully working application, see full source on Github.
My goals
These are my goals that I wanted to achieve by creating this solution:
1. Clear separation and isolation of Write Model and Read Model.
2. Retrieving data using Read Model should be as fast as possible.
3. Write Model should be implemented with DDD approach. The level of DDD implementation should depend on level of domain complexity.
4. Application logic should be decoupled from GUI.
5. Selected libraries should be mature, well-known and supported.
Design
High level flow between components looks like:
As you can see the process for reads is pretty straightforward because we should query data as fast as possible. We don’t need here more layers of abstractions and sophisticated approaches. Get arguments from query object, execute raw SQL against database and return data – that’s all.
It is different in the case of write support. Writing often requires more advanced techniques because we need execute some logic, do some calculations or simply check some conditions (especially invariants). With ORM tool with change tracking and using Repository Pattern we can do it leaving our Domain Model intact (ok, almost).
Solution
Read model
Diagram below presents flow between components used to fulfill read request operation:
The GUI is responsible for creating
Query
object:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
/// <summary> /// Get customer order details. /// </summary> /// <param name="orderId">Order ID.</param> [Route("{customerId}/orders/{orderId}")] [HttpGet] [ProducesResponseType(typeof(OrderDetailsDto), (int)HttpStatusCode.OK)] public async Task<IActionResult> GetCustomerOrderDetails( [FromRoute]Guid orderId) { var orderDetails = await _mediator.Send(new GetCustomerOrderDetailsQuery(orderId)); return Ok(orderDetails); } |
|
1 2 3 4 5 6 7 8 9 |
internal class GetCustomerOrderDetailsQuery : IRequest<OrderDetailsDto> { public Guid OrderId { get; } public GetCustomerOrderDetailsQuery(Guid orderId) { this.OrderId = orderId; } } |
Then, query handler process query:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
public class SqlConnectionFactory : ISqlConnectionFactory, IDisposable { private readonly string _connectionString; private IDbConnection _connection; public SqlConnectionFactory(string connectionString) { this._connectionString = connectionString; } public IDbConnection GetOpenConnection() { if (this._connection == null || this._connection.State != ConnectionState.Open) { this._connection = new SqlConnection(_connectionString); this._connection.Open(); } return this._connection; } public void Dispose() { if (this._connection != null && this._connection.State == ConnectionState.Open) { this._connection.Dispose(); } } } |
The first thing is to get open database connection and it is achieved using
SqlConnectionFactory
class. This class is resolved by IoC Container with HTTP request lifetime scope so we are sure, that we use only one database connection during request processing.
Second thing is to prepare and execute raw SQL against database. I try not to refer to tables directly and instead refer to database views. This is a nice way to create abstraction and decouple our application from database schema because I want to hide database internals as much as possible.
For SQL execution I use micro ORM Dapper library because is almost as fast as native ADO.NET and does not have boilerplate API. In short, it does what it has to do and it does it very well.
Write model
Diagram below presents flow for write request operation:
Write request processing starts similar to read but we create the
Command
object instead of the query object:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
/// <summary> /// Add customer order. /// </summary> /// <param name="customerId">Customer ID.</param> /// <param name="request">Products list.</param> [Route("{customerId}/orders")] [HttpPost] [ProducesResponseType((int)HttpStatusCode.Created)] public async Task<IActionResult> AddCustomerOrder( [FromRoute]Guid customerId, [FromBody]CustomerOrderRequest request) { await _mediator.Send(new AddCustomerOrderCommand(customerId, request.Products)); return Created(string.Empty, null); } |
Then,
CommandHandler
is invoked:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
public class AddCustomerOrderCommandHandler : IRequestHandler<AddCustomerOrderCommand> { private readonly ICustomerRepository _customerRepository; private readonly IProductRepository _productRepository; public AddCustomerOrderCommandHandler( ICustomerRepository customerRepository, IProductRepository productRepository) { this._customerRepository = customerRepository; this._productRepository = productRepository; } public async Task<Unit> Handle(AddCustomerOrderCommand request, CancellationToken cancellationToken) { var customer = await this._customerRepository.GetByIdAsync(request.CustomerId); var selectedProducts = request.Products.Select(x => new OrderProduct(x.Id, x.Quantity)).ToList(); var allProducts = await this._productRepository.GetAllAsync(); var order = new Order(selectedProducts, allProducts); customer.AddOrder(order); await this._customerRepository.UnitOfWork.CommitAsync(cancellationToken); return Unit.Value; } } |
Command handler looks different than query handler. Here, we use higher level of abstraction using DDD approach with Aggregates and Entities. We need it because in this case problems to solve are often more complex than usual reads. Command handler hydrates aggregate, invokes aggregate method and saves changes to database.
Customer aggregate can be defined as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
public class Customer : Entity { public Guid Id { get; private set; } private readonly List<Order> _orders; private Customer() { this._orders = new List<Order>(); } public void AddOrder(Order order) { this._orders.Add(order); this.AddDomainEvent(new OrderAddedEvent(order)); } public void ChangeOrder(Guid orderId, List<OrderProduct> products, IReadOnlyCollection<Product> allProducts) { var order = this._orders.Single(x => x.Id == orderId); order.Change(products, allProducts); this.AddDomainEvent(new OrderChangedEvent(order)); } public void RemoveOrder(Guid orderId) { var order = this._orders.Single(x => x.Id == orderId); order.Remove(); this.AddDomainEvent(new OrderRemovedEvent(order)); } } |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
public class Order : Entity { public Guid Id { get; private set; } private bool _isRemoved; private decimal _value; private List<OrderProduct> _orderProducts; private Order() { this._orderProducts = new List<OrderProduct>(); this._isRemoved = false; } public Order(List<OrderProduct> orderProducts, IReadOnlyCollection<Product> allProducts) { this.Id = Guid.NewGuid(); this._orderProducts = orderProducts; this.CalculateOrderValue(allProducts); } internal void Change(List<OrderProduct> products, IReadOnlyCollection<Product> allProducts) { foreach (var product in products) { var orderProduct = this._orderProducts.SingleOrDefault(x => x.ProductId == product.ProductId); if (orderProduct != null) { orderProduct.ChangeQuantity(product.Quantity); } else { this._orderProducts.Add(product); } } var existingProducts = this._orderProducts.ToList(); foreach (var existingProduct in existingProducts) { var product = products.SingleOrDefault(x => x.ProductId == existingProduct.ProductId); if (product == null) { this._orderProducts.Remove(existingProduct); } } this.CalculateOrderValue(allProducts); } internal void Remove() { this._isRemoved = true; } private void CalculateOrderValue(IReadOnlyCollection<Product> allProducts) { this._value = this._orderProducts.Sum(x => x.Quantity * allProducts.Single(y => y.Id == x.ProductId).Price); } } |
Architecture
Solution structure is designed based on well-known Onion Architecture as follows:
Only 3 projects are defined:
· API project with API endpoints and application logic (command and query handlers) using Feature Folders approach.
· Domain project with Domain Model
· Infrastructure project – integration with database.
Summary
In this post I tried to present the simplest way to implement CQRS pattern using raw sql scripts as Read Model side processing and DDD approach as Write Model side implementation. Doing so we are able to achieve much more separation of concerns without losing the speed of development. Cost of introducing this solution is very low and and it returns very quickly.
I didn’t describe DDD implementation in detail so I encourage you once again to check the repository of the example application – can be used as a kit starter for your app the same as for my applications.
Written by: Kamil Grzybek – ITSG’s CRM expert, architect .net
Article Source: https://www.kamilgrzybek.com/design/simple-cqrs-implementation-with-raw-sql-and-ddd/
Join our newsletter
Thank you for subscribing to our newsletter.
