DEV Community

Cover image for Infamous N+1 Query Problem with Entity Framework Core
Krzysztof Begiedza
Krzysztof Begiedza

Posted on

Infamous N+1 Query Problem with Entity Framework Core

#dotnet #database #efcore #performance

Abstract

When working with Entity Framework Core, you typically interact with your database using LINQ queries to retrieve and manipulate data. However, sometimes when fetching related entities, you may encounter performance issues. Your application might slow down as your database grows, or perhaps you've run into the infamous N+1 query problem.

Introduction to the N+1 Query Problem

The N+1 query problem is a common performance issue that occurs when using an ORM like Entity Framework Core. This issue arises when you fetch a collection of entities (the "N" entities) and then lazily load related entities one by one (the "+1" entities). For each entity in the collection, a separate query is executed to fetch the related data, resulting in N+1 queries being sent to the database.

Why Does the N+1 Query Problem Occur in Entity Framework Core?

This problem typically occurs when lazy loading is enabled, or when related entities are not explicitly loaded in a single query. By default, Entity Framework Core does not enable lazy loading unless configured using UseLazyLoadingProxies(). When lazy loading is turned on, related entities are fetched only when accessed for the first time.

For example:

// Fetch all guilds
var guilds = context.Guilds.ToList();

// Accessing navigation property triggers additional queries
foreach (var guild in guilds)
{
    // Execute N additional queries (one per guild)
    foreach (var player in guild.Players)
    {
        Console.WriteLine(player.Name);
    }
}
Enter fullscreen mode Exit fullscreen mode

In many cases, the N+1 issue arises because developers forget to use eager loading. Without it, Entity Framework Core does not fetch related entities upfront, leading to multiple queries when accessing navigation properties.

public async Task FetchWithLazyLoadingAsync()
{
    var guilds = await context.Guilds.ToListAsync();

    foreach (var guild in guilds)
    {
        logger.LogInformation("Guild:\t{GuildName} have Players:\t{PlayersCount}", guild.Name, guild.Players.Count);
    }
}
Enter fullscreen mode Exit fullscreen mode

The above code results in N+1 queries, where N is the number of guilds in the database:

06:44:49:503 info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@__get_Item_0='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
      SELECT "p"."Id", "p"."GuildId", "p"."Username"
      FROM "Players" AS "p"
      WHERE "p"."GuildId" = @__get_Item_0
06:44:49:523 info: DatabasesSandbox.Services.GameMonitorService[0]
      Guild:    Guild 16 have Players:  100
