DEV Community

Cover image for How To Setup CQRS with MediatR in .NET 5.0 and database MariaDB?
Dewa Mahendra
Dewa Mahendra

Posted on

How To Setup CQRS with MediatR in .NET 5.0 and database MariaDB?

In this article let’s talk about CQRS in .NET 5.0 and its implementation along with MediatR and Entity Framework Core-Code First Approach. I will implement this pattern on a WebApi Project. The source code of this sample is linked at the end of the post. Of the several design patterns available, CQRS is one of the most commonly used patterns that helps architect the Solution to accommodate the Onion Architecture. I will be writing a post on Implementation of Onion Architecture too later, the cleanest way to structure a .NET Solution. So, Let’s get started!


What Is CQRS?

CQRS, Command Query Responsibility Segregation is a design pattern that separates the read and write operations of a data source. Here Command refers to a Database Command, which can be either an Insert / Update or Delete Operation, whereas Query stands for Querying data from a source. It essentially separates the concerns in terms of reading and writing, which makes quite a lot of sense. This pattern was originated from the Command and Query Separation Principle devised by Bertrand Meyer. It is defined on Wikipedia as follows.

“It states that every method should either be a command that performs an action or a query that returns data to the caller, but not both. In other words, asking a question should not change the answer. More formally, methods should return a value only if they are referentially transparent and hence possess no side effects.”
Wikipedia

The problem with traditional architectural patterns is that the same data model or DTO is used to query as well as update a data source. This can be the go-to approach when your application is related to just CRUD operations and nothing more. But when your requirements suddenly start getting complex, this basic approach can prove to be a disaster.

In practical applications, there is always a mismatch between the read and write forms of data, like the extra properties you may require to update. Parallel operations may even lead to data loss in the worst cases. That means, you will be stuck with just one Data Transfer Object for the entire lifetime of the application unless you choose to introduce yet another DTO, which in-turn may break your application architecture.

The idea with CQRS is to allow an application to work with different models. Long story short, you have one model that has data needed to update a record, another model to insert a record, yet another to query a record. This gives you flexibility with varying and complex scenarios. You don't have to rely on just one DTO for the entire CRUD Operations by implementing CQRS.

CQRS Concept

Pros Of CQRS

There are quite of lot of advantages to using the CQRS Pattern for your application. A few of them are as follows.

Optimised Data Transfer Objects
Thanks to the segregated approach of this pattern, we will no longer need those complex model classes within our application. Rather we have one model per data operation that gives us all the flexibility in the world.

Highly Scalable
Having control over the models in accordance with the type of data operations makes your application highly scalable in the long run.

Improved Performance
Practically speaking there are always 10 times more Read Operations as compared to the Write Operation. With this pattern you could speed up the performance on your read operations by introducing a cache or NOSQL Db like Redis or Mongo. CQRS pattern will support this usage out of the box, you would not have to break your head trying to implement such a cache mechanism.


Cons Of CQRS

Added Complexity and More Code
The one thing that may concern a few programmers is that this is a code demanding pattern. In other words, you will end up with at least 3 or 4 times more code-lines than you usually would. But everything comes for a price. This according to me is a small price to pay while getting the awesome features and possibilities with the pattern.

Implementing CQRS Pattern In .NET 5.0 WebApi
Let' s build an .NET 5.0 WebApi to showcase the implementation and better understand the CQRS Pattern. I will push the implemented solution over to Github, You can find the link to my repository at the end of this post. Let us build an API endpoint that does CRUD operations for a Product Entity, ie, Create / Delete / Update / Delete product record from the Database. Here, I use Entity Framework Core as the ORM to access data from my local DataBase.

PS - We will not be using any advanced architectural patterns, but let's try to keep the code clean. The IDE I use is Visual Studio 2019 Community. If you do not have it, I totally recommend getting it. It's completely free. Read the installation guide here.

Secure Parallel Operations
Since we have dedicated models per oprtation, there is no possibility of data loss while doing parellel operations.

