What is Eager Loading and What is Lazy Loading and What is N+1 Problem in Entity Framework?

In this article, you will learn what Eager Loading and Lazy Loading are and how they work in an application and how you can take advantage of them.

With Entity Framework we have various patterns that you can use to load related entities. As you know, entity types can define navigation properties that represent associations in the data model and you can use these properties to load entities that are related to the returned entity by the defined association. When entities are generated based on the data model, navigation properties are generated for entities at both ends of an association, read here about navigation properties. These navigation properties return either a reference on the "one" end of a one-to-one or many-to-one relationship or a collection on the "many" end of a one-to-many or many-to-many relationship.

With .NET 4.0 and Entity Framework we have various ways by which we can load data objects, some of them are listed below:

Eager Loading or Defining Query Paths with Include
Lazy Loading
Explicit Loading and more

How to choose the loading pattern?

Use Eager Loading when the data in related entities is too costly to be loaded at the cost of the queries being made to the database. In other words, fetch all of them at once along with the main entity using Eager Loading.

Use Lazy Loading when you only need the main entity data to be fetched and you know that the related data will not be required.

Explicit Loading is similar to Lazy Loading but it uses the Load method on an EntityCollection to explicitly load all contact numbers for a single friend. The Load method cannot be used with POCO entities because the navigation properties of POCO entities are not required to return EntityCollection. More here.

What is Eager Loading?

Eager Loading is the opposite of Lazy Loading. It loads the related data in scalar and navigation properties along with query results at the beginning.

Here is the domain model and Entities we will use.

Please note the navigation property "Contact" in the Friend model and a one-to-many relationship.

Now, let's say we want to retrieve a Friend along with a Contact entity using Eager Loading.

public ActionResult Index()
{
var friends = db.Friends.Include(a => a.Contacts);
return View(friends);
}

For Eager Loading, we need to use the "Include" method in the query. The Include method tells the EF to use an Eager Loading strategy in loading a friend and contact information. An Eager Loading strategy attempts to load all data using a single query.

To check this you can use the SQL Server Profiler, see what's happening:

In the above SQL Profiler window, what happens in this case is that the SQL query is generated using the JOIN and it fetches the data of the related entity, in other words the Contact along with the main Friend entity data. You will see the query at the bottom of the selection that shows the join being applied at the back end by the SQL Server. This is known as Eager Loading, which means loading the related entity data along with the data of the main entity.

What is Lazy Loading?

The alternative (and default) strategy for the EF is a Lazy Loading strategy. With Lazy Loading, the EF loads only the data for the primary object in the LINQ query (the Friend) and leaves the Contact object.

Lazy Loading brings in the related data on an as-needed basis, meaning that when something touches the Contact property of a Friend, EF loads the data by sending an additional query to the database.

Now, let's say we want to retrieve only a Friend entity, we will use Lazy Loading for this.

public ActionResult Index()
{
var friends = db.Friends;
return View(friends);
}

To check this we can use the SQL Server Profiler, see what is happening:

In the SQL Profiler window above, what happens in this case is that the simple SQL query is generated to fetch records from the Friend entity and this is known as Lazy Loading which means loading only the data of the primary object (Entity).

What is the N+1 Problem?

Unfortunately, when dealing with a list of friend information, a Lazy Loading strategy can force the framework to send an additional query to the database for each friend in the list. In other words, for a list of 100 friends, Lazy Loading of all the contact data requires 101 total queries. The scenario just described is known as the N+1 problem (because the framework executes 101 total queries to return 100 populated objects), and is a common problem to face when using an object-relational mapping framework. Lazy Loading is convenient, but potentially expensive in some situations.

Let's keep the action as it is for this demonstration:

public ActionResult Index()
{
var friends = db.Friends;
return View(friends);
}

Please note, Friend and Contact entities have one to many relationships with a navigation property and also the above action method returns a list of friends including contacts, so we have the opportunity to iterate through the contact number of each friend on the view.

Now, let's modify the view page to get the contact numbers as well, here it is.

@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.Name)
</td>
<td>
@Html.DisplayFor(modelItem => item.Address)
</td>
<td>
@foreach (var c in item.Contacts)
{
@Html.DisplayFor(modelItem => c.Number)
}
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id=item.FriendId }) |
@Html.ActionLink("Details", "Details", new { id=item.FriendId }) |
@Html.ActionLink("Delete", "Delete", new { id=item.FriendId })
</td>
</tr>
}

I highlighted the piece of code. Now let me turn on the SQL Server Profiler to see what's happening and run the view page.

Let me tell you one thing, there is only 4 records in the Friend database table, but if you look at the preceding image, you will notice there are 5 queries executed. So, for 4 records there are 5 queries, now think about the hundreds or thousands records. This is known as the N+1 Problem of Lazy Loading.

Hope this helps.