“Data! Data! Data! I can’t make bricks without clay!” Sir Arthur Conan Doyle

“Not everything that can be counted counts, and not everything that counts can be counted.” Albert Einstein

Entity Framework is a very powerful framework that can assist you with working with data in the many .NET Core applications that developers create to solve problems or as Albert Einstein stated to find those things that count to be counted. Like any framework we use, we need to make sure we first understand it and then push it to get the insights that we seek.

This blog post is my attempt to bring a few nuggets of knowledge about Entity Framework Core (EF Core) to developers to make sure that their data queries are efficient and optimized. We will first travel across some of the new features of EF Core since the 2.0 release to make more powerful solutions for our companies, clients or customers. More posts around how to get better with Entity Framework Core are coming!

All the demos I show in this blog post can be found in the following GitHub repository:

https://github.com/cwoodruff/EFCore21Demos

I hope this brings all of us this to this quote I love:

“An investment in knowledge pays the best interest.” Benjamin Franklin

EF Core: Explicitly Compiled Queries

All of our applications that use EF Core to query data perform these queries more than once during the lifetime of the application. There is a simple way to improve the performance of the application when using these common queries. When we run our Windows or ASP.NET Core applications developed in .NET Core, we use Roslyn to compile the code we wrote so that the computer can efficiently execute the application. So why are we running our data queries with that same logic?

EF Core automatically compiles, and caches queries based on a hashed representation of the query expressions. We can gain some performance improvements by allowing EF Core to bypass the computation of the query hash and the cache lookup that happens each time we normally run queries in EF Core. What we get is a compiled query that can be invoked through a delegate on the DBContext from EF Core.

If we use the AdventureWorks database for this example, we can have a compiled query that will return the first Customer for a given AccountNumber.

We now can use the compiled query with a given DBContext (AdventureWorksContext) to get the data needed.

When I run this foreach loop I see around a 53% performance gain using the compiled query over a normal EF Core query. That is a huge improvement. The improvements we get in our applications will vary but why not use this great feature of EF Core at least in queries that we have identified as our most used?

EF Core: Database Scalar Function Mapping

Another useful way to improve the use of EF Core and also make some queries better is to map scalar functions for your database in the DBContext we develop. An example of this is in the following code for BloggingContext.

The C# function that has been annotated with the [DbFunction] attribute now can be called in our applications as followed.

Please note that this EF Core feature can only be used to return scalar values so we cannot return complex values at this time.

EF Core: Query Types

Currently, EF Core does not have a method to model views that exist in our databases. That is a shame because views can be a powerful method to get better insights from our relational databases. What if I told we now we do have a way to handle database views? Interested? Let’s look at how we can do it.

We will be using the following view from a small database in one of my demos. This view calculates the number of blog posts for each blog in the database and returns the name of the blog and an integer value for the post count. Just a reminder that Query Types cannot be used to insert, update or delete data.

When we develop our EF Core DbContext, we can now add a query that can be mapped to a view. This is done in the OnModelCreating() method as shown below.

We can now use the DBQuery in our BloggingContext to get the collection of blogs and post counts.

We can also use Query Types for other scenarios like mapping to tables that do not have primary keys, queries defined in the model or can serve as the return type for FromSql() queries.

EF Core: DbContext Pooling

If you are an ASP.NET Core developer, the following feature for EF Core will sure to be a powerful tool in your toolbelt in the future. By default, our ASP.NET Core applications will register the DBContext into the dependency injection (DI) container so that we can obtain instances of the type for use. What we get with this method is a new instance of our DBContext for each request. What if we could have a better way of handling our DBContext?

We now have a way to create a pool of DBContext types at the start of our web application. These pooled types will still be placed in the DI container for use in our application, but we will no longer be creating a new instance of the DBContext for each call. The new DBContext pooling just needs AddDBContextPool instead of AddDBContext at the time of registering the service.

You have two ways to register the DBContext. The most common way is through the Startup ConfigureServices() method. You can also set up through the OnConfiguring() method in the DBContext.

Doing tests involve DBContext Pooling, I have found that just changing to use pooling can increase the number of calls using a DBContext by around 25%. That is a huge impact just for a simple change in your ASP.NET Core solution.

Wrap Up

I hope in the new year; you find these EF Core features as a way to improve the performance and efficiency of your .NET Core applications. I also hope that learning and digging deeper into the frameworks and tools that we use every day also makes you a better developer.

This blog post is part of The Second Annual C# Advent.

“We are drowning in information but starved for knowledge.” John Naisbitt

5 thoughts on “Getting the Most Out of Entity Framework Core – Part 1

  1. > If you are an ASP.NET Core developer, the following feature for EF Core will sure to be a powerful tool in your toolbelt in the future. By default, our ASP.NET Core applications will register the DBContext into the dependency injection (DI) container so that we can obtain instances of the type for use. What we get with this method is a new instance of our DBContext for each request. What if we could have a better way of handling our DBContext?

    That’s a really bad idea.

    First of all, it makes your code much harder to understand. A DBContext includes its own cache. Which means that DB calls early in the web request can change the behavior of later calls.

    I discovered this when we started leaking private data that got attached to the public data we were returning. We couldn’t catch this with unit tests because it only occurred when the DBContext was reused and the private data happened to be cached.

    *****

    Now lets say you always use .AsNoTracking and separate view-models so you don’t have to worry about caching effects.

    Your application is still going to be inefficient when it comes to using the database. While you are busy serializing objects into JSON or building HTML, that DBContext is still holding onto a database connection. Which means you have one less connection in the pool for use by other web requests. (Or more likely, you are just opening excess connections on the database server.)

    A DBContext should be created when needed and immediately disposed afterwards in order to release the DB connection back to the pool.

    ****

    > We now have a way to create a pool of DBContext types at the start of our web application. These pooled types will still be placed in the DI container for use in our application, but we will no longer be creating a new instance of the DBContext for each call. The new DBContext pooling just needs AddDBContextPool instead of AddDBContext at the time of registering the service.

    Why?

    Database connections are already pooled. You shouldn’t need to layer your own pool on top of the connection pool.

    WTF is going on inside EF core that makes a DBContext do expensive that you need to wrap the connection pool with another pool?

    1. @Jonathan Allen

      That is completely incorrect.

      EF Core contexts are reset when disposed, even when using context pooling, so there are no state or cache issues. Your issues sound like you manually kept the context around instead of using the built-in framework pooling which is a tested and supported feature.

      The performance comes from the fact that EF Core has two initialization steps. The first is when the class is instantiated , which is very fast and cheap, and the second is when it is actually used for a query. This second step is what is saved when using pooling which resets the internal services instead of creating new ones, This can lead to a decent performance improvement when DbContexts are created often for quick no-tracking queries. It can also help in reducing overall memory allocations which is always beneficial.

      Perhaps you should consider reading the documentation and the source code repo to learn more about these features, along with how and why they work: https://github.com/aspnet/EntityFrameworkCore/issues/10125

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.