How to Optimize Entity Framework Performance?

Entity Framework (EF) is Microsoft’s database layer abstraction tool for .NET applications. The framework uses object-relational mapping technology for manipulating data in databases. It allows you to create prototypes quickly, give them to clients, get feedback, and improve where needed. If significant changes to your application layers are required, EF saves you the trouble of rebuilding and reconnecting the data layer. It also hides database configuration and implementation details, letting you focus on the data and the rest of the application’s layers.

You can use EF on any platform supporting the .NET framework.You don’t need to create different application versions to cater to the other target platforms.

EF’s Detect Changes algorithm checks whether an entity has changed by comparing its current and past values. This inspection determines which values the application needs to update in the database. Database calls are usually expensive for a system, but this approach makes calls more efficient. EF also has routines for handling concurrency conflicts and provides efficient querying with Language Integrated Query (LINQ).

Despite the various benefits of using EF, performance bottlenecks can emerge when it’s incorrectly used. This hands-on Entity Framework tutorial guides you through these bottlenecks and explores how to prevent and solve them.

Prerequisites

To follow this article, you should have the following:

The .NET SDK installed
An understanding of C# and object-oriented programming
Prior experience creating a database using EF
A code editor, such as VS Code
Knowledge of database design and development
EF command line interface and design package (dotnet ef) installed

For guidance along the way, download the tutorial’s code.

Understanding performance bottlenecks in Entity Framework

This section provides a detailed look at three performance bottlenecks you can create if you don’t use EF correctly. The bottlenecks result from inefficient querying, lack of caching, and improper use of tracking. It also demonstrates how to avoid creating these bottlenecks.

Inefficient queries

Inefficient queries can result from improper use of table indices, unnecessary record retrieval, and lack of pagination when pulling large amounts of data. This article’s “Ways of improving entity framework performance” section describes practices you should follow to make your queries more efficient.

Lack of caching

You can implement a caching strategy to avoid frequent, expensive calls to the database. The three built-in forms of caching are object caching, query plan caching, and metadata caching. In addition to these three first-level types, there’s also second-level caching, which you can implement by extending EF.

Improper use of tracking

By default, EF implements tracking for all kinds of data. But, for large amounts of data, keeping track of their states may consume a lot of memory, leading to slowness. It's best to use tracking only for data likely to be updated. That means you shouldn’t use change tracking for read-only data because changes can’t be made to read-only data.

Additionally, it's crucial that you constantly monitor your EF-based applications’ performance. You can use tools like SQL Server Profiler and Glimpse for this. You can also use Performance Counters.

Ways of improving Entity Framework performance

Now that you’re familiar with common bottlenecks in EF, read on to learn the methods you can use to improve EF’s performance.

Use LINQ queries

LINQ is a technology that incorporates data querying capabilities into C#. Its role in EF is to provide compile-time checks when writing queries and loading data from multiple sources while transforming it.

LINQ queries help you minimize the amount of data fetched through projections. Through LINQ, you can also boost performance and minimize round trips to databases using eager and lazy loading. For loading large amounts of data, you can use pagination to reduce memory consumption and improve query performance.

Use projection

The following example demonstrates using projection and selecting specific columns with LINQ to minimize the data fetched.

In a terminal, run this command to create a new console application called EFDemo:

dotnet new console -o EFDemo && cd EFDemo

Next, run the following command to install an SQLite Entity Framework Core database provider first:

dotnet add package Microsoft.EntityFrameworkCore.Sqlite

Then, add a model class called Model.cs and include the following code. This code stores the names of pets and their owners. The application will simulate data storage for a pet and vet service.

using Microsoft.EntityFrameworkCore; 

public class PetContext : DbContext 
{ 
    public virtual DbSet Owners { get; set; } 
    public virtual DbSet Pets { get; set; } 
    public string DbLocation { get; } 

    public PetContext() 
    { 
        var folder = Environment.SpecialFolder.LocalApplicationData; 
        var folderPath = Environment.GetFolderPath(folder); 
        DbLocation = System.IO.Path.Join(folderPath, "demoDB.db"); 
    } 

    protected override void OnConfiguring(DbContextOptionsBuilder options) 
        => options.UseSqlite($"Data Source={DbLocation}"); 
} 

public class Owner{ 
    public int ownerID { get; set; } 
    public string ownerName { get; set; } 
    public string city { get; set; } 
    public string streetCode { get; set; } 
    public string email {get; set;} 

    public List OwnedPets { get; } = new(); 

} 

public class Pet 
{ 
    public int petId { get; set; } 
    public string animalType { get; set; } 
    public int ownerID { get; set; } 
    public Owner Owner { get; set; } 
}

Now, run the migrations for this model using the following command:

dotnet ef migrations add PetApp && dotnet ef database update

Next, update the Program.cs file with the following code:

 using var db = new PetContext(); 