Setting up the Project

Open up Visual Studio and Create a new ASP.NET Core Web Application with the WebApi Template.

Installing the required Packages
Install the following packages to your API project via the Package Manager Console. Just Copy and Paste the below lines over to your Package Manager Console. All the required packages get installed. We will explore these packages as we progress.

Install-Package Microsoft.EntityFrameworkCore
Install-Package Microsoft.EntityFrameworkCore.Relational
install-package microsoft.entityframeworkcore.design
install-package Pomelo.EntityFrameworkCore.MySql
install-package Microsoft.EntityFrameworkcore.Tools -version 3.1.10
Install-Package MediatR
Install-Package MediatR.Extensions.Microsoft.DependencyInjection
Enter fullscreen mode Exit fullscreen mode

Adding the Product Model

Since we are following a code first Approach, let’s design our data models. Add a Models Folder and create a new class named Product with the following properties.

public class Product
   {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Barcode { get; set; }
        public bool IsActive { get; set; } = true;
        public string Description { get; set; }
        public decimal Rate { get; set; }
        public decimal BuyingPrice { get; set; }
        public string ConfidentialData { get; set; }
    }
Enter fullscreen mode Exit fullscreen mode

Adding the Context Class and Interface
Make a new Folder called Context and add a class named Application Context. This particular class will help us to access the data using Entity Framework Core ORM.

public class ApplicationContext : DbContext
    {
        public DbSet<Product> Products { get; set; }
        public ApplicationContext(DbContextOptions<ApplicationContext> options)
            : base(options)
        { }
  public async Task<int> SaveChanges()
        {
            return await base.SaveChangesAsync();
        }
    }
Enter fullscreen mode Exit fullscreen mode

PRO TIP — How to Extract an Interface from a Class?

Now that we have completed the class, let me show you an easy way to generate an Interface for any given class. Visual Studio is much powerful than what we think it is. So here is how it goes.

  1. Go to the class that we need an Interface for. In our case, go to the ApplicationContext.cs class.
  2. Select the entire class.
  3. Once selected, go to Edit -> Refactor -> Extract Interface.
  4. VS will ask for confirmation. Verify the name of the Interface to be generated and click Ok.
  5. Boom, we have our Interface ready. Imagine how helpful this feature will be when we have pretty long classes.

Extract interface

Configuring the API Services to support Entity Framework Core

Navigate to your API Project’s Startup class. This is the class where the Application knows about various services and registrations required. Let’s add the support for EntityFrameworkCore. Just add these lines to your Startup Class’s ConfigureServices method. This will register the EF Core with the application.

 var mySqlVer = new Version(8, 0, 21);
            var connection = Configuration.GetConnectionString("CqrsDatabase");
            services.AddDbContext<ApplicationContext>(
                dbContextOptions => dbContextOptions
                    .UseMySql(connection, new MySqlServerVersion(mySqlVer),
                        mysqlOptions => mysqlOptions
                                .EnableRetryOnFailure(10, TimeSpan.FromSeconds(30), null)
                                .CommandTimeout(600)
                                .MigrationsAssembly(typeof(ApplicationContext).Assembly.FullName)
                            )
                );
Enter fullscreen mode Exit fullscreen mode

Line 2 Says about the Connection String that is named as CqrsDatabase. But we haven’t defined any such connection, have we?

Defining the Connection String in appsettings.json

We will need to connect a data source to the API. For this, we have to define a connection string in the appsettings.json found within the API Project. For demo purposes, I am using LocalDb Connection. You could scale it up to support multiple Database type. Various connection string formats can be found here.

Here is what you would add to your appsettings.json.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "ConnectionStrings": {
    "CqrsDatabase": "server=localhost;userid=<userId>;pwd=<pwd>;port=<port>;database=<dbName>;sslmode=none;"
  },
  "AllowedHosts": "*"
}
Enter fullscreen mode Exit fullscreen mode

