Performance Optimizations

Having a pool of DbContexts

Entity Framework Core 2 allows us to precreate a pool of DbContexts. Instead of creating one new instance for each time it is requested—normally, at least once per request—you can have a number of contexts precreated and ready to be used. It is configured like this:

Code Listing  136

In this example, we are registering a pool of 128 contexts, which is actually the default.

Using a profiler

There are some profilers, specific for Entity Framework Core or otherwise, that can assist you in writing better queries and understanding what is going on below. The most-used one is probably Entity Framework Profiler, by Hibernating Rhinos.

But you also have to look directly at the database. For that purpose, in the case of SQL Server, you have:

• SQL Server Profiler (included with the SQL Server package)
• Express Profiler (free and open source)

These allow you to hook directly up to the database and see all SQL sent to it.

Filter entities in the database

You are certainly aware that Entity Framework works with two flavors of LINQ:

• LINQ to Objects: Operations are performed in memory.
• LINQ to Entities: Operations are performed in the database.

LINQ to Entities queries that return collections are executed immediately after calling a “terminal” method—one of ToList, ToArray, or ToDictionary—or when enumerating a collection (when GetEnumerator is called). After that, the results are materialized, and are therefore stored in the process’s memory space. Being instances of IEnumerable<T>, they can be manipulated with LINQ to Objects standard operators without us even noticing. This means it’s totally different to issue these two queries, because one will be executed fully by the database, while the other will be executed in memory after retrieving all entities.

Code Listing  137

Beware, you may be bringing a lot more records than you expected!

Do not track entities not meant to be changed

Entity Framework has a first-level (or local) cache where all the entities known by an EF context, loaded from queries or explicitly added, are stored. This happens so that when the time comes to save changes to the database, EF goes through this cache and checks which ones need to be saved (which ones need to be inserted, updated, or deleted). What happens if you load a number of entities from queries when EF has to save? It needs to go through all of them to see which have changed, and that constitutes the memory increase.

If you don’t need to keep track of the entities that result from a query, since they are for displaying only, you should apply the AsNoTracking extension method:

Code Listing  138

This even causes the query execution to run faster, because EF doesn’t have to store each resulting entity in the cache.

Only use eager loading where appropriate

As you saw in the Eager section, you have only two options when it comes to loading navigation properties: either load them together with the root entity, or live without them. Generally speaking, when you are certain of the need to access a reference property or to go through all of the child entities present in a collection, you should eager-load them with their containing entity. There’s a known problem called SELECT N + 1 that illustrates this: you issue one base query that returns N elements, and then you issue another N queries, one for each reference or collection that you want to access.

Performing eager loading is achieved by applying the Include extension method.

Code Listing  139

This way you can potentially save a lot of queries, but it can also bring much more data than the one you need.

Use paging

Instead of retrieving all records from the database, you should only return the useful part of them. For that, you use paging, which is implemented in LINQ through the Skip and Take methods:

Code Listing  140

Don’t forget an OrderBy clause; otherwise, you will get an exception.

Use projections

As you know, normally the LINQ queries return full entities; that is, for each entity, they bring along all of its mapped properties (except references and collections). Sometimes we don’t need the full entity, but just some parts of it, or even something calculated from some parts of the entity. For that purpose, we use projections.

Projections allow us to significantly reduce the data returned by handpicking just what we need, which is particularly useful when we have entities with a large number of properties. Here are some examples:

Code Listing  141

Projections with LINQ depend on anonymous types, but you can also select the result into a .NET class for better access to its properties.

Code Listing  142

Use disconnected entities

When saving an entity, if you need to store in a property a reference to another entity for which you know the primary key, then instead of loading it with Entity Framework, just assign it a blank entity with only the identifier property filled.

Code Listing  143

This will work fine because Entity Framework only needs the foreign key set.

This also applies to deletes—no need to load the entity beforehand; its primary key will do.

Code Listing  144

Use compiled queries

Even though EF Core compiles LINQ queries the first time they are used, there is still a minor performance penalty each time we issue a query, as the cache needs to be looked up. We can use compiled queries—which were present in old versions of Entity Framework—to get around this:

Code Listing  145

As you can see, you can even eagerly include collections. Make sure you add a terminating method, like ToList.

Use SQL where appropriate

If you have looked at the SQL generated for some queries, and you know your SQL, you can probably tell that it is far from optimized. This is because EF uses a generic algorithm for constructing SQL that automatically picks up the parameters from the specified query and puts it all blindly together. Of course, understanding what we want and how the database is designed, we may find a better way to achieve the same purpose.

When you are absolutely certain that you can write your SQL in a better way than EF can, feel free to experiment with SqlQuery and compare the response times. You should repeat the tests a number of times, because other factors may affect the results, such as other accesses to the database, the Visual Studio debugger, and the number of processes running in the test machines. All of these can cause impact.

One thing that definitely has better performance is batch deletes or updates. Always do them with SQL instead of loading the entities, changing or deleting them one by one, and then saving the changes. Use the ExecuteSqlCommand method for that purpose.