ASP.NET Core: Implementing CQRS Pattern

-

In this post, we will be implementing Command Query Responsibility  Segregation (CQRS) pattern. This pattern is used to separate read and write operations for a data store. The advantage of implementing this pattern is to increase performance, scalability, and security. 

What is the Command Query Responsibility  Segregation (CQRS) pattern?

In the CQRS pattern, the Command refers to a Database command that represents Insert, Update, and Delete Operations. The Query means the Read operations from the Data Store. This means that we are separating Read and Operations separately to provide service access to callers who are interested in performing either Read or Write operations at a time.    

Why do we need CQRS Pattern?

Technically the CQRS implementation helps us to create separate services (APIs) for reading data and writing data. This is useful when we want to use the same data store for performing Read/Write operations. If we implement the logic for performing Read/Write operations in the single service then the increase in concurrent Read/Write requests to this single service may delay the executions. Moreover, if we need to perform any updates in logic for read operations e.g. conditional searches or criteria-based searches then it needs the modification in service, and during this time the other write operations will not be accessible. In such situations, it's always better to implement separate services for Read/Write Operations and hence CQRS pattern is useful.

Figure 1 provides an idea of the CQRS pattern



Figure 1: The CQRS Pattern 


Implementation of CQRS Pattern

To implement the code for this article you must be aware of the concepts of ASP.NET core. I have already published articles on ASP.NET Core on this and this link. The code for this article is implemented using Visual Studio 2022 Enterprise Edition with .NET 7 SDK and the database created in SQL Server database.

Listing 1 shows the script for creating a database and table

Create Database Company;

Go

Use Company;

Go