06:44:50:696 info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[@__get_Item_0='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
      SELECT "p"."Id", "p"."GuildId", "p"."Username"
      FROM "Players" AS "p"
      WHERE "p"."GuildId" = @__get_Item_0
06:44:50:709 info: DatabasesSandbox.Services.GameMonitorService[0]
      Guild:    Guild 18 have Players:  100
Enter fullscreen mode Exit fullscreen mode

Strategies to Optimize Database Performance with Entity Framework Core

To mitigate the N+1 query problem and optimize database performance, consider using the following strategies:

1. Eager Loading

Eager loading retrieves related entities along with the main entity in a single query, reducing the number of database round trips. Use the Include method to specify which navigation properties to load:

public async Task FetchWithEagerLoadingAsync()
{
    var guilds = await context.Guilds
                              .Include(g => g.Players)
                              .ToListAsync();

    foreach (var guild in guilds)
    {
        logger.LogInformation("Guild:\t{GuildName} have Players:\t{PlayersCount}", guild.Name, guild.Players.Count);
    }
}
Enter fullscreen mode Exit fullscreen mode

This method results in only one query:

06:47:45:534 info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "g"."Id", "g"."Name", "p"."Id", "p"."GuildId", "p"."Username"
      FROM "Guilds" AS "g"
      LEFT JOIN "Players" AS "p" ON "g"."Id" = "p"."GuildId"
      ORDER BY "g"."Id"
06:47:48:820 info: DatabasesSandbox.Services.GameMonitorService[0]
      Guild:    Guild 8 have Players:   100
06:47:49:649 info: DatabasesSandbox.Services.GameMonitorService[0]
      Guild:    Guild 9 have Players:   100
06:47:50:509 info: DatabasesSandbox.Services.GameMonitorService[0]
      Guild:    Guild 25 have Players:  100
06:47:51:371 info: DatabasesSandbox.Services.GameMonitorService[0]
      Guild:    Guild 6 have Players:   100
Enter fullscreen mode Exit fullscreen mode

2. Explicit Loading

Explicit loading allows you to load related entities on demand. This can be done using the Entry method:

public async Task FetchWithExplicitLoadingAsync()
{
    var explicitGuild = context.Guilds.OrderBy(g => g.Name).First();
    await context.Entry(explicitGuild )
                 .Collection(b => b.Players).LoadAsync();

    logger.LogInformation("Guild:\t{GuildName} have Players:\t{PlayersCount}", explicitGuild.Name, explicitGuild.Players.Count);

    var guilds = await context.Guilds.OrderBy(g => g.Name).ToListAsync();

    foreach (var guild in guilds)
    {
        logger.LogInformation("Guild:\t{GuildName} have Players:\t{PlayersCount}", guild.Name, guild.Players.Count);
    }
}
Enter fullscreen mode Exit fullscreen mode

Explicit loading allows you to control when related entities are loaded, avoiding unnecessary queries. In logs below you can see that only one query is executed to fetch related entities for the specified guild, but all other guilds were not loaded and additional queries were executed to fetch related entities for them:

07:18:22:634 info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[@__get_Item_0='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
      SELECT "p"."Id", "p"."GuildId", "p"."Username"
      FROM "Players" AS "p"
      WHERE "p"."GuildId" = @__get_Item_0
07:18:30:019 info: DatabasesSandbox.Services.GameMonitorService[0]
      Guild:    Guild 0 have Players:   100
07:18:31:043 info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "g"."Id", "g"."Name"
      FROM "Guilds" AS "g"
      ORDER BY "g"."Name"
07:18:34:959 info: DatabasesSandbox.Services.GameMonitorService[0]
      Guild:    Guild 0 have Players:   100
07:18:36:855 info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[@__get_Item_0='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
      SELECT "p"."Id", "p"."GuildId", "p"."Username"
      FROM "Players" AS "p"
      WHERE "p"."GuildId" = @__get_Item_0
07:18:36:869 info: DatabasesSandbox.Services.GameMonitorService[0]
      Guild:    Guild 1 have Players:   100
07:18:38:634 info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[@__get_Item_0='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
      SELECT "p"."Id", "p"."GuildId", "p"."Username"
      FROM "Players" AS "p"
      WHERE "p"."GuildId" = @__get_Item_0
07:18:38:648 info: DatabasesSandbox.Services.GameMonitorService[0]
      Guild:    Guild 10 have Players:  100
Enter fullscreen mode Exit fullscreen mode

3. Query Projection

Query projection involves selecting only the required data from the database, reducing the amount of data transferred over the network. You can use the Select method to project the query results into a custom type or an anonymous type. For example:

public async Task FetchWithQueryProjectionAsync()
{
    var guilds = await context.Guilds
        .Select(g => new
        {
            Name = g.Name,
            Players = g.Players.Select(p => p.Username).ToList()
        })
        .ToListAsync();

    foreach (var guild in guilds)
    {
        logger.LogInformation("Guild:\t{GuildName} have Players:\t{PlayersCount}", guild.Name, guild.Players.Count);
    }
}
Enter fullscreen mode Exit fullscreen mode

By projecting the query results, you can optimize database performance by fetching only the necessary data. This can help reduce the number of queries and improve the efficiency of your application as you can observe in the logs:

07:06:56:051 info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "g"."Name", "g"."Id", "p"."Username", "p"."Id"
      FROM "Guilds" AS "g"
      LEFT JOIN "Players" AS "p" ON "g"."Id" = "p"."GuildId"
      ORDER BY "g"."Id"
07:07:06:805 info: DatabasesSandbox.Services.GameMonitorService[0]
      Guild:    Guild 8 have Players:   100
07:07:07:965 info: DatabasesSandbox.Services.GameMonitorService[0]
      Guild:    Guild 9 have Players:   100
07:07:08:980 info: DatabasesSandbox.Services.GameMonitorService[0]
      Guild:    Guild 25 have Players:  100
Enter fullscreen mode Exit fullscreen mode

4. (bonus) Disable Lazy Loading

If lazy loading is not required in your application, you can disable it to prevent unexpected behavior and minimize risk of the N+1 query problem.
You can do it globally by setting the UseLazyLoadingProxies option to false in the Entity Framework Core context configuration. For example:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseLazyLoadingProxies(false);
}
Enter fullscreen mode Exit fullscreen mode

Conclusion

The N+1 query problem is a well-known performance issue in Entity Framework Core that can affect the efficiency of your database queries. By implementing strategies like eager loading, explicit loading, query projection, and disabling lazy loading, you can optimize your application’s performance. Understanding these techniques can significantly improve your application’s responsiveness and database efficiency.

Further reading

  1. Entity Framework Core Documentation
  2. Optimizing Performance in Entity Framework Core
  3. Read this on my blog

Top comments (0)

Read next

moh_moh701 profile image

Mastering C# Fundamentals: Variable Scope

mohamed Tayel -

deewakar_kumar_8812427a98 profile image

7 Practical Tips for Optimizing C# Code Performance

Deewakar Kumar -

leandroats profile image

if-else & early return

Leandro Torres -

dotnetfullstackdev profile image

Mastering in these 10+ Result/Return types in ASP.Net Core MVC

DotNet Full Stack Dev -