please input your user id, pwd, and your database first.

Generating the Database

Now we have our models and the connection string ready, all we have to do is to generate a database from the defined models. For this, we have to use the Package Manager Console of Visual Studio. You can open this by going to Tools -> Nuget Package Manager -> Package Manager Console.

Before continuing, let’s check if there are any Build issues with the Solution. Build the Application once to ensure that there is no error, because it is highly possible for the next steps to not show any proper warning and fail if any errors exist.

Once the Build has succceeded, Let’s start by adding the migrations.

add-migration "initial"

After that, move on to update the database

update-database

The Mediator Pattern

While building applications, especially ASP.NET Core Applications, it’s highly important to keep in mind that we always have to keep the code inside controllers as minimal as possible. Theoretically, Controllers are just routing mechanisms that take in a request and sends it internally to other specific services/libraries and return the data. It wouldn’t really make sense to put all your validations and logics within the Controllers.

Mediator pattern is yet another design pattern that dramatically reduces the coupling between various components of an application by making them communicate indirectly, usually via a special mediator object. We will dive deep into Mediator in another post. Essentially, the Mediator pattern is well suited for CQRS implementation.

MediatR Library

MediatR is a library that helps implements Mediator Pattern in .NET. The first thing we need to do, is to install the following packages.

Configuring MediatR

We have already installed the required package to our application. To register the library, add this line to the end of our API startup class in the ConfigureService Method.

services.AddMediatR(typeof(ApplicationContext).Assembly);

Creating the Product Controller

To the Controllers Folder, Add a new Empty API Controller and name it ProductController.

Product Controller

Implementing the CRUD Operations

CRUD essentially stands for Create, Read, Update, and Delete. These are the Core components of RESTFul APIs. Let’s see how we can implement them using our CQRS Approach. Create a Folder named Features in the root directory of the Project and subfolders for the Queries and Command.

Folder Structure

Queries

Here is where we will wire up the queries, ie, GetAllProducts and GetProductById. Make 2 Classes under the ProductFeatures / Queries Folder and name them GetAllProductsQuery and GetProductByIdQuery

Query Get All Products

using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using MediatR;
using Microsoft.EntityFrameworkCore;
using WebApiCQRS.Context;
using WebApiCQRS.Models;

namespace WebApiCQRS.Features.Queries
{
    public class GetAllProductsQuery : IRequest<IEnumerable<Product>>
    {
        public class GetAllProductQueryHandler : IRequestHandler<GetAllProductsQuery, IEnumerable<Product>>
        {

            private readonly IApplicationContext _context;

            public GetAllProductQueryHandler(IApplicationContext context)
            {
                _context = context;
            }

            public async Task<IEnumerable<Product>> Handle(GetAllProductsQuery query, CancellationToken cancellationToken)
            {
                var productList = await _context.Products.ToListAsync(cancellationToken: cancellationToken);

                return productList?.AsReadOnly();
            }
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Line 11 suggests that we intend to return an IEnumerable list of Products from this Class implementing the IRequest interface of MediatR. Every request must have a request handler. Here it is mentioned in Line 13. At Line 23, we define how the request is being handled. This is almost the same for all types of requests and commands.

Query to Get Product By Id

using System.Linq;
using System.Reflection.Metadata.Ecma335;
using System.Threading;
using System.Threading.Tasks;
using MediatR;
using Microsoft.EntityFrameworkCore;
using WebApiCQRS.Context;
using WebApiCQRS.Models;

namespace WebApiCQRS.Features.Queries
{
    public class GetProductByIdQuery : IRequest<Product>
    {
        public int Id { get; set; }

        public class GetProductByIdQueryHandler : IRequestHandler<GetProductByIdQuery, Product>
        {

            private readonly IApplicationContext _context;
            public GetProductByIdQueryHandler(IApplicationContext context)
            {
                _context = context;
            }

