.NET Core C# Entity Framework Core Patterns

CQRS with Entity Framework Core

Over the past decade, CQRS has become more popular and implementing it with Entity Framework Core makes it easy. We’re going to start off by discussing what CQRS is, different ways to implement it, and why Entity Framework Core is ideally suited to it. We’ll finish with examples of command and query implementations.

What is CQRS?

CQRS stands for Command Query Responsibility Segregation. At its heart, CQRS is the separation between commands and queries, specifically the model. The idea is that using a single unified model to handle both commands and queries results in an over complicated model. As the model tries to handle both, it becomes unable to handle either well and the model becomes increasingly complex.

Queries usually don’t use all the properties of a model anyway. This leads often to the creation of DTOs to handle specific queries, which in turn leads to mapping. I’ve written at length on this approach and my opinion of it here. While you can’t always avoid writing DTOs for queries, I would argue that more often than not they are not necessary. Which brings us to the main drawback of CQRS: by creating two sets of models we can actually end up increasing the complexity of the application rather than decreasing it. It is imperative therefore that we avoid creating DTO classes for our queries where possible, but how?

Data Project Organization

There are multiple ways you can organize your queries and commands. In all cases I would still recommend the approach of organizing everything based on the aggregate root just as you would if you were doing a standard repository pattern. My personal preference right now is to use extension methods on the aggregate root DbSet which I feel is more natural when working with EF Core and lends itself to a more functional paradigm. It also has the following additional benefits:

  • Reduces the amount of injected objects into controllers or request handlers.
  • Rather than injecting a database context into multiple command classes and ending up with multiple contexts, this allows us to work with a single context and thus a single transaction if needed. Alternatively you could pass the database context as a parameter, but that is less than ideal.
  • Keeps the entry point for all database related functions to a single object.

I place my queries and commands in separate folders in the data project. Below is a picture of the typical structure that I use:

CQRS
Data Project Structure

I highly recommend placing your configuration for entities in separate class files to keep the size of the database context down. You can see a folder for projections; that’s because it’s usually a good idea to create reusable ones. This is especially true for larger objects as the projections tend to get used over and over again in different queries. Keeping them in separate classes also has the added benefit of reducing the size of your extension classes. We’ll talk about projections next.

Entity Framework Core Projections

There is no rule that says you must map your queries to a class. In fact, if you want to pull out the full performance of Entity Framework Core, you need to avoid it wherever possible as I have written about here. In our implementation of CQRS with EF Core, we’re going to write our queries as projections to anonymous types and return dynamic. This will allow us to skip the mapping in EF Core and pull out the full performance.

Let’s start off by writing our projection. In this example case we are going to get an employee without the list of accompanying documents with it.

public static class EmployeeProjection
{
    public static Expression<Func<Employee, dynamic>>EmployeeWithoutDocuments
    {
        get
        {
            return m => new
            {
                m.Name,
                m.Company,
                m.Address,
                m.EmployeeId,
                m.Id,
                m.DepartmentId,
                m.Department,
                m.CompanyId
            };
        }
    }
}

The code is fairly simple. We are constructing a expression based on an Employee type and returning an anonymous type. This will keep our Entity Framework Core queries from doing performance killing mapping and prevent us from having to define a specific DTO. You’ll notice in my case that I am placing the projections in their own folder within the data project. A second approach to organizing this is to place these static expressions in your domain classes. My personal preference here is the data project since I feel that these are more a data implementation concern than a modeling concern. Which one you choose will probably not affect your outcome that much, so feel free to go with whatever organization you feel most comfortable with.

Queries in CQRS

As I mentioned earlier, my preference when doing CQRS with Entity Framework Core is to create extension methods on the root aggregate DbSet. In our example this is going to be the Employee DbSet. There are two approaches to designing your queries: build more generic queries that take projections as parameters and building specifically named queries that write the projection directly into the query itself. On that note, you can also pass in expressions for your where, select, and more if you want to make even more generic queries. Below is what the query would look like if you went with specifically named queries:

public static class EmployeeQuery
{
    public static async Task<dynamic> GetEmployeeByIdWithoutDocumentsAsync(this DbSet<Employee> employeeDbSet, int employeeId)
    {
        return await employeeDbSet.Where(m => m.Id == employeeId).Select(EmployeeProjection.EmployeeWithoutDocuments).FirstOrDefaultAsync();
    }
}

