Dapper & CQRS

Introduction

In 2016 the company I work for launched a major rewrite of our main software suite. We are an education company and so the software included everything that a school administration or teacher would need from grade book, attendance, curriculum, assessment, behavior, scheduling. It was really a pretty big success. Teachers and administrative officials seemed to enjoy the software and see it as a large step forward. We were all very proud. In time though, the shine wore off. The common complaints we began to get were that user interfaces were confusing, buggy and hard to navigate and that the system was slow.

As we began to investigate the slowness we noticed that data calls to the various apis were quite slow. Sometime in late 2018 we began to look into ways to speed up the data calls to our api. This was about the time that .Net Core 2.0 had come out, but we were currently using .Net 4.5.3. So instead of reworking currently existing APIs we decided to start from scratch with a new API layer written completely in .Net Core.

I know... I know... We can debate the rightness or wrongness of this decision to start from scratch. To save us all time lets just move onto what we ended up doing with the new stack.

The "legacy stack" as it came to be known was written in .Net 4.5.3 using Entity Framework and the classic repository / unit of work pattern. As we worked with Entity Framework Core in .Net Core we found that we were not improving our query speeds. They were still slow. Usually in the area of 250ms on up based on the query. Entity Framework wasn't going to cut it for this "new stack" code. We decided to try Dapper. Along with Dapper we decided to adopt a different pattern with how our back end code would be structured and data would be delivered. After reading quite a bit about CQRS (Command Query Responsibility Segregation) and finding some great examples online we settled on this pattern. This article in particular was very useful. Though we didn't follow it exactly, we stole quite a few ideas from it.

The rest of this article is a walk through an example of how we setup our API code now and the patterns we follow.

Code

You can get all the source code for this example at https://github.com/MelodicDevelopment/example-dotnet-api-cqrs. We can debate the organization of the code another time. I was trying to get an example up pretty quick. This is however pretty close to what we follow on my team.

If you run the API project you will be brought to a swagger page that documents the simple API I created for this example.

Contracts

The contracts project contains all the interfaces and DTOs for the solution. The example DTOs are Author and Book. Since we decided to follow the CQRS pattern our database queries and commands are separated into separate classes. Queries are based on the IQuery interface.

dotnet-api-cqrs.contracts/data/IQuery.cs

using System.Data;

namespace dotnet_api_cqrs.contracts.data
{
    public interface IQuery<T>
    {
        string Sql { get; set; }

        T Execute(IDbContext context, IDbTransaction transaction = null);
    }
}

Commands are based upon the ICommand interface. ICommand is a simple extension of the IQuery interface with a generic type of int since all commands return an integer.

dotnet-api-cqrs.contracts/data/ICommand.cs

namespace dotnet_api_cqrs.contracts.data
{
    public interface ICommand : IQuery<int> { }
}

The IDbContext is our database connection class that will execute our queries and commands. The implementation is in the data project so we'll talk more about that later.

dotnet-api-cqrs.contracts/data/IDbContext.cs

using System;
using System.Collections.Generic;
using System.Data;

namespace dotnet_api_cqrs.contracts.data
{
    public interface IDbContext : IDisposable
    {
        T QueryFirst<T>(string query, object param = null, CommandType commandType = CommandType.Text, IDbTransaction transaction = null);
        IEnumerable<T> Query<T>(string query, object param = null, CommandType commandType = CommandType.Text, IDbTransaction transaction = null);
        int InsertSingle(string sql, object param = null, CommandType commandType = CommandType.Text, IDbTransaction transaction = null, int? timeout = null);
        int Command(string sql, object param = null, CommandType commandType = CommandType.Text, IDbTransaction transaction = null, int? timeout = null);
        T Transaction<T>(Func<IDbTransaction, T> query);
        void Transaction(Action<IDbTransaction> query);
    }
}

You're going to notice some interfaces that include the name facade as well. Each facade inteface defines methods that return Func or Action delegates. More on these when we talk about the data project.

Data: Commands & Queries

The data project is where you're going to see most of the organization around our data access layer. The first two folders are for commands and queries, and inside each folder is a sub folder for each DTO. Inside the DTO folder you will see query classes. Each query is named for what it does. For example:

• GetAllBooksQuery
• GetBookQuery

It's not hard to determine what these queries do. Let's look at the GetAllBooksQuery.

dotnet-api-cqrs.data/Queries/Book/GetAllBooksQuery.cs

using System.Collections.Generic;
using System.Data;
using dotnet_api_cqrs.contracts.data;
using D = dotnet_api_cqrs.contracts.dto;

namespace dotnet_api_cqrs.data.Queries.Book
{
    public class GetAllBooksQuery : IQuery<IEnumerable<D.Book>>
    {
        public string Sql { get; set; }

        public GetAllBooksQuery()
        {
            Sql = @$"
SELECT              BookID,
                    Title,
                    CopyRightYear,
                    AuthorID
FROM                dbo.Books
ORDER BY            Title";
        }

