#Code CQRS pattern for Web.Api Core 3.1 using Autofac, MediatR with Ado.Net and Dapper (PostgresSql)

Massimo Mannoni
5 min readJul 17, 2020

--

Build a solution based on Command Query Responsibility Segregation pattern to increase performance, scalability and security.

Simplified CQRS- and DDD-based microservice from Microsoft docs

Is pretty simple understand from name, “Command Query Responsibility Segregation” is a pattern used in CRUD context to isolate read operations from commands as create, update, delete.

The solution I’m going to describe is based on different projects operate following Microservices Architecture approach. Let’s quickly describe them.

  1. Asp.Net Core 3.1 Web.Api project is the framework we use to build services based on HTTP protocol and accessible from any client.
  2. Application layer is the project designed to be implemented as a part of our web api with the responsibility to define queries and commands
  3. Domain project contains our conceptual model of the domain
  4. Infrastructure project contains the physical implementation of the interfaces defined in our domain layer.

Prerequisites

  • ASP.NET Core 3.1
  • ASP.NET WebApi Core
  • ASP.NET Identity Core
  • Entity Framework Core 3.1
  • .NET Core Native DI
  • Autofac
  • FluentValidator
  • MediatR
  • Swagger UI
  • PostgresSql

Implementation

Starting from an Empty Solution template let’s add API project and

then add the other three projects using class library core template.

As result we should have the Web.Api as “startup project” and the others as showen below.

1. Database and Domain Model

Now it’s time to define our database , the domain model and inject our dependences using Autofac. Define our “web_user” database table and rename the Class.cs from Domain project in User.cs, after had added the SeedWork classes.

CREATE TABLE public.web_user (id uuid NOT NULL,first_name varchar(80) NULL,last_name varchar(80) NULL,email varchar(80) NULL,CONSTRAINT "User_pkey" PRIMARY KEY (id));

Following the DDD model, we need to modify the user class and add other classes in order to have a correct representation of our user domain.

First, create the UserID inherit from TypedIdValueBase (SeedWork)

then modify the User.cs adding a private constructor which assign a new guid to Id property . More details about auto-property is available on Microsoft https://docs.microsoft.com/it-it/dotnet/csharp/programming-guide/classes-and-structs/auto-implemented-properties

Now we are ready to define our simple repository interface, so let’s create a IUserRepository

and here below the implementation using Ado.Net.

2. Define Infrastructure and modules Injection

Currently we defined our simple model entity, but we need to define the connection interface in the Application project, in order to inject it, using Autofac module.

Add “Configuration\Data” folders and create an interface

public interface ISqlConnectionFactory
{
IDbConnection GetOpenConnection();
}

then on Infrastructure project, create the class implementation.

The SqlConnectionFactory basically receive the connection string, injected into constructor, assigning the value to the private field and using it on GetOpenConnection method, to open a connection with database and returning an IDbConnection object type.

The next step is define the DataAccessModule inherit from Autofac.Module

As for SqlConnectionFactory the DataAccessModule has a constructor used to receive the connection string, and pass the value directly to the SlqConnectioFactory _connectionString field once the registered component, is loaded.

The MediatorModule has a relevant role in our application so let’s describe its logic.

First we had to define a class inherit from IRegistrationSource in order to store our Types and register our set of types from an assembly specifying additional registration methods ie “AsClosedTypesOf.

A Lamba expression components allow us to resolve services from container.

Here you can find more details.

To complete this step we need to add the connection string to appsettings.json file modifying startup.cs and program.cs

So from Api project add the connection string.

"ConnectionStrings": {  "defConnectionString": "writeHereYourConnectionString"}

From NuGet Packages install the Autofac library , create the project dependences to .Application and .Infrastructure and import the relative namespace.

Using .Net Core 3.1 we need to change some default values. Let’s open the program.cs file and modify the main method that allow us to create a new service provider as IserviceProvider passing the AutofacServiceProviderFactory to the ContainerBuilder.

Then update the Startup.cs modifying the code, adding first, a const connection string and second, a method to register your modules.

3. Controllers, Queries and Commands

From Api project add a new controller, called UsersController inherit from Controller will receive the IMediator interface after setting in launchSettings.json the controller as launchUrl.

Add the http verbs you need, declaring the Route and the responseType. For each verbs we use the _mediator injected, to send queries or commands we need to process.

An example of a simple Query without parameters could be represented as the follow class where we define the response type.

The IRequest and ICommand interfaces and the relative Handlers are the contracts used to perform requests and responses through the mediator.

Here below the Command example.

Assuming that everyone has it’s own methodology, my approach to organize queries and commands is create the relative file into the Application project having attention to structure the folder in a easy readable way.

The Handler creation of your Query or Command is pretty simple to archive.

Our Query Handler will receive the request sent by mediator, will get a open connection from ISqlConnectionFactory and will use Dapper to call the function returning though an async query a Task of UserDetailsDto as List.

Commands works in the same way. The only difference is that in this case the class will inherit from ICommandHandler instead of IRequestHandler.

Are you tired? ;)

That’s all for now, here below you can find a working sample to use as base to build your own solution.

In the next articles I’ll “decorate” this sample with more functionalities.

Have a good week end !

Massimo

--

--

No responses yet