            public async Task<Product> Handle(GetProductByIdQuery query, CancellationToken cancellationToken)
            {
                var existingProduct = _context.Products.Where(x => x.Id == query.Id).FirstOrDefaultAsync(cancellationToken);

                var result = existingProduct == null ? null : await existingProduct;

                return result;
            }
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Line 14 is the id of the product we need to fetch. At line 27 we are query the database and fetch the record with id as our query id.

Commands

Add the following classes to ProductFeatures / Commands.
1.CreateProductCommand
2.DeleteProductByIdCommand
3.UpdateProductCommand

Command to Create a New Product

using System.Threading;
using System.Threading.Tasks;
using MediatR;
using WebApiCQRS.Context;
using WebApiCQRS.Models;

namespace WebApiCQRS.Features.Commands
{
    public class CreateProductCommand : IRequest<int>
    {
        public string Name { get; set; }
        public string Barcode { get; set; }
        public string Description { get; set; }
        public decimal BuyingPrice { get; set; }
        public decimal Rate { get; set; }

        public class CreateProductCommandHandler : IRequestHandler<CreateProductCommand, int>
        {
            private readonly IApplicationContext _context;

            public CreateProductCommandHandler(IApplicationContext context)
            {
                _context = context;
            }

            public async Task<int> Handle(CreateProductCommand command, CancellationToken cancellationToken)
            {
                var product = new Product();
                product.Barcode = command.Barcode;
                product.Name = command.Name;
                product.BuyingPrice = command.BuyingPrice;
                product.Rate = command.Rate;
                product.Description = command.Description;

                _context.Products.Add(product);
                await _context.SaveChanges();
                return product.Id;
            }
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Command to Delete a Product By Id

using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using MediatR;
using Microsoft.EntityFrameworkCore;
using WebApiCQRS.Context;

namespace WebApiCQRS.Features.Commands
{
    public class DeleteProductByIdCommand : IRequest<int>
    {
        public int Id { get; set; }
        public class DeleteProductByIdCommandHandler : IRequestHandler<DeleteProductByIdCommand, int>
        {
            private readonly IApplicationContext _context;
            public DeleteProductByIdCommandHandler(IApplicationContext context)
            {
                _context = context;
            }
            public async Task<int> Handle(DeleteProductByIdCommand command, CancellationToken cancellationToken)
            {
                var product = await _context.Products.FirstOrDefaultAsync(x => x.Id ==command.Id, cancellationToken: cancellationToken);
                if (product == null) return default;

                _context.Products.Remove(product);
                await _context.SaveChanges();
                return product.Id;
            }
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Command to Update a Product

using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using MediatR;
using Microsoft.EntityFrameworkCore;
using WebApiCQRS.Context;

namespace WebApiCQRS.Features.Commands
{
    public class UpdateProductCommand : IRequest<int>
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Barcode { get; set; }
        public string Description { get; set; }
        public decimal BuyingPrice { get; set; }
        public decimal Rate { get; set; }

        public class UpdateProductCommandHandler : IRequestHandler<UpdateProductCommand, int>
        {
            private readonly IApplicationContext _context;
            public UpdateProductCommandHandler(IApplicationContext context)
            {
                _context = context;
            }
            public async Task<int> Handle(UpdateProductCommand command, CancellationToken cancellationToken)
            {
                var product = _context.Products.FirstOrDefault(x => x.Id == command.Id);
                if (product == null)
                    return default;
                else
                {
                    product.Barcode = command.Barcode;
                    product.Name = command.Name;
                    product.BuyingPrice = command.BuyingPrice;
                    product.Rate = command.Rate;
                    product.Description = command.Description;

                    await _context.SaveChanges();
                    return product.Id;
                }
            }
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Product Controller

using System.Threading.Tasks;
using MediatR;
using Microsoft.AspNetCore.Mvc;
using WebApiCQRS.Features.Commands;
using WebApiCQRS.Features.Queries;


namespace WebApiCQRS.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ProductController : ControllerBase
    {
        private IMediator _mediator;