As you can see, placing the projection outside of the query class will significantly cut down on the size of the class over time. In order to express what this query does we end up with a longer but more descriptive name for the query. The down side to this is that we end up with a new query every time the projection changes. Here’s what it would look like if we passed it in as a paramter:

public static class EmployeeQuery
{
    public static async Task<dynamic> GetEmployeeByIdAsync(this DbSet<Employee> employeeDbSet, Expression<Func<Employee, dynamic>>projection, int employeeId)
    {
        return await employeeDbSet.Where(m => m.Id == employeeId).Select(projection).FirstOrDefaultAsync();
    }
}

More generic and thus less methods needed, but less descriptive. Which approach you go with is more of a personal preference and judgement decision. I don’t think anyone can argue that there is necessarily a wrong way of doing this. You can then use the query just like you would any other extension method:

var employee = await dbContext.Employee.GetEmployeeByIdAsync(EmployeeProjection.EmployeeWithoutDocuments, 300);

As an alternative to Entity Framework Core you could use a hybrid approach by using Dapper. Dapper is a micro ORM that will offer some modest performance increases depending on the query at the cost of needing to either write stored procedures or write your queries as SQL within the code. You can learn more about Dapper here.

Commands in CQRS

Commands in CQRS are set up just like queries. We create an extension method on the DbSet and call it just like we would the previous query. Our domain models will serve as the model for commands. You will find some people argue that your domain models are not your persistence models and therefore must be kept separate like this blog post.
This is true, but there is theory and purity, and then there is practice.

The vast majority of the time your data will line up with your domain. Building a bunch of DTOs that are nothing more than reflections is only going to complicate your command model needlessly. In instances where the data does not match up with a domain model then by all means create a DTO. Mass creating DTOs though based only on the concept of purity is going to make your life, and the lives of everyone around you, miserable. In the cases where a DTO makes sense, do everyone a favor and skip the horrible DTO suffix. Use DataModel or some other descriptive suffix that tells me immediately what the purpose of this DTO is. Below is an example of our command class for Employee:

public static class EmployeeCommand
{
    public static async Task<dynamic> UpdateEmployee(this DbSet<Employee> employeeDbSet, Employee employee)
    {
        var existingEmployee = await employeeDbSet.FirstOrDefaultAsync(m => m.Id == employee.Id);
        existingEmployee.Name = new PersonName(employee.Name.FirstName, employee.Name.LastName);
        existingEmployee.Address = new Address(employee.Address.StreetAddress, employee.Address.City, employee.Address.State, employee.Address.ZipCode);
        existingEmployee.DateOfBirth = employee.DateOfBirth;
    }
}

You’ll notice that we are creating new objects on Address and PersonName. The reason for this is that they are value objects and thus are immutable. You can read more about this here. Call this just like you would the query and then call SaveChangesAsync to commit the transaction. You can find the example code for this post here.

Author

Sean Leitzinger