        public IEnumerable<D.Book> Execute(IDbContext context, IDbTransaction transaction = null)
        {
            return context.Query<D.Book>(Sql, transaction: transaction);
        }
    }
}

The GetAllBooksQuery class implements the IQuery> interface. When we want to execute it we pass in the IDbContext to the Execute method. Pretty straightforward. Now lets look at the InsertBookCommand class.

dotnet-api-cqrs.data/Commands/Book/InsertBookCommand.cs

using System.Data;
using dotnet_api_cqrs.contracts.data;
using D = dotnet_api_cqrs.contracts.dto;

namespace dotnet_api_cqrs.data.Commands.Book
{
    public class InsertBookCommand : ICommand
    {
        private readonly D.Book _book;

        public string Sql { get; set; }

        public InsertBookCommand(D.Book book)
        {
            _book = book;

            Sql = $@"
INSERT INTO         dbo.Books
                    (Title, CopyRightYear, AuthorID)
VALUES              (@Title, @CopyRightYear, AuthorID);";
        }

        public int Execute(IDbContext context, IDbTransaction transaction = null)
        {
            var param = new {
                Title = _book.Title,
                CopyRightYear = _book.CopyRightYear,
                AuthorID = _book.AuthorID
            };

            return context.InsertSingle(Sql, param, transaction: transaction);
        }
    }
}

This class will take in the book parameter, setup the sql parameters, and then execute them with the IDbContext. The InsertSingle method will actually return the new Book and return it's new ID.

Data: Facades

When we originally started developing with this pattern we began using the queries and commands directly in our services without setting them up for injection. We simply did new GetAllBooksQuery() right in the code in the services. This proved hard to test. So to solve this problem we decided to use facades. Facades simply group together related queries and commands for a DTO or logical group of DTOs. Another challenge we wanted to try and solve between the services and the facades was the ability to use read / write database contexts. So we adopted the pattern of always returning a function delegate from the facade methods. To make this easier we created a Facade base class with some methods to use in the child classes. If we want to return a value from the query or command we use the Func delegate. If we don't need a return value from the query or command we use the Action delegate. Each function delegate takes in an IDbContext and a possible IDbTransaction. This allows us to pass in possibly different contexts to execute the query or command. Passing in a transcaction allows us to perform multiple database actions in the same transaction. The DbContext is setup to run every query and command within a transaction. You can also use the Transaction method in the DbContext to create your own transaction to perform multiple database actions.

dotnet-api-cqrs.data/Facade.cs

using System;
using System.Data;
using dotnet_api_cqrs.contracts.data;

namespace dotnet_api_cqrs.data
{
    public class Facade
    {
        protected Func<IDbContext, IDbTransaction, T> Prepare<T>(IQuery<T> queryCommand)
        {
            return (context, transaction) => {
                return queryCommand.Execute(context, transaction);
            };
        }

        protected Action<IDbContext, IDbTransaction> Prepare(ICommand command)
        {
            return (context, transaction) => {
                command.Execute(context, transaction);
            };
        }
    }
}

The BookFacade is a straightforward example of what a facade looks like in practice. Again, facades don't contain any business logic. They simply allow us to group queries and commands related to a DTO or logical grouping of DTOs.

using System;
using System.Collections.Generic;
using System.Data;
using dotnet_api_cqrs.contracts.data;
using dotnet_api_cqrs.contracts.dto;
using dotnet_api_cqrs.data.Commands.Book;
using dotnet_api_cqrs.data.Queries.Book;

namespace dotnet_api_cqrs.data
{
    public class BookFacade : Facade, IBookFacade
    {
        public Func<IDbContext, IDbTransaction, IEnumerable<Book>> GetBooks()
        {
            return Prepare(new GetAllBooksQuery());
        }

        public Func<IDbContext, IDbTransaction, Book> GetBook(int bookID)
        {
            return Prepare(new GetBookQuery(bookID));
        }

        public Func<IDbContext, IDbTransaction, IEnumerable<Book>> GetBooksForAuthor(int authorID)
        {
            return Prepare(new GetBooksForAuthorQuery(authorID));
        }

        public Func<IDbContext, IDbTransaction, int> InsertBook(Book book)
        {
            return Prepare<int>(new InsertBookCommand(book));
        }

        public Action<IDbContext, IDbTransaction> DeleteBook(int bookID)
        {
            return Prepare(new DeleteBookCommand(bookID));
        }
    }
}c#

Services

The services project is where we put any business logic that needs to happen. One thing we found when we went with Dapper and CQRS was that a lot of business logic was taken care of by the fact that we were retrieving exactly what we wanted from the database. We didn't have an entity to represent each table in the database and a repository to work with that entity. We only took the columns we wanted and we could do simple table joins to create our business models exactly as we wanted them without all the boilerplate of Entity Framework and the repository pattern. Our actual code has methods to execute stored procedures and return multiple datasets, but I didn't include that here for simplicity's sake. So in the end our services look pretty trim usually, but they do serve as the place where business logic happens. The services all extend a base Service class that can take in multiple database contexts for reading and writing. We inject our facades here into the services. For this particular example I'm only using one context that does all reading and writing.