CREATE TABLE [dbo].[Department](

       [DeptUniqueId] [int] IDENTITY(1,1) NOT NULL,

       [DeptNo] [varchar](20) NOT NULL,

       [DeptName] [varchar](100) NOT NULL,

       [Capacity] [int] NOT NULL,

       [Location] [varchar](100) NOT NULL,

PRIMARY KEY CLUSTERED([DeptUniqueId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]

GO

Listing 1: Database and Table Script

Step 1: Open Visual Studio 2022 and create a new ASP.NET Core API project targetted .NET 7. Name this project Core_API_CQRS.  

Step 2: In this project add the following packages so that we can use EntityFrameork Core 7 and build the project

Microsoft.EntityFrameworkCore

Microsoft.EntityFrameworkCore.SqlServer

Microsoft.EntityFrameworkCore.Relational

Microsoft.EntityFrameworkCore.Design

Microsoft.EntityFrameworkCore.Tools

Step 3: Open the command prompt and navigate to the project folder and run the command shown in Listing 2 to generate entity classes and DbContext class for performing CRUD Operations. We are using the EntityFramework Core Database First approach. 

dotnet ef dbcontext  scaffold  “Data Source=.;Initial Catalog=Company; Integrated Security=SSPI; TrustServerCertificate=True”  Microsoft.EntityFrameworkCore.SqlServer -o Models

 Listing 2: The command to scaffold DbContext using EntityFramework Core     

Once this command is executed successfully, the Models folder will be added to the Project. This folder will have the Department.cs file that contains the Department entity class. The CompanyContext.cs file contains the CopanyContext class that is derived from the DbContext class to manage the mapping with the Department table in the Company database. The CompanyContext class contains OnConfiguring() method with the connection string in it. Copy the connection string from this method and paste it into the appsettings.json file as shown in Listing 3. We will be reading this connection string using the ICofiguration contract. 

 

"ConnectionStrings": {

    "AppConnStr": "Data Source=.;Initial Catalog=Company;Integrated Security=SSPI"

  }

Listing 3: The Database Connection String

Step 4: In the project add a new folder and name it CQRSPattern. We will be using this folder to add contract interfaces and their classes for performing Query and Command  (Read and Write) operations. In this folder add a new interface file and name it IQueryRepository.cs. In this file add the code as shown in Listing 4.


public interface IDepartmentQueriesRepository
{
   Task<IEnumerable<Department>> GetDepartmentsAsync();
   Task<Department> GetByDeptNoAsync(int dno);
}

Listing 4: The Query Repository Interface           

The interface shown in Listing 4, declares methods for performing Query (read) operations. In the same folder add a new interface file and name it ICommandRepository.cs. In this file, we will add an ICommandRepository interface that contains Command methods (write) as shown in Listing 5


public interface IDepartmentCommandRepository
{
  Task<Department> SaveDepartmentAsync(Department dept);
}

Listing 5: The Command methods

Step 5: In the same folder created in Step 4, add a new class file named QueryClass.cs. In this file, we will add DepartmentQueryRepository class. This class will implement IDepartmentQueryRepository interface and will implement all of its methods. These methods will be constructor-injected CompanyContext class so that we can perform Query operations in this class. The code for this class is shown in Listing 6


public class DepartmentQueryRepository : IDepartmentQueriesRepository
{
        CompanyContext ctx;

        public DepartmentQueryRepository(CompanyContext ctx)
        {
            this.ctx = ctx;
        }

        async Task<Department> IDepartmentQueriesRepository.GetByDeptNoAsync(int dno)
        {
            return await ctx.Departments.FindAsync(dno);
        }

        async Task<IEnumerable<Department>> IDepartmentQueriesRepository.GetDepartmentsAsync()
        {
            return await ctx.Departments.ToListAsync();
        }
}

Listing 6: The Query class

In the same folder, add a new class file and name it CommandClass.cs. In this class file, we add a DepartmentCommandRepository class. Like DepartmentQueryRepository, the DepartmentCommandRepository is also a constructor injected with CompanyContext class. This class implements IDepartmentCommandRepository interface and implements all its methods so that Command (Write) operations can be performed. The code for the class is shown in Listing 7.


public class DepartmentCommandRepository : IDepartmentCommandRepository
{
        CompanyContext ctx;

        public DepartmentCommandRepository(BajajCompanyContext ctx)
        {
            this.ctx = ctx;
        }

        async Task<Department> IDepartmentCommandRepository.SaveDepartmentAsync(Department dept)
        {
            var result = await ctx.Departments.AddAsync(dept);
            await ctx.SaveChangesAsync();
            return result.Entity;
        }
}

Listing 7: The DepartmentCommandRepository class


Step 6: We will register DbContext, Command, and Query classes in Dependency Container as shown in Listing 8


// Add services to the container.
builder.Services.AddDbContext<BajajCompanyContext>(options => 
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("AppConnStr"));
});
builder.Services.AddControllers()
     .AddJsonOptions(options => {
         options.JsonSerializerOptions.PropertyNamingPolicy = null;
     });
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

builder.Services.AddScoped<IDepartmentCommandRepository, DepartmentCommandRepository>();
builder.Services.AddScoped<IDepartmentQueriesRepository, DepartmentQueryRepository>();

Listing 8: Dependency Registration

Now we have created two separate classes for Command and Query operations, we will add two separate API controllers to the Controllers folder of the project.        

Step 7: In the Controllers folder, add a new Empty API Controller and name it DepartmentQueryController. In this controller, we will inject the IDepartmentQueriesRepository using constructor injection. This controller defines two HttpGet methods to perform Query operations as shown in Listing 9


[Route("api/[controller]")]
[ApiController]
public class DepartentQueryController : ControllerBase
{
  IDepartmentQueriesRepository _queryRepo;

  public DepartentQueryController( IDepartmentQueriesRepository queryRepo)
  {
             
     _queryRepo = queryRepo;
  }

  [HttpGet("{id}")]
  public async Task<IActionResult> GetAsync(int id) 
  {
     var response = await _queryRepo.GetByDeptNoAsync(id);
     return Ok(response);
  }

  [HttpGet]
  public async Task<IActionResult> GetAsync()
  {
     var response = await _queryRepo.GetDepartmentsAsync();
     return Ok(response);
  }
}

