Building high performance database queries using Entity Framework Core and AutoMapper

Last Updated: July 31, 2020 | Created: November 30, 2018

When you are writing Entity Framework Core (EF Core) queries (i.e. reading data from the database) you want them to be quick to run (high performance) and quick to write. It turns out that it’s possible to do that in EF, but the “quick to run” part isn’t that obvious. I make a living around writing EF Core code quickly, and that performs well (I wrote the book, “Entity Framework Core in Action”) and my approach is to use the LINQ Select method (quick to run) and the AutoMapper library (quick to write).

This article is about both “quick to run” and “quick to write”, but majors on AutoMapper, as the Select method is easy to understand, but AutoMapper does take a bit of getting used to. I also mention my EfCore.GenericServices library that uses AutoMapper, which speeds up the writing of database accesses even more.

TR;DR; – summary

  • LINQ Select method normally produces the fastest performing query because they only load what you need, and some calculations can be run in the database (see Posts.Count later) .
  • Writing Select queries contains more lines of code than other query approaches because you have to select each property you want to load.
  • AutoMapper’s ProjectTo method can build the Select query for you, but you need to configure the mapping.
  • Setting up the AutoMapper configuration isn’t total obvious (to me at least) so I give you some examples.
  • My GenericServices library uses AutoMapper and does some of that configuring for you.

This article is aimed at developers who use Microsoft’s Entity Framework library, so I assume you are familiar with C# code and either Entity Framework 6 (EF6.x) or Entity Framework Core library. I don’t assume you know the AutoMapper library, but I don’t detail all the features of AutoMapper and give links to its documentation. The examples in this article use EF Core, but all of the ideas are transferable to EF6.x.

Setting the scene – A quick intro to Entity Framework Core

Database accesses are normally referred to as CRUD operation (Create, Read, Update and Delete), with the read referred to in EF as a query. Queries are normally the most common operation, and often the one you want to be quick (Google and Amazon searches have conditioned us to expect very fast searches). Sometimes the query is a list of things, like products you can buy or trains you could catch, and sometimes you need a single item, like a calendar event you want to change. When the data is stored in a relational database, like SQL Server, a query might need data from multiple parts of the database, called tables, to form the exact data the user wants to see.

The EF Core library makes it easy to access a database by mapping the database tables to .NET classes (known as entity classes), and uses the database pointers (known as foreign keys) between tables to create references (known as navigational properties) to other entity classes. It also turns your LINQ commands into the correct language/format used by the database you want to access. The figure below shows four entity classes, which represent a set of Posts, each with a Blogger who wrote it, and some Tags (via a many-to-many PostTag linking entity class).  The relational properties are represented in red.

In general, the LINQ Select operator in Entity Framework Core (EF Core) can produce database queries that are often perform better than any other approach. This is because the Select method allows you to pick exactly the properties you want, including looking into related classes. This performance gain is especially good when part of the query can be done inside the database, e.g. like counting how many posts a blogger has written (I show that later).

The problem is that writing a Select query, with an assignment for each property, is very repetitious and a bit boring (and boring is bad, as I can make mistakes when I am bored). In this article I show how you can use a library called AutoMapper to automatically build Select queries, which saves you time (and you don’t need to be bored anymore). I also cover one of my libraries that uses AutoMapper to make Create, Read, Update and Delete (known as CRUD) database accesses easier.

In the next section I am going to build some queries to show this database in different ways.

Part 1. Building a simple database query – a list Bloggers

The first display I want to build a list of each blogger, with the number of posts they have written. Here is the type of display I want to show.

This must pick out information from two of entity classes. Now, let’s see the different ways of obtaining that data.

1a. Using Eager loading to load the Post classes

One way to do that would be to use Eager Loading. The code would look like this.

var list = context.Bloggers
    .Include(x => x.Posts)
    .ToList();

That gets the data we need, but it pulls in ALL of the post’s content, when I only want to count them. That is very inefficient, because the Posts can be very big. If you need good performance then you don’t want to write your queries this way.

