Now i will be writing an article on the concept of the lazy and eager loading of the data in the entity framework. This concept is one of the most important concepts related to the database hits from the application, which 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 quite normal situation to have entities that are related to each other. For ex : we may have a User table that contains basic user information like UserId, Username, Password, emailaddress etc. and another table UserDetails which 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 is having one-to-many relation with the UserDetails table.

Entity framework provides us with an ability in which we can load the data of a parent entity as well as its related child entity at the same time i.e. when we load the data of the user table, we also get the related data of each User from the UserDetails table. This is know as the Eager Loading.

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

To start with this process, we will be creating 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 setup the project and added the two entities into the database and edmx model into the sample project like below :

2. Next, start the SQL Profiler and select File-> New Trace & 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 Events Selection Tab settings as default. Click on Run.

The above 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 my ease.

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 above code, note the step 1. At this step, the data of only the main entity i.e. the Usersis loaded and not that of the UserDetails. The data of the related entity is loaded when the step 2 is being executed i.e. when the nested foreach loop is executed.

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

In above image, the highlighted line displays the query that gets executed for the outer foreach which fetches the details of the Usersentity. 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 i.e. this query fetches the records from UserDetails which are having the fkUserId as 1. You can see the query at the bottom. Similarly, the following two queries will fetch the related records of the Userentity records having id’s as 2 and 3. You can check the same 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, which could badly hamper the efficiency of the application. So we need to take care of the scenarios when we should enable this feature .

In above case, if you do no need the UserDetails, 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 our Step 1 to

var userData = objSampleDBEntities.Users.Include(“UserDetails”);

Here we are explicitily mentioning to fetch 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 i.e. the UserDetails along with the main User entity data. You can see the query at the bottom of the selection which 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 much to be loaded at the cost of the queries being made to the database. i.e. fetch all of them at once along with the main entity using the 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 concept of the lazy & eager loading.