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. 

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!!!