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

In this post you will learn what Eager Loading is and what Lazy Loading is and how this works in the application, and how you can take its advantages.

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 listed below:

1. Eager Loading or Defining Query Paths with Include

2. Lazy Loading

3. Explicit Loading and more

How to choose 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.

  

Use Explicit Loading this is similar to lazy loading but it uses the Load method on 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 opposite of Lazy Loading. It loads the related data in scalar and navigation properties along with query result at first shot.

Here is the domain model and Entities we are going to use.

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

Now, let’s say we want to retrieve Friend along with Contact entity using eager loading.

public ActionResult Index()

{

    var friends = db.Friends.Include(a => a.Contacts);

    return View(friends);

}

For eager loading, we have to use ‘Include’ method in the query. 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 SQL Server Profiler, see what's happening:

In 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, 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 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 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 SQL Server Profiler, see what happening:

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

What is 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. Means, for a list of 100 friends, lazy loading 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 bring back 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 has one to many relationships with navigation property and also above action method is returning list of friends including contacts, so we have 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 peace of code. Now let me 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 Friend database table, but if you look at the above image, you will notice there are 5 queries executed. So, for 4 records there are 5 queries, now think about hundreds or thousands records. This is known as N+1 Problem of Lazy Loading.

Hope this helps.