Lazy Loading vs Eager Loading

This article describes lazy and Eager Loading of data in the Entity Framework. This concept is one of the most important concepts related to the database hits in an application that can affect the performance of the application due to the basics behind these two. So let's start with it.

In Entity Framework, it is very normal to have entities that are related to each other. For example, we may have a User table that contains basic user information like UserId, Username, Password, emailaddress etc. and another table UserDetails that contains contact details of the same user with attributes like Id, UserId (Foreign key from Users table), Contact Address, State, City etc. So here, the user table has a one-to-many relation with the UserDetails table.

Entity Framework provides the ability to load the data of a parent entity as well as its related child entity at the same time, in other words when we load the data of the user table, we also get the related data of each User from the UserDetails table. This is known as Eager Loading.

On the other hand, if we do not want to load the related entity data at the same time as when the main entity is fetched, we use Lazy Loading.

To begin, we will create a sample project and add an .edmx model into it with two entities, User (parent entity) & UserDetails (child entity). We will also be using the SQL Server Profiler to check how the queries are executed in the database at the back end.

1. So, hope you have set up the project and added the two entities into the database and the edmx model into the sample project as in the following:



2. Next, start the SQL Profiler and select "File" -> "New Trace" to start a new trace. Connect using the settings of the SQL Server.

3. Set the basic details like Trace Name as per your requirements and keep the default settings in the Events Selection Tab. Click on "Run".



The preceding 3 steps will be common to the Lazy/Eager Loading. After that they will differ.

Here comes the Lazy Loading

4. Add the following code in your application. I have created a console based application for convenience.



This is the case where the Lazy Loading is enabled by default. You can check the same in the Edmx.designer.cs file also.

5. Now run the application. In the code above, note the Step 1. At this step, the data of only the main entity, the Users, is loaded but not that of the UserDetails. The data of the related entity is loaded when Step 2 is being executed; in other words when the nested foreach loop is executed.

Let's see what the SQLProfiler has in store for us, for the queries executed for this process. See the screenshots below:



In the above image, the highlighted line displays the query that is executed for the outer foreach that fetches the details of the Users entity. The query is being displayed at the bottom.



In the second image, you can see that the selected query fetches the related data of the first record of the main entity, in other words  this query fetches the records from UserDetails with the fkUserId as 1. You will see the query at the bottom. Similarly, the following two queries will fetch the related records of the User entity records having the ids 2 and 3. You can check that by changing the selection.

This means that to fetch the data of the related entity, database calls are being made again and again, after the data for main entity has been fetched, that could negatively hamper the efficiency of the application. So we need to take care of the scenarios when we should enable this feature .

In the above case, if you do no need the UserDetails, then you can remove the foreach loop . You can remove the inner foreach loop and you will see that the SQL will not fetch the related entity data.

And now the Eager Loading

6. Now we will modify the code that we had in Step 1 to:

var userData = objSampleDBEntities.Users.Include("UserDetails");

Here we are explicitily fetching the data of the related entity, when the data of the main entity is being fetched.

7. Start a new trace by following the steps that we performed earlier & run the application.

8. Now observe the SQL profiler details. See the screenshot below:



Now 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 UserDetails along with the main User 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.

Now the question arises when to use what option:

1. 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.
2. 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.

So I hope this article explains the basic concepts of the Lazy & Eager Loading.