NOTE: You get EVEN WORSE performance if you load the Posts via Lazy Loading, e.g. having a virtual public ICollection<Post> of Posts. That will cause one extra trip to the database for every entry. I get paid to performance tune EF systems and the first thing I look for is Lazy Loading – it’s a major performance drag.

See the Summary of the performance table for detailed timings.

1b. Using a hand-coded Select query

Over the years I have found the LINQ Select queries to be more efficient, where I only select the properties I need into a class I call a Data Transfer Object (DTO), also known as a ViewModel in ASP.NET. Here is my ListBloggersDto class that I am going to copy the data into

 
public class ListBloggersDto 
{
    public string Name { get; set; }
    public string EmailAddress { get; set; }
    public int PostsCount { get; set; }
} 

And here is a hand-coded Select query that will only read in the data I actually need.

 
var list = context.Bloggers.Select(x =>
    new ListBloggersDto
{
    Name = x.Name,
    EmailAddress = x.EmailAddress,
    PostsCount = x.Posts.Count
}).ToList(); 

This is more efficient as it only loads the data it needs. This is especially true for the count of the Blogger’s Posts – this is done in the database, which stops us loading all the Posts just to count them. The SQL that EF Core would create from this query looks like this (don’t worry if you don’t know SQL – take it from me that is very efficient).

 
SELECT "x"."Name", "x"."EmailAddress", (
    SELECT COUNT(*)
    FROM "Posts" AS "p"
    WHERE "x"."BloggerId" = "p"."BloggerId"
) AS "PostsCount"
FROM "Bloggers" AS "x"

Summary of the performance

In section 12.5.1 on my book “Entity Framework Core in Action” I built a query to load the data to create the book display list (see http://efcoreinaction.com/ for live example site) using three approaches. Here is the result of the three different ways of loading the data. As you can see the Select query was the fastest.

NOTE: I didn’t test Lazy loading as EF Core 2.1 wasn’t released when I was writing that chapter, but I can tell you it would be equal (or maybe worse) to the explicit loading timing.

Now, for our much simpler example the Select method would have a sub-millisecond timing because the query is so simple, and would be much quicker than any of the other loading approaches. But the table above gives you a better comparative of a complex query (see my article “Entity Framework Core performance tuning – a worked example” for more on performance)

Quick to write – AutoMapper

Having looked at the “quick to run” part lets move onto the “quick to write” part. The query examples so far are pretty easy to write, but in bigger Select queries you would have to write many assignments, and that gets tedious. This is where AutoMapper comes in, so lets now recreate the same query using AutoMapper.

1c. Using AutoMapper to build the Select query automatically

AutoMapper is what is known as an object-to-object mapper, and can selectively map from one class to another, handling any relationships. AutoMapper can also produce LINQ code via its ProjectTo<T> method (see Queryable Extensions). This allows you to let AutoMapper build your Select queries for you by matching up names. There are three stages to building a Select query using AuthoMapper:

  1. Create your DTO
  2. Add an AutoMapper configuration,
  3. Then use that AutoMapper configuration in your query.

Let’s go through each part in turn

a) Create my DTO

I need to create my DTO, which is the same as the hand-coded one.

 
public class ListBloggersDto
{
    public string Name { get; set; }
    public string EmailAddress { get; set; }
    public int PostsCount { get; set; }
}

The first two properties have the same name and type as properties in the Blogger class, so they are copied. The interesting property is the PostsCount – this is translated as Posts.Count(). That’s a AutoMapper feature which is helpful.

NOTE: AutoMapper can also ‘flatten’ relationships’: what that means it can pick out a one-to-one relationship by joining together the two names without the dot in the middle. You will see this in part 2, where I list each Post, but access the Blogger’s Name by using the name of the relationship, Blogger, followed by the property’s name, Name, i.e. BloggerName.

b) Configure the AutoMapper mapping via dependency injection

There are lots of ways to configure AutoMapper mappings. For NET Core the recommends using AutoMapper’s Profile and assembly scanning with NET Core’s Dependency Injection (DI).