        protected IMediator Mediator => _mediator ??= HttpContext.RequestServices.GetService(typeof(IMediator)) as IMediator;

        [HttpPost]
        public async Task<IActionResult> Create(CreateProductCommand command)
        {
            return Ok(await Mediator.Send(command));
        }
        [HttpGet]
        public async Task<IActionResult> GetAll()
        {
            return Ok(await Mediator.Send(new GetAllProductsQuery()));
        }

        [HttpGet("{id}")]
        public async Task<IActionResult> GetById(int id)
        {
            return Ok(await Mediator.Send(new GetProductByIdQuery { Id = id }));
        }
        [HttpDelete("{id}")]
        public async Task<IActionResult> Delete(int id)
        {
            return Ok(await Mediator.Send(new DeleteProductByIdCommand { Id = id }));
        }
        [HttpPut("{id}")]
        public async Task<IActionResult> Update(int id, UpdateProductCommand command)
        {
            if (id != command.Id)
            {
                return BadRequest();
            }
            return Ok(await Mediator.Send(command));
        }

    }
}
Enter fullscreen mode Exit fullscreen mode

Testing

Since we are done with the implementation, let’s check the result. For this we use Swagger UI. We have already installed the package. Let’s configure it.

Configure Swagger

With .NET 5.0 you don’t need to setting up the Swagger manually again like .NET Core 3.1 . Check it out our full startup.cs as follows :

using System;
using System.Reflection;
using MediatR;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.OpenApi.Models;
using WebApiCQRS.Context;
using WebApiCQRS.Features.Commands;
using WebApiCQRS.Features.Queries;
using WebApiCQRS.Models;

namespace WebApiCQRS
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            // Configuring the API Services to support Entity Framework Core
            var mySqlVer = new Version(8, 0, 21);
            var connection = Configuration.GetConnectionString("CqrsDatabase");
            services.AddDbContext<ApplicationContext>(
                dbContextOptions => dbContextOptions
                    .UseMySql(connection, new MySqlServerVersion(mySqlVer),
                        mysqlOptions => mysqlOptions
                                .EnableRetryOnFailure(10, TimeSpan.FromSeconds(30), null)
                                .CommandTimeout(600)
                                .MigrationsAssembly(typeof(ApplicationContext).Assembly.FullName)
                            )
                );
            services.AddControllers();
            services.AddSingleton<IHttpContextAccessor, HttpContextAccessor>();

            //set the MediatR
            services.AddScoped<IApplicationContext, ApplicationContext>();
            services.AddMediatR(typeof(ApplicationContext).Assembly);

            //set the Swagger
            services.AddControllers();
            services.AddSwaggerGen(c =>
            {
                c.SwaggerDoc("v1", new OpenApiInfo { Title = "WebApiCQRS", Version = "v1" });
            });
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
                app.UseSwagger();
                app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "WebApiCQRS v1"));
            }

            app.UseHttpsRedirection();

            app.UseRouting();

            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Testing with Swagger

Now build the application and run it. Navigate to https://localhost:44311/swagger/. PS , your localhost port may vary. Navigate accordingly.

Swagger UI

This is Swagger UI. It lets you document and test your APIs with great ease. To add a new product, click on the POST dropdown and add the details of the new product like below.

Product Endpoint

Once done, click on execute. This will try to add the specified record to the database. Now let’s see the list of products. Navigate to the GET method and click on execute. This is how easy things get with swagger.

Response Body

Summary

We have covered CQRS implementation and definition, Mediator pattern and MediatR Library, Entity Framework Core Implementation, Swagger Integration, and more. If I missed out on something or was not clear with the guide, let me know in the comments section below. Is CQRS your go-to approach for Complicated Systems? Let me know. I have uploaded this code in github too so you can try it on your local machine and happy coding guys. :)

Github link : web-api-cqrs

Top comments (0)