Listing 9: The Query Controller

In the Controllers folder add a new Empty API Controller and name it DepartmentCommandController. In this controller, we will inject IDepartmentCommandRepsitory using constructor injection. In this controller, we will add the HttpPost method to perform Command operations as shown in Listing 10


[Route("api/[controller]")]
[ApiController]
public class DepartmentCommandController : ControllerBase
{
  private readonly IDepartmentCommandRepository _commandRepo;

  public DepartmentCommandController(IDepartmentCommandRepository repository)
  {
      _commandRepo = repository;
  }

  [HttpPost]
  public async Task<IActionResult> PostAsync(Department dept)
  {
     var response = await _commandRepo.SaveDepartmentAsync(dept);
     return Ok(response);
  }
}

Listing 10:The Command Controller

Here we have added two separate controllers for performing Query and Command operations. The advantage of this approach is that based on the requirements for performing Read and Write operations end-users can call that specific controller. This increases the execution performance of the API and further says that if we want to add further logic for ReadWrite operations then that specific controller can be changed.         

Now we can run the project and test these APIs. The code for this article can be downloaded from this link.

Conclusion: When we plan to apply any pattern for implementing data operations using APIs or Microservices then the use of the CQRS pattern is recommended.  





 


                    

Popular posts from this blog

Uploading Excel File to ASP.NET Core 6 application to save data from Excel to SQL Server Database

-
Image
Recently, while conducting a session on ASP.NET Core 6, my client raised a query regarding uploading excel files to the ASP.NET Core MVC application and then further saving data from excel to the SQL Server Database table. I gave him the solution and then thought to write a post on it. As we all know, the Excel Workbook is one of the most heavily used files to save data. But to persist data it is recommended that it should be stored in an SQL Server database.  If the data is stored in an SQL Server database it can be queried easily.  Figure 1, explains an implementation of the application which we are going to discuss in the article. Figure 1: The ASP.NET Core That accepts Excel files and then further store data from Excel Files in SQL Server Database The   ExcelDataReader.DataSet Library To read excel files in ASP.NET Core applications, we need to use the ExcelDataReader.DataSet package. This is a lightweight and fast library for reading Excel files in .NET applications....
Read more

ASP.NET Core 6: Downloading Files from the Server

-
Image
In this article, we will implement the file downloading feature using ASP.NET Core 6 application. In real-world applications, we can have various files like reports, spreadsheets, images, PDF documents stored on the server. (Note that all cloud-hosted applications prefer to store these files on the storage services, e.g. Blob, S3, etc.). But what if the client prefers to store files on the separate file server mapped to the web application host server? Then, in that case, we may need to download these files from the file server. In this article, we will implement exactly the same thing.   Step 1: Open Visual Studio 2022 and create a new ASP.NET Core MVC Application targetted to .NET 6. Name this application as Core6_FileDownload. In this project add a new folder and name it as ServerFiles. In this file add some images. (You can add Excel, PDF, Word, Files). Step 2: Since we need to read the ServerFiles folder using the ASP.NET Core application, we need to add the IFi...
Read more

ASP.NET Core 6: Using Entity Framework Core with Oracle Database with Code-First Approach

-
Image
In this article, we will see how to use Oracle Database with Entity Framework Core with Code-First Approach. The reason behind writing a post on this is, recently while conducting training, one of my students raised the query regarding the EF Core with Oracle. Since Oracle is one of the most powerful databases, various enterprise applications are using it for the past several years and since the .NET Core is the most popular technology for building modern apps, the scenarios for accessing the Oracle database in .NET Core apps the use Entity Framework Core is common. Oracle Data Provider for .NET Entity Framework Core The Oracle Data Provider for .NET i.e. ODP.NET is used to access data from the Oracle database in the .NET Core apps using Entity Framework Core. This uses the Oracle.EntityFrameworkCore.dll, a managed library. This data provider allows using Code-First and Database-First approaches to perform Read/Write Operations with Oracle database.    Implementation To implem...
Read more