For this example, I use a DI approach to configuring your AutoMapper mapping in an ASP.NET Core web application. First you add a class that inherits AutoMapper’s Profile class, most likely near to where you define your DTOs. In class’s constructor you add one or more CreateMap methods to set up the mapping. Here is a simple example.

 
public class BloggerDtosProfile : AutoMapper.Profile
{
    public BloggerDtosProfile()
    {
        CreateMap<Blogger, ListBloggersDto>();
        // Add other CreateMap’s for any other configs
    }
}

Then you need to call AutoMapper’s AddAutoMapper() method. In ASP.NET Core that would be in the ConfigureServices method in the Startup class, e.g.

 
public IServiceProvider ConfigureServices(
    IServiceCollection services)
{
    // … other setups removed for clarity
    services.AddAutoMapper();
}

The AddAutoMapper() method scans all the assemblies looking for all the classes that inherit AutoMapper.Profile class. It then produces a IMapper instance containing the all the Mappings found.

NOTE: I find the configuring AutoMapper is the hard part, as it relies on me remembering to set up the AutoMapper mapping. I have tried a number of ways to make this better and I think in my EfCore.GenericServices I have got it right. In that I write my DTO and then you need to add an empty interface called ILinkToEntity<TEntity>, where TEntity is your class that EF Core maps to the database. Then the setup of EfCore.GenericServices a) finds the DTOs and then uses the TEntity part of the ILinkToEntity<TEntity> interface to, among other things, form the correct AutoMapper mappings.

c) Use AutoMapper in your query

I’m now going to show you a ASP.NET Core controller so that you can see how the DI works, but you can use AutoMapper in any application you like.

 
public class HomeController : Controller
{
    private readonly BlogContext _context;
    private readonly IMapper _mapper;

    public HomeController(BlogContext context, IMapper mapper)   
    {                                              
        _context = context;  
        _mapper = mapper;                      
    }                                              

    public IActionResult Index()            
    {
        var dtos = _context.Bloggers
            .ProjectTo<ListBloggersDto>(_mapper)     
            .ToList();   

        return View(dtos);         
    }
}

I have injected by EF Core content and the AutoMapper’s IMapper instance into the controller via the constructor. Then any of my methods in the ASP.NET Core controller. And the SQL code produced is the same as the hand-coded Select earlier in 1b.

For this small DTO then it isn’t worth using AutoMapper, but most applications have hundreds of mappings like this, and classes that contains a lot more properties. This is where AutoMapper comes into its own.

NOTE: If you are generally doing CRUD operations then you should look at my article “GenericServices: A library to provide CRUD front-end services from a EF Core database” that uses AutoMapper and EF Core to make CRUD very simple to do.

AutoMapper is clever and can work out some relationships for you, but for more complex relationships you need to do some more configuring, which I cover in the next part.

Part 2: Handling more complex database queries – list of posts

The next example is a summery list of all the articles, showing the bogger (author), the post title and the tags the post has.

This is a bit more complex, especially around getting the tag names.

2a. List posts using hand-coded select query

Let’s start with the hand-coded LINQ Select query. First, I need to show you the DTO I use to hold the resulting query.

 
public class ListPostsDto 
{
    public string BloggerName { get; set; }
    public string Title { get; set; }
    public DateTime LastUpdated { get; set; }
    public List<string> TagNames { get; set; }
}

Now the hand-coded Select query.

 
var dtos = context.Posts.Select(x => 
    new ListPostsDto
{
    BloggerName = x.Blogger.Name,
    Title = x.Title,
    LastUpdated = x.LastUpdated,
    TagNames = x.TagLinks.Select(y => y.Tag.Name).ToList()
}).ToList();

Notice the following:

  • Line 4: I can select the Name of the Blogger by using the navigational property. EF can handle any depth of selection, say the Bogger class had a property called Address, which has its own table holding address information, then I could access the Blogger’s Country via x.Blogger.Address.Country. EF produces efficient SQL (an INNER JOIN) to extract the data from the related tables.
  • Line 7: I want a list of all the Tag Name’s associated with this post. I therefore need to use the PostTag linking table to access the Tag names.

The produces fairly efficient (see note below) SQL query by only extracting the data you need.