Comments (25)

  1. C. Baptiste
    April 18, 2019

    Enjoyed the article on CQRS. Can you expand into Event Store with EF?

  2. Sean Leitzinger
    April 18, 2019

    Sure. I’ll work on a blog post on that next.

  3. Oded.R
    May 12, 2019

    Sean great article,
    Can you expand what you gain by creating a dynamic object within a static class,
    Rather than creating a separate DTO.
    In both cases you need to write the fields you want to use.
    As I see things, by adding DTO object there is no effect on memory\maintenance\performance.

    • Sean Leitzinger
      May 13, 2019

      Hi Oded, thanks for reading, and sorry for the late reply. There are a couple of differences. First, you’ll notice that my projection is not mapping to a strongly typed DTO, but instead it is dynamic. Using anonymous types is roughly twice as performant from my tests. This becomes more apparent the larger the data set and the more complex the object. It might make a good blog post to benchmark this and give concrete examples.

      The second thing is that every time you need to map to something when using DTOs you either have to create a new class and define all the properties, or you have tack on properties to an existing DTO. This exponentially increases the amount of classes and thus decreases maintainability. The other issue is that the more complex your objects are with nested children, the more difficult it becomes to keep your DTOs fine grained. If you decide to flatten your DTOs then that introduces its own issues.

  4. Mike
    July 14, 2020

    While a JS-like approach using dynamic might sound flexible, don’t you think it’s a problem not having a strong type? I worked on a project using dynamic and a misspelling caused a bug which was a frustrating to find.

    • Sean Leitzinger
      July 14, 2020

      Only the returned type is dynamic, so when writing your queries you are still working against the strongly typed DbSet. If you are going to modify that dynamic value, then you may want to think about either using a DTO, or modifying it client side into what you need. That being said, there shouldn’t be too many instances where you would need to modify your query after getting it. There’s usually a number of different options available to you, but it really depends on your use case. In general, returning dynamic and directly returning it from an API is going to cover most use cases.

      • Mike
        July 15, 2020

        Hi Sean,

        Do you have a concrete example of what you mean ?

        Even if you don’t modify the dynamic value, you definitely need to read values from it, which means the code is not strongly typed and it can easily become a nightmare to maintain or update.

        • Sean Leitzinger
          July 15, 2020

          I am not sure I’m following what you mean here. If I write something like return new OkObjectResult(await dbContext.Employee.GetEmployeeByIdAsync(EmployeeProjection.EmployeeWithoutDocuments, 300)); I’m going to get my dynamic object mapped directly to JSON from my web API endpoint. Most apps these days are a front end like Angular hooked up to an API. There is no need to read any values from your query manually unless you are modifying the returned query before passing it back, or if you are more of a classic MVC set up where you are using view models.

          In the case of MVC, then use something like Automapper to map to your view model since it can map dynamic objects. If you are needing to modify the values before returning it, then I think that depends on your use case. Assuming that you can’t manipulate your data to what you want either client side or in the query itself, then you may want to opt for a DTO here. The main idea is that in the majority of cases you are not going to need to use a DTO and can just take your custom query and pass it back as JSON directly without needing a strongly typed query object to represent your data structure.

  5. Rob
    July 15, 2020

    Separating the projections is indeed helpful. Makes sense.

    The dynamic approach won’t work when you use auto generated clients through nswag I guess. This could be a reason to go for the strong typed dtos.

    • Sean Leitzinger
      July 15, 2020

      Possibly, yes. If you wanted an auto generated client you would need to work something in yourself with the library you are using.

      • Steve R
        December 8, 2020

        Can you not use generics to remove the dynamic returned type?

  6. Evandro
    July 19, 2020

    This approach makes WAY more sense than implementing a repository pattern/unity of work when working with EF.

  7. Patrick
    July 23, 2020

    The examples in this article do not compile and, in my opinion, lacks clear strong typed design. You’re trading strongly typed checks for `dynamic` because of what I perceive to be laziness.

    You’re also breaking your own rules by seemingly including entire entities in the projections that should be favoured. You’re projecting an employee, but then not projecting the company.

    To fix this I would do the following. I did not fix the projections for what seem to be navigational properties, as I could not make that assumption without more context.

    “`cs
    public class EmployeeWithoutDocumentsDto
    {
    public string Name { get; set; }
    public Company Company { get; set; }
    public string Address { get; set; }
    public int EmployeeId { get; set; }
    public int Id { get; set; }
    public int DepartmentId { get; set; }
    public Department Department { get; set; }
    public int CompanyId { get; set; }
    }

    public static class EmployeeProjection
    {
    public static Expression<Func> EmployeeWithoutDocuments
    {
    get
    {
    return x => new EmployeeWithoutDocumentsDto
    {
    Name = x.Name,
    Company = x.Company,
    Address = x.Address,
    EmployeeId = x.EmployeeId,
    Id = x.Id,
    DepartmentId = x.DepartmentId,
    Department = x.Department,
    CompanyId = x.CompanyId
    };
    }
    }
    }
    “`

    And then fix the returning type not to be void (or, more specficially, `Task`).

    “`cs
    public static async Task GetEmployeeByIdWithoutDocumentsAsync(this DbSet employeeDbSet, int employeeId)
    {
    return await employeeDbSet.Where(m => m.Id == employeeId).Select(EmployeeProjection.EmployeeWithoutDocuments).FirstOrDefaultAsync();
    }
    “`

    Writing against a DTO is more safe for refactoring and context. I would also heavily favour to not make these extension methods – this is why services/repositories exist.

  8. Luru
    July 25, 2020

    IMHO you’ll have a lot of problems with dynamics if you need to expose these queries through and API using Swagger for example, because of the lack of the strong typed checks will cause problems.

    • Sean Leitzinger
      July 25, 2020

      Possibly, but Swagger gives you a lot of control over what it generates. At that point, it’s just matter of hooking it into the set up and generating what you want. From an ease of use, maintainability, and just general quality of life perspective, not having to mess with DTOs and mapping is worth any extra effort to generate something in swagger in my opinion.

  9. john
    August 20, 2020

    Did you ever follow up with event sourcing?

    • Sean Leitzinger
      August 21, 2020

      I ended up not going through with it. The time investment was significant and this article would have just been one small piece of the end to end solution. The query side would probably not have ended up using SQL Server which would make a lot of the setup in the article not relevant. My main purpose of this article was to show you can do CQRS without creating a ton of DTOs, and without necessarily splitting the command database from the query database. It was also to show you don’t have to do event sourcing in order to gain benefits from this approach. Ultimately I wanted to steer people away from the “repository” pattern that is so prevalent and the sheer amount of DTO mapping that people tend to reach for. I may revisit it if I get more time and if Event Driven Design continues to gain popularity and maturity.

  10. riz p
    October 24, 2020

    I am doing something similar for my entities – i am creating query classes. However, I have recently run into an issue as follows: Imagine I have a Vehicle class and a Make class with one Make to many Vehicles. Now this Vehicle itself is part of many other entities, and for each of them, I have a query class which has some reporting queries. In addition, I also have some views for user to select Vehicle dropdowns which also have queries to fetch this.

    Whenever we display Vehicle Name, we always have to show “Make.Name – Vehicle.Name”. So all of the queries had this mechanism when selecting the data. However, now the requirement has changed to have it so “Make.Name : Vehicle.Name” which makes it difficult because this is in so many places. How can avoid making this mistake and how to best centralize this logic?

    • Sean Leitzinger
      October 25, 2020

      If I’m understanding correctly, you are actually formatting your display string as part of your EF Core query. You ended up with many similar queries who used this format. There are two options here if this is the case:

      1. Outsource the formatting of the string to a helper function so that if the format changes you change it only in the helper function.

      2. Treat the formatting of the string as a presentation concern and create some sort of drop down label function that formats this. This is dependent on what front end framework you’re using.

      Either way, the ultimate goal is to do the formatting in one place with a single method that takes a Make.Name and a Vehicle.Name and returns you a formatted string.

  11. Aleksey
    November 11, 2020

    Hi, great article. But i have one practical question. What if i have that class:
    public class Person
    {
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int Age { get; set; }
    public ICollection Addresses { get; set; }
    public ICollection EmailAddresses { get; set; }

    }
    and i want to select Peoples which have equal Street in any Addresses and Email in any EmailAddresses (for example).
    Using navigation properties EF generates crazy SQL. So i think i need more than one DbSet for that and use join.
    So… how i can organize that? Should i extend DbContext? Or maybe send additional DbSets to extension… Or maybe some UOW and write extensions for it …
    I sure you have many situations where you need more than one DbSet.

  12. carlos
    November 29, 2020

    How could you make a JOIN between DbSet if you don’t have access to the Context inside the extension Method?

  13. FNI
    February 9, 2021

    Either I am missing something, but the examples do not work.
    – There is no non generic version of DbSet like in public static async Task GetEmployeeByIdWithoutDocumentsAsync(this DbSet employeeDbSet, int employeeId)
    – The return type needs to be changed to Task as well in order to compile

    It is very frustrating to read through all the posts that include code that does not compile or mentioned any required dependencies.

    • Sean Leitzinger
      February 9, 2021

      Hi, it looks like when I moved my blog over to this site I missed a place where it removed angle brackets. This code should read this DbSet<Employee> employeeDbSet. If you look at the code in Git, you will see this. Likewise, Task should be Task<dynamic>. I would suggest pulling the code down from git.

      • FNI
        February 9, 2021

        Thanks for the quick confirmation about the missing parts. Wasn’t able to find a link to the repo, thats why I was asking.

Leave a comment

Your email address will not be published. Required fields are marked *