Nikolaos Kantzelis ASP.Net Blog

This blog will focus on ASP.NET Framework

Sponsors

About Me

Great Blogs

Lazy Loading,Eager Loading,Explicit Loading in Entity Framework 4

 

This is going to be the ninth post of a series of posts regarding ASP.Net and the Entity Framework and how we can use Entity Framework to access our datastore. You can find the first one here, the second one here , the third one here , the fourth one here, the fifth one here ,the sixth one here ,the seventh one here and the eighth one here.

I have a post regarding ASP.Net and EntityDataSource. You can read it here.I have 3 more posts on Profiling Entity Framework applications. You can have a look at them here, here and here.

In this post I will be looking into Lazy Loading, Eager Loading, Explicit Loading options . It is very important to know what happens when EF generates on the fly the SQL queries that hit the database. You must understand exactly what is hitting your database.I know a lot of people who are using EF and have performance issues with their applications. Most of the time , they get surprised with the number of queries that hit the database to return the data.

I assume that you have access to a version of SQL Server.If you do not, you can download and install the free SQL Server Express edition from here. 

In this post (step 9) you will find a T-SQL script that will create the database objects of the CompanyEmployees database.Before that you execute the T-SQL script you must create in the Query window the CompanyEmployees database yourself. You can download the companiesemployeesinsertt.zip to insert data into the tables.

1) Launch Visual Studio 2010 (express edition will work fine). Create an empty ASP.Net web site from the available templates and choose a suitable name for it. Choose C# as the development language.

2) Add a new item to your website,a web form.Leave the default name.

3) Add a new project to your solution, a class library project.Remove the class1.cs file from the project. 

4) Add a new ADO.Net Entity Data model to the class library project. Choose a suitable name for it, e.g CompanyEmployees.edmx.

5) Then the Wizard pops up. Choose "Generate from Database" option and finish the steps of the wizard by selecting only the tables (Companies,Employees) to be included in the model. Now you have your model ready with all the entities and the generated code.

6) Lazy Loading is enabled by default in EF version 4. Let's write a small query and see how it behaves.We will get the company names for the first 3 companies and then ask for their employee's first name.

You must have your SQL Profiler launched and have the SQL:BatchStarting and RPC:Completed events selected in your new trace.Run the trace.Remember we do not have to do anything to enable Lazy Loading.

In the Page_Load() event handling routine of the Default.aspx page type,

using(var ctx = new CompanyEmployeesEntities())
        {
         
            var query = ctx.Companies.Take(3);

            foreach (var company in query)
            {
                Response.Write(company.CompanyName);

                Response.Write("<br/>");


                foreach (var emp in company.Employees)
                {
                    Response.Write(emp.EmpFirstName);
                    Response.Write("<br/>");
                }
            }

        }

 