NOTE: Handling “many” relationships, i.e. ones that return multiple results like the TagNames, is an area where EF Core can have performance issues. If I left off the .ToList() on the TagNames then it would query the database for each ListPostsDt0 (this is known as the N+1 database queries issue). But in EF Core version 2.1, adding the .ToList() turns the load of all the TagNames into one final database query. This is OK, but can still have problems – see my article “Entity Framework Core performance tuning – a worked example” to see an even better way to handle comma separated strings.

2b. List of posts using AutoMapper and special configuration

AutoMapper is clever enough to automatically map the first three properties in the ListPostsDto – in particular it maps the BloggerName property to Blogger.Name. What it can’t do is work out the mapping for the TagNames, so we need to add some configuration the CreateMap method. I use the original ListPostsDto I used in the hand-coded version, but add the AutoMapper configuration code:

 
public class ListPostsDto
{
    public string BloggerName { get; set; }
    public string Title { get; set; }
    public DateTime LastUpdated { get; set; }
    public List<string> TagNames { get; set; }
}

Now the AutoMapper configuration code.

 
public class PostsDtosProfile : AutoMapper.Profile
{
    public PostsDtosProfile()
    {
        CreateMap<Post, ListPostsDto>()
            .ForMember(
                p => p.TagNames, 
                opt => opt.MapFrom(x => 
                    x.TagLinks.Select(y => y.Tag.Name).ToList()));
    }
}

The ForMember method (lines 6 to 9) takes two parts: the first part defines the property in the DTO that you are mapping (TagNames, on line 7), and the second part (lines 8 to 9) tells AutoMapper how to generate the data to go into the TagNames property from the input class, which is the Post class.

NOTE: AutoMapper automatically turns the BloggerName into Blogger.Name – this is the “flattening” I referred to earlier. This is a very useful feature and makes handling references to a single relationship really easy.

Let’s see the list articles query using AutoMapper’s ProjectTo method. In case I’ll show you a unit test as you might need this in your system. I also show how to create the IMapper variable using a helper method I created, as that is useful too (and checks you created the Profile class correctly).

 
[Fact]
public void TestProjectionMappingPosts()
{
    //SETUP
    var config = AutoMapperHelpers
        .CreateMapperConfig<PostsDtosProfile>();

    //ATTEMPT
    var input = EfTestData.CreateBloggersWithPosts()
        .AsQueryable();
    var list = input.ProjectTo<ListPostsDto>().ToList();

    //VERIFY
    list.First().BloggerName.ShouldEqual("Ada Lovelace");
}

Where my helper method CreateMapperConfig, looks like this.

NOTE: Technical point about unit testing. ProjectTo<T> need MapperConfiguration when testing, while Map<T> needs IMapper. Have a look at my unit tests of AutoMapper here for a good example of how to write tests that include AutoMapper.

 
public static MapperConfiguration CreateMapperConfig<T>() 
    where T : Profile, new()
{
    var config = new MapperConfiguration(cfg =>
    {
        cfg.AddProfile(new T());
    });
    return config ;
}

This produces the same SQL as the hand-codes example.

NOTE: In EfCore.GenericServices you can configure the AutoMapper mappings by adding a class which inherits from PerDtoConfig<TDto, TEntity>. When EfCore.GenericServices is scanning for DTOs it also looks for matching PerDtoConfig<TDto, TEntity> classes uses that data to alter the AutoMapper’s MapperConfiguration.

Conclusion

Firstly, I showed you how to create high performance queries by using the LINQ Select method. The Select query isn’t obvious – in fact the EF Core documentation on loading relationships doesn’t even list it even though you can load relationships in a Select method. But the ASP.NET Core EF tutorial lists Select as an option, as I was asked to help develop that tutorial 😊. If you want fast and effective queries then think about using the LINQ Select method.

The problem with the Select method is it takes some work to write it, e.g. when Selects have lots of properties in them then they get long and repetitious (i.e. boring). I showed that AutoMapper can help by automatically build the Select query for you. While AutoMapper is a great tool, but it takes a bit of getting used to, especially around the configuration. But when you have lots of queries then it’s worth the effort.

