(EFCore Best Practices) Lazy Loading and the n+1 problem

Enabling Lazy Loading in EF Core

Before I explain anything else, let's learn how to enable lazy loading in an EF Core project.

There are two methods of doing this - but I will cover the simpler of the two.

PM> Install-Package Microsoft.EntityFrameworkCore.Proxies

First of all install the nuget package called Microsoft.EntityFrameworkCore.Proxies. This package handles most of the complex logic for us.

After this open the Program.cs file and locate the AddDbContext code and add the option called UseLazyLoadingProxies. This will turn on the lazy loading scheme.

The next thing is to modify the model classes and mark the child collections as virtual.

For example, in our project we have an Item class. This class contains a collection of related invoices. If we want to use lazy loading for this collection then we have to mark the property as virtual like public virtual ICollection whatever. . ..

public class Item
{
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public int ID { get; set; }

  public String Name { get; set; } = default!;

  public int Price { get; set; }

  // mark child props virtual 
  public virtual ICollection<Invoice> Invoices { get; set; };

}

With these steps lazy loading is enabled for the Invoices property of the Item object.

Video Explanation (see it happen!)

Please watch the following youtube video:

Lazy Loading in Action!

Next let's add an anchor link to make a call to the database. This will help us see how lazy loading works.

Open the solution explorer, and the Index.cshtml razor page.

Add an anchor link called "Lazy Load of Invoices" as you see here. This is connected to a handler called LazyLoad.

// --- Index.cshtml file ---  
// prior code not shown 
// see the downloads attached to this video 

<a asp-page="Index" asp-page-handler="LazyLoad">
  Lazy Load of Invoices
</a>

Next let us open the backing class Index.cshtml.cs and add the handler. You can obtain the source code from the downloads - so I will show just the handler code.

// --- Index.cshtml file ---  
// prior code not shown 
// see the downloads attached to this video 

public Task OnGetLazyLoadAsync()
{
  // 1 query 
  foreach(var item in _ctx.Items.ToList())
  {
    // 1 query again for each item 
    // because of lazy loading 
    foreach(var invoice in item.Invoices)
    {
    }
  }

  // 1 + 10 (items) = n + 1 queries occur  
  Message = $"LazyLoad executed - check logs!";

  return Task.CompletedTask;
}

This code has two simple foreach loops. The first loop fetches all the records from the Items table.

The inner loop accesses the Invoices collection of each item. It is at this point that Lazy Loading comes into action! It automatically makes a database request to fetch the related invoices of an item. The point to note here is that lazy loading is an automatic action - data is loaded just at the point of need.

This has always been the plus point of lazy loading.

Pitfall of Lazy Loading

But there is a hidden problem now!

Let's turn to the logs. We observe that there is an explosion of SQL queries! There is a trip to database each time the foreach loop runs. This can seriously hamper performance.

  // 1 + n trips to database 

info: 23-Sep-22 10:00:09.808 RelationalEventId.CommandExecuted[20101] 
(Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[@__p_0='?' (DbType = Int32)]]
      SELECT [i].[ID], [i].[InvoiceDate], [i].[ItemFK]
      FROM [Invoices] AS [i]
      WHERE [i].[ItemFK] = @__p_0
info: 23-Sep-22 10:00:09.809 RelationalEventId.CommandExecuted[20101] 
(Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[@__p_0='?' (DbType = Int32)]]
      SELECT [i].[ID], [i].[InvoiceDate], [i].[ItemFK]
      FROM [Invoices] AS [i]
      WHERE [i].[ItemFK] = @__p_0
info: 23-Sep-22 10:00:09.811 RelationalEventId.CommandExecuted[20101] 
(Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[@__p_0='?' (DbType = Int32)]]
      SELECT [i].[ID], [i].[InvoiceDate], [i].[ItemFK]
      FROM [Invoices] AS [i]
      WHERE [i].[ItemFK] = @__p_0
info: 23-Sep-22 10:00:09.814 RelationalEventId.CommandExecuted[20101] 
(Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[@__p_0='?' (DbType = Int32)]]
      SELECT [i].[ID], [i].[InvoiceDate], [i].[ItemFK]
      FROM [Invoices] AS [i]
      WHERE [i].[ItemFK] = @__p_0
info: 23-Sep-22 10:00:09.815 RelationalEventId.CommandExecuted[20101] 
(Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[@__p_0='?' (DbType = Int32)]]
      SELECT [i].[ID], [i].[InvoiceDate], [i].[ItemFK]
      FROM [Invoices] AS [i]  

      // - - - - truncated - - -  // 
  
  

The worse part is that it all happens without any suspicions. Who would suspect two simple loops to raise too many trips to a remote server?

It is because of this pitfall that lazy loading is no longer enabled by default.

The solution

Let's now come to the solution.

One line answer is - AVOID LAZY LOADING IF POSSIBLE.

A better strategy is to use eager loading by using Include operator.

We can add another anchor link and add a handler called EagerLoad

The source code has been attached as a download to the course video.

  // 1. Avoid lazy load if possible 
  // 2. do eager loading if possible 

public Task OnGetEagerLoadAsync()
{
  // 1 query (eager loads) 
  foreach (var item in _ctx.Items.Include(m => m.Invoices).ToList())
  {
    foreach (var invoice in item.Invoices)
    {
    }
  }

  // 1 query occurs 
  Message = $"EagerLoad executed - check logs!";

  return Task.CompletedTask;
}

As you can see we have used the Include operator to include the invoices also.

We can run the application and examine the logs to verify that now there is only a single query to the remote database.

This is an optimization that you can make if requried for your situation. Thanks!