var owners = new List<Owner> 
{ 
    new Owner { 
    ownerID = 76755, 
    ownerName = "John Doe", 
    city = "Ontario", 
    streetCode = "RY45IE3", 
    email = "markalex@mk.com" 
 }, 
 new Owner { 
    ownerID = 7676, 
    ownerName = "Alex Mark", 
    city = "Ontario", 
    streetCode = "RY45IE3", 
    email = "markalex@mk.com" 
  }, 

db.AddRange(owners); 

db.SaveChanges();

var owner1 = (from owner in db.Owners 
            where owner.ownerID == 76755 
            select owner).FirstOrDefault<Owner>(); 

Console.WriteLine(owner1.ownerName); 


 var owner1pets = new List<Pet> 
{ 
    new Pet { animalType = "Dog" }, 
    new Pet { animalType = "Cat" }, 
    new Pet { animalType = "Dog" }, 
  new Pet { animalType = "Parrot" }, 
   new Pet { animalType = "Dog" }, 
    new Pet { animalType = "Squirrel" }, 
    new Pet { animalType = "Cat" } 
}; 

owner1.OwnedPets.AddRange(owner1pets); 
db.SaveChanges(); 

var owner2 = (from owner in db.Owners 
              where owner.ownerID == 7676 
              select owner.ownerName); 

Console.WriteLine(owner2.FirstOrDefault()); 


Console.ReadKey();

Note that this example uses only a few records for brevity.

Now, take a look at these two snippets:

var owner1 = (from owner in db.Owners 
            where owner.ownerID == 76755 
            select owner).FirstOrDefault<Owner>();

var owner2 = (from owner in db.Owners 
              where owner.ownerID == 7676 
              select owner.ownerName);

When you run the program using the dotnet run command in your terminal, it finds a name for the owner with ID 76755 and a name for another owner with ID 7676. The two queries do the same thing, but the first fetches more data than the second. The second query only fetches the owner’s name, while the first returns all columns.

The second is the more efficient query because it only fetches the owner’s name without the rest of the table rows. This example illustrates how minor issues may cause performance bottlenecks as an application grows.

Eager loading

You can also use eager and lazy loading to improve performance. Eager loading fetches related data in the initial query. To implement eager loading, use the Include method, which instructs the query to include the related data when fetching an entity. Doing so avoids more than one database trip.

To fetch owner1’s data with pet information, modify the query to this:

var owner1 = (from owner in db.Owners 
            where owner.ownerID == 76755 
            select owner) 
.Include(pets => pets.Pets) 
.FirstOrDefault();

Lazy loading

Lazy loading loads related data when a navigational property in the data model is accessed, meaning it loads the related data only when you request it.

To use lazy loading in the demo application, modify the OnConfiguring statement in the model class to be the following:

protected override void OnConfiguring(DbContextOptionsBuilder options) 
        => options. 
        .UseLazyLoadingProxies() //This is the newly added method 
        .UseSqlite($"Data Source={DbLocation}"); 
}

Then, to load OwnedPets lazily, you can make it virtual, as shown below:

public virtual List<Pet> OwnedPets { get; set; };

The virtual keyword tells EF OwnedPets is a navigational property that it can override. This avoids overloading the memory with data you may not use, as opposed to eager loading, which loads the related data whether you use it or not. Which method you should use will depend on which is the most appropriate according to your needs.

Use pagination

Another way of using LINQ to improve query performance is using pagination. This is useful for loading vast chunks of data or displaying it in a user interface. There are two types of pagination: offset and keyset.

This example demonstrates offset pagination for displaying the tenth page, where the number of results displayed per page is 25.

var position = 9 * 25; 
var nextPage = db.Pets 
    .OrderBy(b => b.petID) 
    .Skip(position) 
    .Take(25) 
    .ToList();

After looking at using LINQ queries, the next section explores using caching to improve EF’s performance.

Caching in Entity Framework

Caching reduces frequent database calls, helping to prevent a strain on the system’s resources. The following sections discuss three types of caching.

Query caching

Query caching in EF involves configuring the maximum size of the caches and the wait time for clearing the caches. .NET Core Entity Framework has built-in query caching, which caches the results of LINQ-to-Entities queries. Still, it’s limited in not providing explicit control over cache eviction policies or expiration times.

You can use a package manager, such as Entity Framework Plus, to work around this. You can then easily use the FromCache method to retrieve records in subsequent calls from the cache.

For example, you can replace the owner1 query in your code to this:

using Z.EntityFramework.Plus;/*This line should be added at the top of the file to include the Entity Framework Plus package*/

var owner1 = (from owner in db.Owners 
            where owner.ownerID == 76755 
            select owner). FromCache().ToList()

Second-level caching

Second-level caching isn’t available in Entity Framework Core by default. EF 6 supports second-level caching through third-party wrapping providers, such as NCache or Entity Framework Plus. These providers have methods and tools for configuring the caches and their eviction policies, synchronizing the cache with data in databases, and using cache tags for evicting the caches. To add Entity Framework Plus in your project, run the command below in your terminal:

 
dotnet add package Z.EntityFramework.Plus.EFCore --version 7.21.0

For NCache, you can use instructions found on their official website.

Cache eviction

Cache eviction removes old and unused data from the cache memory freeing the cache memory to store new and updated data. You should set an expiration time for data removal. For instance, using Entity Framework Plus, the code below sets the cache to expire after two minutes relative to the current timestamp.

using Z.EntityFramework.Plus;/*This line should be added at the top of the file to include the Entity Framework Plus package*/

 
var options = new MemoryCacheEntryOptions() { AbsoluteExpirationRelativeToNow = 
TimeSpan.FromMinutes(2.0) };

var owner1 = (from owner in db.Owners 
            where owner.ownerID == 76755 
            select owner). FromCache().ToList()

Summary

This Entity Framework tutorial examined the benefits of using EF, issues that may arise when using it, and ways to improve the framework's performance. The problems were inefficient querying strategies, incorrect tracking, and lack of caching. You saw that you could use features provided by LINQ to improve your queries, track only where necessary, and use caching in your queries.

Using EF to optimize database performance is straightforward. Using the LINQ-provided features, you can implement projection, eager loading, lazy loading, and pagination to make your queries as efficient as possible. This will quickly boost the performance of your EF-based applications.

Was this article helpful?

Sorry to hear that. Let us know how we can improve the article.

Optimizing database performance in Entity Framework