Along the way I talk about my EfCore.GenericServices library, which uses AutoMapper inside. This handles the configuration of AutoMapper for you, which makes it a bit easier. But on the other hand, you need to learn how to set up EfCore.GenericServices. I have written an introductory article, some documentation, and an example application in the repo that you can run locally (it uses an in-memory database, so it runs anywhere), so don’t be afraid to try it.

Happy coding.

4 4 votes
Article Rating
Subscribe
Notify of
guest
14 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments
Ivan
Ivan
1 year ago

What about the inverse, mapping from a DTO to Entity? I have run into problems with entity framework, It seems that Automapper overwrites everything so the change tracker thinks that every property is modified. Also, for dto and entityies with relationships things gets even worse, because when convertion a DTO directly to an entity, it is a “detached” entity, so Entity Framework’s change tracker does not know any of the changes made to the entity. At the moment the code I’m using is a mix of setting the entity state to Modified to let EF know things changed and hacks to map relationships/collections Like on update an entity, deleting a child relationship/collection and re-insering childs on update, which is quite inneficient. There must be a better way to map between DTO -> Entity that does not need me (the developer) to “hack” and do maual change tracking.

ajp
ajp
2 years ago
SELECT"x"."Name","x"."EmailAddress", (    SELECTCOUNT(*)    FROM"Posts"AS"p"    WHERE"x"."BloggerId"="p"."BloggerId")AS"PostsCount"FROM"Bloggers"AS"x"