7)  Run your application and you will see that we get the company names and their associated employees first names. Have a look at the SQL Profiler trace. In my case we have 4 queries (our database is being hit 4 times). I have the first query selecting the first 3 companies and then 3 queries to get the employees for that company.

  •  SELECT TOP (3)
    [c].[CompanyID] AS [CompanyID],
    [c].[CompanyName] AS [CompanyName]
    FROM [dbo].[Companies] AS [c]

  • exec sp_executesql N'SELECT
    [Extent1].[EmpID] AS [EmpID],
    [Extent1].[EmpFirstName] AS [EmpFirstName],
    [Extent1].[EmpLastName] AS [EmpLastName],
    [Extent1].[Email] AS [Email],
    [Extent1].[CompanyID] AS [CompanyID],
    [Extent1].[Street] AS [Street],
    [Extent1].[City] AS [City],
    [Extent1].[Country] AS [Country],
    [Extent1].[PostalCode] AS [PostalCode],
    [Extent1].[Phone] AS [Phone]
    FROM [dbo].[Employees] AS [Extent1]
    WHERE [Extent1].[CompanyID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1
  • exec sp_executesql N'SELECT
    [Extent1].[EmpID] AS [EmpID],
    [Extent1].[EmpFirstName] AS [EmpFirstName],
    [Extent1].[EmpLastName] AS [EmpLastName],
    [Extent1].[Email] AS [Email],
    [Extent1].[CompanyID] AS [CompanyID],
    [Extent1].[Street] AS [Street],
    [Extent1].[City] AS [City],
    [Extent1].[Country] AS [Country],
    [Extent1].[PostalCode] AS [PostalCode],
    [Extent1].[Phone] AS [Phone]
    FROM [dbo].[Employees] AS [Extent1]
    WHERE [Extent1].[CompanyID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=2
  • exec sp_executesql N'SELECT
    [Extent1].[EmpID] AS [EmpID],
    [Extent1].[EmpFirstName] AS [EmpFirstName],
    [Extent1].[EmpLastName] AS [EmpLastName],
    [Extent1].[Email] AS [Email],
    [Extent1].[CompanyID] AS [CompanyID],
    [Extent1].[Street] AS [Street],
    [Extent1].[City] AS [City],
    [Extent1].[Country] AS [Country],
    [Extent1].[PostalCode] AS [PostalCode],
    [Extent1].[Phone] AS [Phone]
    FROM [dbo].[Employees] AS [Extent1]
    WHERE [Extent1].[CompanyID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=3

 

8)  Let's move on to the Eager Loading option that we have . Under this line

 using(var ctx = new CompanyEmployeesEntities())
        {

 type


 ctx.ContextOptions.LazyLoadingEnabled = false;

 which means that I disable Lazy Loading.This is an option that belongs to the ObjectContext object.

9) Run again your application. You will not see the related entities (employees first names) displayed on the screen. 

In the SQL Profiler, the trace has only this query executed in my database,

  • SELECT TOP (3) [c].[CompanyID] AS [CompanyID], [c].[CompanyName] AS [CompanyName] FROM [dbo].[Companies] AS [c]

 Let's try to load the related entities using the Include() method

 

Just after the line your just typed,

 ctx.ContextOptions.LazyLoadingEnabled = false;

add this line of code

 var query = ctx.Companies.Include("Employees").Take(3);

Comment out this line of code.

var query = ctx.Companies.Take(3);

 

Clear the trace results and start your trace again.

Run your application and see the results.You will see the 3 companies and their related entities - employees first names displayed on the screen.

If you look at the trace results you will see just one query hitting the database. In my case it looks like this

SELECT
[Project1].[CompanyID] AS [CompanyID],
[Project1].[CompanyName] AS [CompanyName],
[Project1].[C1] AS [C1],
[Project1].[EmpID] AS [EmpID],
[Project1].[EmpFirstName] AS [EmpFirstName],
[Project1].[EmpLastName] AS [EmpLastName],
[Project1].[Email] AS [Email],
[Project1].[CompanyID1] AS [CompanyID1],
[Project1].[Street] AS [Street],
[Project1].[City] AS [City],
[Project1].[Country] AS [Country],
[Project1].[PostalCode] AS [PostalCode],
[Project1].[Phone] AS [Phone]
FROM ( SELECT
    [Limit1].[CompanyID] AS [CompanyID],
    [Limit1].[CompanyName] AS [CompanyName],
    [Extent2].[EmpID] AS [EmpID],
    [Extent2].[EmpFirstName] AS [EmpFirstName],
    [Extent2].[EmpLastName] AS [EmpLastName],
    [Extent2].[Email] AS [Email],
    [Extent2].[CompanyID] AS [CompanyID1],
    [Extent2].[Street] AS [Street],
    [Extent2].[City] AS [City],
    [Extent2].[Country] AS [Country],
    [Extent2].[PostalCode] AS [PostalCode],
    [Extent2].[Phone] AS [Phone],
    CASE WHEN ([Extent2].[EmpID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    FROM   (SELECT TOP (3) [c].[CompanyID] AS [CompanyID], [c].[CompanyName] AS [CompanyName]
        FROM [dbo].[Companies] AS [c] ) AS [Limit1]
    LEFT OUTER JOIN [dbo].[Employees] AS [Extent2] ON [Limit1].[CompanyID] = [Extent2].[CompanyID]
)  AS [Project1]
ORDER BY [Project1].[CompanyID] ASC, [Project1].[C1] ASC

10) Now let's investigate the Explicit Loading option.

Comment out this line of code

var query = ctx.Companies.Include("Employees").Take(3);
   

 After these 2 lines of code type,

 Response.Write(company.CompanyName);

 Response.Write("<br/>");

 type,         
 company.Employees.Load();

Clear the trace and start the trace again in the SQL Profiler. Run your application and see the results.You will see the 3 companies and their related entities - employees first names displayed on the screen.

If you look at the SQL Profiler trace we have the same queries like in the Lazy Loading option in step 7. So we have 4 queries hitting the database.

So, I think now we are clear on what happens. Bear in mind that 4 queries sometimes can be less costly than one query.

It is nice to have options. Use them carefully depending on the specific requirements of your application.

Email me if you need the source code.

Hope it helps!!!

Comments

lovedota21 said:

hey I love your article,can you send me the source code my email is lovedota21@gmail.com.

Thanks !

# March 10, 2011 10:06 AM

Mohammad Kabir said:

Well written article. Thanks for sharing.

# August 17, 2011 8:35 PM

Triactol said:

Thanks for this short article.  Seeing both the lazy and eager options was just what I needed for a demo I am building to show our developers ADO.NET Data Services.

# November 4, 2011 11:28 AM

April said:

I have been surfing online greater than 3 hours these days, yet I never discovered

any fascinating article like yours. It's beautiful price enough for me. In my opinion, if all webmasters and bloggers made good content as you probably did, the web will be a lot more helpful than ever before.

# May 8, 2012 3:27 PM

rzuigismhuw@gmail.com said:

You certainly have some agreeable opinions and views

# December 1, 2012 4:54 PM

iqxhzwfdlss@gmail.com said:

Hi, great article it's exactly the point

# December 12, 2012 8:07 PM

Rohit Kesharwani said:

My vote is 5/5 for this. Please send me source code on my emailid rohit421991@hotmail.com

Thanks.

# January 5, 2013 8:50 AM

Vijay Thakare said:

Please send me source code

# February 28, 2013 11:26 PM