The series so far:<\/strong><\/p>\n Most developers are used to creating and handling CRUD (create, read, update, delete) systems for operations that divide tasks into updating and searching for data. When it comes to the problems this kind of well-known practice causes when considering the huge number of different frameworks, solutions, and infrastructural issues that arise every day, the life of those same developers can get tricky.<\/p>\n\n\n\n One of the main concerns regarding this is the increasing number of representations that your models can have when the application must serve more and more clients. Most of the time, the architectural design of the applications is made in the common CRUD-like way, that is, a single database (whether it is relational or not) that\u2019ll both store and serve as the querying center of all requirements.<\/p>\n\n\n\n That concern about aligning these worlds every time a new microservice is born, for example, ties the developer\u2019s hands to create each side in a more specialized manner when it comes to performance issues, object model complexities, resilience, and scalability independence, etc.<\/p>\n\n\n\n The term CQRS<\/strong>, (Command Query Responsibility Segregation) <\/em>is based on the famous CQS idea<\/a>. It is the closest architecture available to split these responsibilities to accomplish more and simplify the design. It\u2019s all about separating things, especially if they\u2019re very different conceptually when it comes to reading and updating models.<\/p>\n\n\n\n The pattern basically divides into two categories:<\/p>\n\n\n Figure 1 illustrates how this communication usually happens. Notice, specifically, the event sync in white that\u2019ll take the written model data and send it asynchronously to another module in order to save the same data to the materialized view (NoSQL database, in this case).<\/p>\n\n\n\n <\/p>\n\n\n\n Figure 1. CQRS architectural representation.<\/p>\n\n\n\n CQRS goes a bit further allowing you to have both worlds deployed and working structurally and physically separated from each other, even on different hardware, cloud, etc. There\u2019s a lot of flexibility here since you can define your own toolset, along with event-driven and message-driven architectures, DDD, etc. to make it more dynamic depending on your reality.<\/p>\n\n\n\n In this series, you\u2019ll learn how to create and gradually enhance one basic CQRS-like application, created upon ASP.NET Core. It consists of a Web API, a REST web service that will deal with customers data in a CRUD fashion, however, sending each of the commands to its respective handler. Then, you can decide where to manage the data flows: whether it is on the command or query\u2019s database\/datastore.<\/p>\n\n\n\n In this first part of the article, you\u2019ll get started with the project setups. The project relies on a REST-like application that handles customer information (basically user\u2019s data and a list of phones) to demonstrate how a single model lives within a CQRS structure.<\/p>\n\n\n\n The first thing you must install is the latest version of Visual Studio (the Community edition is good to go). For this article, Visual Studio 2017 is the chosen version, since it is the latest. The prerequisites consist of two workloads:<\/p>\n\n\n Also, make sure that the .NET Core SDK<\/a> (latest version) is properly installed on your machine.<\/p>\n\n\n\n If you have already installed VS, you can change the IDE setup<\/a> by launching the installer, running it again, and selecting the proper workloads.<\/p>\n\n\n\n Setting up SQLite<\/strong><\/p>\n\n\n\n SQLite is used as the relational database to store the customer’s data \u2013 the \u2018write\u2019 side of the project. First, go to the download page of DB Browser<\/a> (a famous GUI tool to manage the SQLite databases) and install it.<\/p>\n\n\n\n For the querying side of the model, we\u2019ll use MongoDB as the database. First of all, you need to download and install it<\/a> on your Windows machine. Follow the default installation steps and don\u2019t forget to keep the option that installs MongoDB Compass<\/a> (the default GUI for MongoDB) as well. Once it\u2019s finished, open Compass and create a new database with the Figure 2 configurations.<\/p>\n\n\n\n <\/p>\n\n\n\n Figure 2. Configuring MongoDB new database.<\/p>\n\n\n\n RabbitMQ is going to be the default message broker to the Event<\/em> processing side of the CQRS model. You can pick up another broker that you feel more comfortable with, just be sure to keep the same (similar) configurations. For this, go to the installation page<\/a>, and following the instructions to install and sets it up.<\/p>\n\n\n\n Additionally, don\u2019t forget to install the Erlang dependency for Windows<\/a>. Once the installer finishes the process, RabbitMQ will be a Windows service that starts up along with your machine, through its default configurations: guest<\/em> as both user and password. To access the administration page, just go to http:\/\/localhost:15672\/<\/a> in your browser (Figure 3).<\/p>\n\n\n\n <\/p>\n\n\n\n Figure 3. RabbitMQ administration page.<\/p>\n\n\n\n In case this page doesn\u2019t show up the first time, you can force the RabbitMQ Management plugin to restart. For this, go to the RabbitMQ installation \\sbin<\/em> folder and run the following command:<\/p>\n\n\n\n Figure 4 goes back step by step. Then, restart your computer and try to access the URL again.<\/p>\n\n\n\n <\/p>\n\n\n\n Figure 4. Enabling RabbitMQ Management plugin.<\/p>\n\n\n\n Create a new Web Application project by going to File > New > Project<\/em>. Then, select the template ASP.NET Core Web Application<\/em> and name it as CustomerApi<\/em> (Figure 5).<\/p>\n\n\n\n <\/p>\n\n\n\n Figure 5. Creating a new ASP.NET Core Web Application.<\/p>\n\n\n\n You\u2019ll be prompted to select a template for your Web Application, like that shown in Figure 6. Since the application just needs to expose endpoints for the tests, you can select the API<\/em> option and, if you wish to deploy your application on a local Docker container, you can select the Enable Docker Support<\/em>. Remember that this option will require you to have Docker previously installed<\/a> on your computer.<\/p>\n\n\n\n <\/p>\n\n\n\n Figure 6. Selecting the application template.<\/p>\n\n\n\n Note: You can download the completed project from the link at the bottom of the article.<\/em><\/p>\n<\/div>\n\n\n\n Next, you need to add the Entity Framework dependencies to your NuGet dependencies, specifically the one that refers to the SQLite integration. For this, go to menu View > Other Windows > Package Manager Console<\/em> and run the following commands:<\/p>\n\n\n\n Then, do the same to the Extensions.Configuration in order to enable the binding of our objects based on predefined key-value pairs; as well as the ASP.NET Core package:<\/p>\n\n\n\n Next, add the MongoDB C# Driver<\/a> dependency to your NuGet dependencies going again to View > Other Windows > Package Manager Console<\/em> and running the following command:<\/p>\n\n\n\n Finally, let\u2019s config the Web API project to have the RabbitMQ Client (once we need it to post and consume messages) installed as a NuGet dependency going once more to the Package Manager Console<\/em> and issuing the command:<\/p>\n\n\n\n At the end of all these setups, you\u2019ll have the same NuGet Dependencies <\/em>structure as shown in Figure 7.<\/p>\n\n\n\n <\/p>\n\n\n\n Figure 7. Final NuGet Dependencies tree.<\/p>\n\n\n\n Let\u2019s get started with the project configurations letting it know how to interpret the initial launching and app settings. By default, the files launchSettings.json<\/em> (which defines the rules for the IIS server, build, profiling, etc.) and appsettings.json<\/em> (which is responsible for defining the settings in app scope like database connections, amqp, etc.) are created to enable your project configuration customization.<\/p>\n\n\n\n Take a look at the following code listing, starting by the launchSettings.json<\/em> file found in the Properties<\/em> folder. Make a note of the original ports in your file. Change it according to this code and add the original HTTP port back:<\/p>\n\n\n\n Notice too that we have two IIS configurations in the file:<\/p>\n\n\n The second file, appsettings.json<\/em>, must be changed to the following:<\/p>\n\n\n\n Some configurations are familiar and come within the file when it is created by VS, like the Debug, Console and Logging settings. Here, we\u2019ve added a few others, like:<\/p>\n\n\n Plus, once you\u2019re running this example in a local development environment, make sure to copy the same properties to the file appsettings.Development.json<\/em>.<\/p>\n\n\n\n Once all the environment frameworks and tools are configured, let\u2019s move on to the model definitions. We\u2019ll start by creating the C# class of our Repositories<\/em> and Entities<\/em> since these integrations require some work regarding their complexities until we have all of them properly working along with the ASP.NET project.<\/p>\n\n\n\n Before going to the classes creation, you need to create the database file. SQLite was chosen as the CQRS relational database because it is serverless (unlike many other databases, it doesn\u2019t need the common client\/server architecture), that is, it works inside the applications, allowing the reads and writes directly from the file that represents the database itself. It\u2019s perfect for test and quality assurance environments, so we\u2019ll pick it up in order to evict complexity increasing.<\/p>\n\n\n\n So, first, open the DB Browser for SQLite<\/em>, click on New Database<\/em>, navigate to the root folder of the CustomerApi project and save this connection there with the name of customer_db.db<\/em>. Leave the database empty for now.<\/p>\n\n\n\n Get back to the project and create a new project folder called Models<\/em>. Then, another called SQLite <\/em>under Models<\/em>. This is to keep the files of each repository approach separated (since we\u2019re not creating an independent project for each solution). And finally, create three new files called CustomerRecord.cs, PhoneRecord.cs <\/em>and PhoneType.cs<\/em>. They must have the following contents:<\/p>\n\n\n\n CustomerRecord.cs<\/strong><\/p>\n\n\n\n PhoneRecord.cs<\/strong><\/p>\n\n\n\n PhoneType.cs<\/strong><\/p>\n\n\n\n The structure is lean: some simple attributes and a list phones represented by the second record entity. This one, specifically, needs to have the back-relation to the customer defined in order for the Entity Framework migration feature be able to understand and create the proper relationship between them.<\/p>\n\n\n\n The Entity Framework First Migration<\/a> is a feature that allows you to migrate the structure of your Entities models to the original database. Once you\u2019ve defined the model classes, Entity Framework will be able, through this feature, to read the entities structure metadata and execute the corresponding scripts to create the tables, columns, etc. in the final datastore.<\/p>\n\n\n\n To use this, go to the project root folder and edit the file CustomerApi.csproj<\/em>. Search for the tag <DotNetCliToolReference Include=”Microsoft.EntityFrameworkCore.Tools.DotNet” Version=”2.0.2″ \/><\/p>\n\n\n\n Restart your IDE. Now, you need to create the database context that\u2019ll command the rules of the migration. In the Models\/SQLite<\/em> folder, create a new class and name it as CustomerSQLiteDatabaseContext.cs<\/em>. Add the following content:<\/p>\n\n\n\n It basically defines the model builder and how it is going to consider the customer -> phones relation when generating the database structures. Here, it\u2019s important to correctly set up the one<\/em> and many<\/em> sides of the entity relationships, as well as the foreign keys.<\/p>\n\n\n\n Then, go again to the Models\/SQLite<\/em> folder and create the repository class, called CustomerSQLiteRepository.cs<\/em>. This one will be responsible for holding the context object and managing the operations of the SQLite database file:<\/p>\n\n\n\n Note that the methods are common CRUD operations performed onto the Customers<\/em> object of the context.<\/p>\n\n\n\n Finally, you must adjust the Startup.cs<\/em> file of the project to understand the new configs, by adding a new DBContext <\/em>as well as a repository transient to the Don\u2019t forget the proper using statements in the beginning of the file:<\/p>\n\n\n\n To guarantee that the database is in fact created, you can also add the following code to the Now, let\u2019s test the migrations feature. Open the command prompt, navigate to the project root folder and issue the following commands:<\/p>\n\n\n\n The first command will build the project, read the entity models and create the migration files to migrate the model. The second command triggers the commands to be executed directly into the database.<\/p>\n\n\n\n After that, open the DB Browser again for SQLite<\/em> and observe the tables generated automatically.<\/p>\n\n\n\n To give you a taste of how this API must operate the command side of the CQRS application, let\u2019s create the controller facade for the API to handle basic CRUD operations.<\/p>\n\n\n\n For this, in the \/Controllers<\/em> folder, rename the default generated controller class to CustomersController.cs<\/em> and change its content to the following:<\/p>\n\n\n\n Notice that the same endpoints (each HTTP method) are created based on the RESTful principles<\/a> to facilitate the pattern and make it more understandable for the clients to integrate.<\/p>\n\n\n\n\n
\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/09\/word-image-237.png\")
<\/figure>\n\n\n\nSetting up the environment<\/h2>\n\n\n\n
\n
Setting up MongoDB<\/h2>\n\n\n\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/09\/word-image-238.png\")
<\/figure>\n\n\n\nSetting up RabbitMQ<\/h2>\n\n\n\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/09\/word-image-239.png\")
<\/figure>\n\n\n\nrabbitmq-plugins.bat enable rabbitmq_management<\/pre>\n\n\n\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/09\/word-image-240.png\")
<\/figure>\n\n\n\nCreating the Project<\/h2>\n\n\n\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/09\/word-image-241.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/09\/word-image-242.png\")
<\/figure>\n\n\n\ninstall-package Microsoft.EntityFrameworkCore\ninstall-package Microsoft.EntityFrameworkCore.Sqlite\ninstall-package Microsoft.EntityFrameworkCore.Tools.DotNet<\/pre>\n\n\n\n
install-package Microsoft.Extensions.Configuration\ninstall-package Microsoft.Extensions.Configuration.Json\ninstall-package Microsoft.AspNetCore.All<\/pre>\n\n\n\n
install-package mongocsharpdriver<\/pre>\n\n\n\n
install-package RabbitMQ.Client<\/pre>\n\n\n\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/09\/word-image-243.png\")
<\/figure>\n\n\n\n{\n\t\"iisSettings\": {\n\t\t\"windowsAuthentication\": false,\n\t\t\"anonymousAuthentication\": true,\n\t\t\"iisExpress\": {\n\t\t\t\"applicationUrl\": \"http:\/\/localhost:58751\/\",\n\t\t\t\"sslPort\": 0\n\t\t}\n\t},\n\t\"buildOptions\": {\n\t\t\"emitEntryPoint\": true,\n\t\t\"preserveCompilationContext\": true,\n\t\t\"copyToOutput\": {\n\t\t\t\"include\": [ \"appsettings.json\" ]\n\t\t}\n\t},\n\t\"profiles\": {\n\t\t\"IIS Express\": {\n\t\t\t\"commandName\": \"IISExpress\",\n\t\t\t\"launchBrowser\": true,\n\t\t\t\"launchUrl\": \"api\/customers\",\n\t\t\t\"environmentVariables\": {\n\t\t\t\t\"ASPNETCORE_ENVIRONMENT\": \"Development\"\n\t\t\t}\n\t\t},\n\t\t\"CustomerApi\": {\n\t\t\t\"commandName\": \"Project\",\n\t\t\t\"launchBrowser\": true,\n\t\t\t\"launchUrl\": \"api\/customers\",\n\t\t\t\"environmentVariables\": {\n\t\t\t\t\"ASPNETCORE_ENVIRONMENT\": \"Development\"\n\t\t\t},\n\t\t\t\"applicationUrl\": \"http:\/\/localhost:58752\/\"\n\t\t}\n\t}\n}<\/pre>\n\n\n\n\n
{\n \"Logging\": {\n \"IncludeScopes\": false,\n \"LogLevel\": {\n \"Default\": \"Debug\",\n \"System\": \"Information\",\n \"Microsoft\": \"Information\"\n }\n },\n \"ConnectionStrings\": {\n \"DefaultConnection\": \"Filename=.\/customer_db.db\"\n },\n \"amqp\": {\n \"username\": \"guest\",\n \"password\": \"guest\",\n \"hostname\": \"localhost\",\n \"uri\": \"amqp:\/\/localhost:5672\/\",\n \"virtualhost\": \"\/\"\n }\n}<\/pre>\n\n\n\n\n
SQLite Integration<\/h2>\n\n\n\n
using System.Collections.Generic;\nnamespace CustomerApi.Models.SQLite\n{\n\tpublic class CustomerRecord\n {\n\t\tpublic long Id { get; set; }\n\t\tpublic string Email { get; set; }\n\t\tpublic string Name { get; set; }\n\t\tpublic int Age { get; set; }\n\t\tpublic List<PhoneRecord> Phones { get; set; }\n\t}\n}<\/pre>\n\n\n\nnamespace CustomerApi.Models.SQLite\n{\n\tpublic class PhoneRecord\n {\n\t\tpublic long Id { get; set; }\n\t\tpublic PhoneType Type { get; set; }\n\t\tpublic int AreaCode { get; set; }\n\t\tpublic int Number { get; set; }\n\t}\n}<\/pre>\n\n\n\nnamespace CustomerApi.Models.SQLite\n{\n\tpublic enum PhoneType\n\t{\n\t\t\tHOMEPHONE, CELLPHONE, WORKPHONE\n\t}\n}<\/pre>\n\n\n\n<ItemGroup><\/code> and add the following item to it:<\/p>\n\n\n\nusing Microsoft.EntityFrameworkCore;\nnamespace CustomerApi.Models.SQLite\n{\n\tpublic class CustomerSQLiteDatabaseContext : DbContext\n\t{\n\t\tpublic CustomerSQLiteDatabaseContext(DbContextOptions<CustomerSQLiteDatabaseContext> options)\n\t\t\t: base(options)\n\t\t{\n\t\t}\n\t\tprotected override void OnModelCreating(ModelBuilder modelBuilder)\n\t\t{\n\t\t\tmodelBuilder.Entity<CustomerRecord>()\n\t\t\t\t\t\t.HasMany(x => x.Phones);\n\t\t}\n\t\tpublic DbSet<CustomerRecord> Customers { get; set; }\n\t}\n}<\/pre>\n\n\n\nusing System.Collections.Generic;\nusing System.Linq;\nnamespace CustomerApi.Models.SQLite\n{\n\tpublic class CustomerSQLiteRepository\n\t{\n\t\tprivate readonly CustomerSQLiteDatabaseContext _context;\n\t\tpublic CustomerSQLiteRepository(CustomerSQLiteDatabaseContext context)\n\t\t{\n\t\t\t_context = context;\n\t\t}\n\t\tpublic CustomerRecord Create(CustomerRecord customer)\n\t\t{\n\t\t\tMicrosoft.EntityFrameworkCore.ChangeTracking.EntityEntry<CustomerRecord> entry = _context.Customers.Add(customer);\n\t\t\t_context.SaveChanges();\n\t\t\treturn entry.Entity;\n\t\t}\n\t\tpublic void Update(CustomerRecord customer)\n\t\t{\n\t\t\t_context.SaveChanges();\n\t\t}\n\t\tpublic void Remove(long id)\n\t\t{\n\t\t\t_context.Customers.Remove(GetById(id));\n\t\t\t_context.SaveChanges();\n\t\t}\n\t public IQueryable<CustomerRecord> GetAll()\n\t {\n return _context.Customers;\n }\n\t\tpublic CustomerRecord GetById(long id)\n\t\t{\n\t\t\treturn _context.Customers.Find(id);\n\t\t}\n\t}\n}<\/pre>\n\n\n\nConfigureServices<\/code> method:<\/p>\n\n\n\nservices.AddDbContext<CustomerSQLiteDatabaseContext>(options =>\t\toptions.UseSqlite(Configuration.GetConnectionString(\"DefaultConnection\")));\nservices.AddTransient<CustomerSQLiteRepository>();<\/pre>\n\n\n\n
using CustomerApi.Models.SQLite;\nusing Microsoft.EntityFrameworkCore;<\/pre>\n\n\n\n
Configure<\/code> method:<\/p>\n\n\n\nusing (var serviceScope = app.ApplicationServices.GetService<IServiceScopeFactory>().CreateScope())\n\t\t\t{\n\tvar context = serviceScope.ServiceProvider.GetRequiredService<CustomerSQLiteDatabaseContext>();\n\tcontext.Database.EnsureCreated();\n}<\/pre>\n\n\n\ndotnet ef migrations add CreateDatabase\ndotnet ef database update<\/pre>\n\n\n\n
First Hints with the API<\/h2>\n\n\n\n
using CustomerApi.Models.SQLite;\nusing Microsoft.AspNetCore.Mvc;\nnamespace CustomerApi.Controllers\n{\n [Route(\"api\/[controller]\")]\n\tpublic class CustomersController : Controller\n\t{\n\t\tprivate readonly CustomerSQLiteRepository _sqliteRepository;\n \tpublic CustomersController(CustomerSQLiteRepository sqliteRepository)\n\t\t{\n\t\t\t_sqliteRepository = sqliteRepository;\n\t\t}\n\t\t[HttpGet]\n\t\tpublic IActionResult GetAll()\n\t\t{\n\t\t var customers = _sqliteRepository.GetAll();\n\t\t if (customers == null)\n\t\t {\n \t\treturn NotFound();\n \t }\n\t\t return new ObjectResult(customers);\n\t\t}\n\t\t[HttpGet(\"{id}\", Name = \"GetCustomer\")]\n\t\tpublic IActionResult GetById(long id)\n\t\t{\n\t\t\tvar customer = _sqliteRepository.GetById(id);\n\t\t\tif (customer == null)\n\t\t\t{\n\t\t\t\treturn NotFound();\n\t\t\t}\n\t\t\treturn new ObjectResult(customer);\n\t\t}\n\t\t[HttpPost]\n\t\tpublic IActionResult Post([FromBody] CustomerRecord customer)\n\t\t{\n CustomerRecord created = _sqliteRepository.Create(customer);\n\t\t\treturn CreatedAtRoute(\"GetCustomer\", new { id = created.Id }, created);\n\t\t}\n\t\t[HttpPut(\"{id}\")]\n\t\tpublic IActionResult Put(long id, [FromBody] CustomerRecord customer)\n\t\t{\n\t\t\tvar record = _sqliteRepository.GetById(id);\n\t\t\tif (record == null)\n\t\t\t{\n\t\t\t\treturn NotFound();\n\t\t\t}\n\t\t\tcustomer.Id = id;\n _sqliteRepository.Update(customer);\n\t\t\treturn NoContent();\n\t\t}\n\t\t[HttpDelete(\"{id}\")]\n\t\tpublic IActionResult Delete(long id)\n\t\t{\n\t\t\tvar record = _sqliteRepository.GetById(id);\n\t\t\tif (record == null)\n\t\t\t{\n\t\t\t\treturn NotFound();\n\t\t\t}\n _sqliteRepository.Remove(id);\n\t\t\treturn NoContent();\n\t\t}\n\t}\n}<\/pre>\n\n\n\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/09\/word-image-244.png\")
<\/figure>\n\n\n\n