If EF does this kind of query, then this is completely insane to use EF because this kind of query is too expensive`

Last edited 2 years ago by Jon P Smith
jbogard
jbogard
3 years ago

Hey Jon, great article! I’m always curious to get other people’s first impressions of AutoMapper, especially the API. If you have any ideas for improving the configuration API, let me know!

Jon P Smith
3 years ago
Reply to  jbogard

Hi jbogard,

I’m glad you like the article. I have used AutoMapper for many years and it forms parts of four of my libraries. It had saved me countless hours when building applications, so thanks for putting it out there. Glad I could publicize your library, even if you don’t many any money from it:)

I have scratched my head at certain times about what is the best way to configure AutoMapper, especially within my libraries. (I actually got it pretty wrong in one library by putting the profile inside the DTO – very bad idea). The old static mapping configuration was the easiest, but isn’t the right way to go now that DI is so prevalent – especially because static configuration can cause issues with unit testing. I will send you a direct email with any thoughts I have.

Jon P Smith
3 years ago
Reply to  jbogard

PS. Tried to send you an email via lostechies.com but it didn’t work. Drop me your email via my contact form if you would like me to send some thoughts.

ozbob
ozbob
3 years ago

Great article, I’ve been EF6 + Automapper’ing for a while now, but don’t use the ProjectTo, I will give it a go now having read this article.
I didn’t find a definition of .CreateBloggersWithPosts in your article that you refer to in TestProjectionMappingPosts.
I am off to read your next article on Repository Patterns, something I’ve depended on for a long time … https://www.thereformedprogrammer.net/is-the-repository-pattern-useful-with-entity-framework-core/

Jon P Smith
3 years ago
Reply to  ozbob

Hi ozbob,

Glad it introduced the ProjectTo method – I think you will find it useful in building good EF queries.

Sorry about CreateBloggersWithPosts – I pulled the unit test code from another project (EfCore.GenericServices), but I did give a link to that unit test. Unit testing AutoMapper is a little strange because you don’t want to finalise the mappings like you do in a real application. Therefore what you provide to the Map method is different to what you provide to a ProjectTo method. Don’t know why, but found out by trial and error.

PS. Hope the Repository Patterns article is useful.

Richard Young
Richard Young
3 years ago

Jon, I have been poring over your book, and all the revisions to your book, and your articles looking to understand the DTO to entity object updating mystery. Let me first say that your book got me about a 1/4 of the way but it a very difficult book to digest. All the chapters of your book are apparently superseded by your evolving approach to asp.net and data. I’m not sure why but you articles here are more understandable than the book, which glosses over much of what you asre doing here. Automapper is just another part of the evolving picture and I thank you for this article. I also wish that there was a closer relationship with the code examples in the book with the code on GitHub. Its like you coding style changed half way through the book and has morphed into a more sophisticated methodology without completely fleshing out the examples in the book. Like in the past a single book comes that raise me to another level and your book was that, and at the same time I find it needlessly cryptic at time. I do appreciate that these article exist to clarify things.

Jon P Smith
3 years ago
Reply to  Richard Young

Hi Richard,

I’m sorry the book isn’t “doing it” for you. I think I can explain some of the things you are finding, so let me see if this helps.

There is a definite change in the book at Chapter 6. Chapter 1 to 5 are a general flow through how to use EF Core in general. They are most likely the easiest chapters to read. Chapter 6 to 9 is a reference section, and goes into the deep depths of EF Core. You might like to just skim these on first read. I think Chapter 10 onwards are complicated, but more readable (especially Chapter 10 and 15).

There were lots of discussions with Manning about the level of the book, and it was decided to at the intermediate to expert level. That’s because EF Core is actually really complicated underneath and for the book to be useful it needed me to explain these complex parts. I tried my best (the book has 100 diagrams!) but some parts of EF Core are really complicated.

As to my articles I write these to be more accessible, often with a overview near the start (this article has a “Setting The Scene” to try an do that). These articles take me a lot of time – this one took three days and I only started it because one of my clients was ill and I had some time to start it and finish over the weekend. I put this article into the Manning template and its 12 pages long – that is 1/3 of a chapter! In the book I cover AutoMapper in section 10.3 and its 3.5 pages long. The book would take way too long and be three times the size if I tried that.

Finally, yes I have added new libraries and articles after the book, but the book is still the bible on EF Core for me. I actually look things up in my own book maybe every other week! I don’t think I have covered anything in my articles that change what I said in the book – I’m just enjoying applying the knowledge I have gained.

All the best on your journey with EF Core.

Richard Young
Richard Young
4 years ago
Reply to  Jon P Smith

I din’t mean to say that the book isn’t a wealth of information, it just that I always buy ebooks and the kindle version of it has some layout issues that make reading it sort of a pain. I have used the PDF with better results. The book is my singlular guild to the asp.core EFcore universe. I like the old testament, or the koran, I know there is great knowledge locked in the pages, and I simply not worthy to understand it yet, but it teases me with glimpse of information that drives me to search harder.

Murat Türkay
Murat Türkay
3 years ago

Hi John,I love your blog, I learn a lot from you,
Thank you for this great article,

I want to ask, how to filter the complex queries with efcore or do you have a suggestion to do it another way?

for example


var dtos = context.Posts.Select(x =>
new ListPostsDto
{
BloggerName = x.Blogger.Name,
Title = x.Title,
LastUpdated = x.LastUpdated,
TagNames = x.TagLinks.Select(y => y.Tag.Name).ToList(),
TagsCount = x.TagLinks.Count()
}).Where(p=> p.TagsCount > 3).OrderBy(p=> p.TagsCount).ToList();

a query like that,

efcore runs nested queries for every row, and it cause to very very bad performance.

Jon P Smith
3 years ago
Reply to  Murat Türkay

Hi Murat,

There is a way to do it, but it needs you to use a combination of mapping to a DTO that has a “Count” in it (see the ListBloggersDto in this section https://www.thereformedprogrammer.net/building-efficient-database-queries-using-entity-framework-core-and-automapper/#a-create-my-dto) and adding the Where and OrderBy AFTER the AutoMapper ProjectTo. Here is an example based on the ListBloggersDto shown in this article

 
        var dtos = _context.Bloggers
            .ProjectTo(_mapper)
            .Where(x => x.PostsCount > 3)
            .OrderBy(x => x.PostsCount)    
            .ToList(); 
 

The trick is to get the DTO mapping producing SQL that converts the Count (or other aggregates) into SQL code. Then it will perform well.

This combination of mapping, filtering, sorting, paging happens a lot and there is a great pattern called “Query Objects” that is worth learning about. I didn’t show Query Objects in this article but I talk about them in this section in one of my articles https://www.thereformedprogrammer.net/is-the-repository-pattern-useful-with-entity-framework-core/#1-query-objects-a-way-to-isolate-and-hide-database-read-code . I also cover this in chapter 2 in my book.