dotnet-api-cqrs.services/BookService.cs

using System.Collections.Generic;
using dotnet_api_cqrs.contracts.data;
using dotnet_api_cqrs.contracts.dto;
using dotnet_api_cqrs.contracts.services;

namespace dotnet_api_cqrs.services
{
    public class BookService : Service, IBookService
    {
        private readonly IBookFacade _bookFacade;

        public BookService(IDbContext context, IBookFacade bookFacade) : base(context)
        {
            _bookFacade = bookFacade;
        }

        public IEnumerable<Book> GetAllBooks()
        {
            return _bookFacade.GetBooks()(Context, null);
        }

        public Book GetBook(int bookID)
        {
            return _bookFacade.GetBook(bookID)(Context, null);
        }

        public IEnumerable<Book> GetBooksForAuthor(int authorID)
        {
            return _bookFacade.GetBooksForAuthor(authorID)(Context, null);
        }

        public Book InsertBook(Book book)
        {
            var newBookID = _bookFacade.InsertBook(book)(Context, null);
            return _bookFacade.GetBook(newBookID)(Context, null);
        }

        public void DeleteBook(int bookID)
        {
            _bookFacade.DeleteBook(bookID)(Context, null);
        }

        public Book ReplaceBook(Book oldBook, Book newBook)
        {
            return Context.Transaction<Book>(_transaction => {
                _bookFacade.DeleteBook(oldBook.BookID)(Context, _transaction);

                var newBookID = _bookFacade.InsertBook(newBook)(Context, _transaction);
                return _bookFacade.GetBook(newBookID)(Context, _transaction);
            });
        }
    }
}

Injection

I'm not going super deep here. The data and services projects each contain a static class that sets up the facades and services for injection. These classes are referenced in the Startup.cs file of the api to setup all the necessary injection.

API

The api has two controllers with endpoints for doing some simple CRUD methods on books and authors.

dotnet-api-cqrs.api/Controllers/BookController.cs

using System.Collections.Generic;
using dotnet_api_cqrs.contracts.dto;
using dotnet_api_cqrs.contracts.services;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;

namespace dotnet_api_cqrs.api.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class BookController : ControllerBase
    {
        private readonly ILogger<BookController> _logger;
        private readonly IBookService _bookService;

        public BookController(ILogger<BookController> logger, IBookService bookService)
        {
            _logger = logger;
            _bookService = bookService;
        }

        [HttpGet]
        public IEnumerable<Book> Get()
        {
            return _bookService.GetAllBooks();
        }

        [HttpGet("{bookID:int}")]
        public Book Get(int bookID)
        {
            return _bookService.GetBook(bookID);
        }

        [HttpGet("author/{authorID:int}")]
        public IEnumerable<Book> GetForAuthor(int authorID)
        {
            return _bookService.GetBooksForAuthor(authorID);
        }

        [HttpPost]
        public Book Post(Book book)
        {
            return _bookService.InsertBook(book);
        }

        [HttpDelete("{bookID:int}")]
        public IActionResult Delete(int bookID)
        {
            _bookService.DeleteBook(bookID);
            return Ok();
        }
    }
}

Benefits

• The performance gains we saw with this approach were impressive. Where we were seeing return times of >250ms, we were able to write queries that were returning in <50ms.
• No more crazy Entity Framework / OData queries that got hobbled together by .Net and killed performance across the board. We could work with our database team to write highly optimized queries that were extremely performant.
• This approach greatly simplied testing and increased the speed of development because we didn't have as much boilerplate.
• Being a sAFE team, we are big on the T-shaped developer. This approach allowed our database engineers the ability to easily understand our queries and get into the code to make modifications themselves where it use to be a "no database engineers" zone.

There was more benefits, but we'll leave it here for now. This article is getting a bit long.

So that's the basic setup for how our APIs are configured now. I'd love to get your thoughts / questions / criticisms. Be gentle. This is my first technical blog post. More to come though.

Comments

[Dave Suico]

Great work!

However, I'm wondering why do queries have to be wrapped in a facade and then in a service? See I've been writing an app using a project template they called Clean Architecture recently and then I learned that queries and commands are called in controllers directly and no more wrappers around. Your thoughts on this matter is greatly appreciated!

[Rick Hopkins]

Thought I had responded to this, but must have forgot. So we wrap them in a facades for a few reasons. First, we find it useful to group like queries and commands related to either a business area or data entity. Second, for testing we find it easier to mock up without having to directly new up a query. Third, we use separate database contexts for read and write to our database. In our services we often create complex transactions to perform multiple operations all in one transaction against the same database context. Also, related to the second reason, when doing these bigger transactions calling multiple queries or commands its nice to have them all available in one place instead of having to bring in each query separately. Facades probably aren't necessary, but we've found benefits to having them in regard to the reasons I've listed. There are probably more benefits I'm not thinking of right at the moment. A draw back is that it is just one more piece in the pipeline to create and remember to piece together. We also have debates on our team all the time about what queries and commands should be grouped together into a facade and what makes sense. Hope this helps.