LINQ to Entities: Deferred Execution and Lazy Loading

How do deferred execution and lazy loading work in LINQ to Entities (Entity Framework)? Let’s find out!

First, some sample data. We don’t want to make it too simple, so let’s have three entities:

  1. Employee, who belongs to a Department
  2. Department, which is under a Functional Group
  3. Functional Group

The data:

Functional Groups

Id Description
1 Functional Group 01
2 Functional Group 02

Departments

Id Description FunctionalGroupId
1 Department 01 1
2 Department 02 2
3 Department 03 2

Employees

Id First Name Last Name DepartmentId
1 Employee One 1
2 Employee Two 1
3 Employee Three 2
4 Employee Four 2
5 Employee Five 3

Experiment #1: Lazy loading and deferred execution

var context = new SampleModel();

var employees = context.Employees;

foreach (var employee in employees)
{
    Console.WriteLine("{0} {1}",
        employee.FirstName,
        employee.LastName);
}
Console.ReadLine();

Results

Employee One
Employee Two
Employee Three
Employee Four
Employee Five

Generated SQL Statements

I placed a breakpoint right before the foreach loop. Before foreach: nothing. After foreach:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[DepartmentId] AS [DepartmentId]
FROM [dbo].[Employees] AS [Extent1]

Which means that execution is deferred until properties are hit (.FirstName and .LastName). Also, the Departments and FunctionalGroups tables aren’t loaded, because of lazy loading.

Experiment #2: Deferred execution

var context = new SampleModel();

var employees = context.Employees;

foreach (var employee in employees)
{
    Console.WriteLine("{0} {1} - {2} - {3}",
        employee.FirstName,
        employee.LastName,
        employee.Department.Description,
        employee.Department.FunctionalGroup.Description);
}
Console.ReadLine();

Results

Employee One - Department 01 - Functional Group 01
Employee Two - Department 01 - Functional Group 01
Employee Three - Department 02 - Functional Group 02
Employee Four - Department 02 - Functional Group 02
Employee Five - Department 03 - Functional Group 02

Generated SQL Statements

Before foreach: nothing. After foreach:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[DepartmentId] AS [DepartmentId]
FROM [dbo].[Employees] AS [Extent1]

exec sp_executesql N’SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Description] AS [Description],
[Extent1].[FunctionalGroupId] AS [FunctionalGroupId]
FROM [dbo].[Departments] AS [Extent1]
WHERE [Extent1].[Id] = @EntityKeyValue1′,N’@EntityKeyValue1 int’,@EntityKeyValue1=1

exec sp_executesql N’SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Description] AS [Description]
FROM [dbo].[FunctionalGroups] AS [Extent1]
WHERE [Extent1].[Id] = @EntityKeyValue1′,N’@EntityKeyValue1 int’,@EntityKeyValue1=1

exec sp_executesql N’SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Description] AS [Description],
[Extent1].[FunctionalGroupId] AS [FunctionalGroupId]
FROM [dbo].[Departments] AS [Extent1]
WHERE [Extent1].[Id] = @EntityKeyValue1′,N’@EntityKeyValue1 int’,@EntityKeyValue1=2

exec sp_executesql N’SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Description] AS [Description]
FROM [dbo].[FunctionalGroups] AS [Extent1]
WHERE [Extent1].[Id] = @EntityKeyValue1′,N’@EntityKeyValue1 int’,@EntityKeyValue1=2

exec sp_executesql N’SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Description] AS [Description],
[Extent1].[FunctionalGroupId] AS [FunctionalGroupId]
FROM [dbo].[Departments] AS [Extent1]
WHERE [Extent1].[Id] = @EntityKeyValue1′,N’@EntityKeyValue1 int’,@EntityKeyValue1=3

Again, query execution is deferred. This time, the Departments and FunctionalGroups tables are loaded, because I hit both .Department.Description and .Department.FunctionalGroup.Description.

Notice that lots of small SQL statements are sent to the DB to fetch data one piece at a time.

Experiment #3: Adding a using statement

IEnumerable<Employee> employees = null;

using (var context = new SampleModel())
{
    employees = context.Employees;
}

foreach (var employee in employees)
{
    Console.WriteLine("{0} {1} - {2} - {3}",
        employee.FirstName,
        employee.LastName,
        employee.Department.Description,
        employee.Department.FunctionalGroup.Description);
}

Console.ReadLine();

Results

We get an exception! The ObjectContext instance has been disposed and can no longer be used for operations that require a connection. Execution has been deferred, but at the point where the execution is supposed to happen, there is no longer any data context.

The solution is to use either ToArray() or ToList() to not defer execution.

Experiment #4: Immediate execution using ToArray()

IEnumerable<Employee> employees = null;

using (var context = new SampleModel())
{
    employees = context.Employees.ToArray();
}

foreach (var employee in employees)
{
    Console.WriteLine("{0} {1} - {2} - {3}",
        employee.FirstName,
        employee.LastName,
        employee.Department.Description,
        employee.Department.FunctionalGroup.Description);
}

Console.ReadLine();

Generated SQL Statements

Before the foreach loop:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[DepartmentId] AS [DepartmentId]
FROM [dbo].[Employees] AS [Extent1]

After the foreach loop:

An exception, again! The ObjectContext instance has been disposed and can no longer be used for operations that require a connection. Because of lazy loading, the Departments and FunctionalGroups tables weren’t loaded at the outset. So when .Department.Description and .Department.FunctionalGroup.Description were hit, there was a run-time error due to entities not being able to be loaded.

(Note: this implies that ToArray() or ToList(), on their own, will result in immediate execution only for the entity in question. Related entities might be loaded down the line if the relevant properties are hit.)

The solution is to use Include.

Experiment #5: Immediate execution plus eager loading

IEnumerable<Employee> employees = null;

using (var context = new SampleModel())
{
    employees = context.Employees
        .Include("Department.FunctionalGroup")
        .ToArray();
}

foreach (var employee in employees)
{
    Console.WriteLine("{0} {1} - {2} - {3}",
        employee.FirstName,
        employee.LastName,
        employee.Department.Description,
        employee.Department.FunctionalGroup.Description);
}

Console.ReadLine();

Results

Employee One - Department 01 - Functional Group 01
Employee Two - Department 01 - Functional Group 01
Employee Three - Department 02 - Functional Group 02
Employee Four - Department 02 - Functional Group 02
Employee Five - Department 03 - Functional Group 02

Generated SQL Statements

Before the foreach loop:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[DepartmentId] AS [DepartmentId],
[Extent2].[Id] AS [Id1],
[Extent2].[Description] AS [Description],
[Extent2].[FunctionalGroupId] AS [FunctionalGroupId],
[Extent3].[Id] AS [Id2],
[Extent3].[Description] AS [Description1]
FROM [dbo].[Employees] AS [Extent1]
LEFT OUTER JOIN [dbo].[Departments] AS [Extent2] ON [Extent1].[DepartmentId] = [Extent2].[Id]
LEFT OUTER JOIN [dbo].[FunctionalGroups] AS [Extent3] ON [Extent2].[FunctionalGroupId] = [Extent3].[Id]

After the foreach loop: nothing.

Hats off to the Entity Framework for being clever enough to use left joins for many-to-one relationships.

Experiment #6: Immediate execution, no lazy loading

Let’s say we don’t want FunctionalGroups to be loaded if the .Department.FunctionalGroup property is hit somewhere down the line. We only want Departments to be loaded.

IEnumerable<Employee> employees = null;

using (var context = new SampleModel())
{
    employees = context.Employees
        .Include("Department")
        .ToArray()
        .Select(x => new Employee
        {
            FirstName = x.FirstName,
            LastName = x.LastName,
            Department = new Department
            {
                Description = x.Department.Description
            }
        });
}

foreach (var employee in employees)
{
    Console.WriteLine("{0} {1} - {2} - {3}",
        employee.FirstName,
        employee.LastName,
        employee.Department != null ? employee.Department.Description : string.Empty,
        employee.Department != null && employee.Department.FunctionalGroup != null ? employee.Department.FunctionalGroup.Description : string.Empty);
}

Console.ReadLine();

Results

Employee One - Department 01 -
Employee Two - Department 01 -
Employee Three - Department 02 -
Employee Four - Department 02 -
Employee Five - Department 03 -

Generated SQL Statements

Before the foreach loop:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[DepartmentId] AS [DepartmentId],
[Extent2].[Id] AS [Id1],
[Extent2].[Description] AS [Description],
[Extent2].[FunctionalGroupId] AS [FunctionalGroupId]
FROM [dbo].[Employees] AS [Extent1]
LEFT OUTER JOIN [dbo].[Departments] AS [Extent2] ON [Extent1].[DepartmentId] = [Extent2].[Id]

After the foreach loop: nothing. FunctionalGroups isn’t loaded even when .Department.FunctionalGroup is hit, because we’re returning new instances of the Employee entity, not the ObjectContext-derived instances.

Conclusion

Deferred execution and lazy loading are two related concepts in LINQ to Entities. By default, execution is performed only when properties are hit, so that only the necessary entities are loaded. However, this might be undesirable as multitudes of small queries are generated and sent to the server at run-time. If the entities that need to be loaded are known at compile-time by the programmer, it might be better to not use deferred execution nor lazy loading, and ensure that the optimal SQL query is generated and sent to the server by using the techniques highlighted above.

Note of Thanks

I would like to extend my deepest appreciation to Nick Zhebrun and Yuri Korolev for their AnjLab Sql Express Profiler. Truly useful.

9 November 2010 | LINQ, Entity Framework, .NET, C# | Comments

4 Responses to “LINQ to Entities: Deferred Execution and Lazy Loading”

  1. 1 Kshitish 22 June 2011 @ 12:14 am

    Nice one….

  2. 2 khaled 22 June 2012 @ 9:59 pm

    Many thanks! very useful.

  3. 3 lymber 3 July 2012 @ 7:52 am

    nice clear and simple explanation!

  4. 4 harendra 22 March 2013 @ 1:43 pm

    Nice article..removed my confusion. Want to explore EF more. Appreciate if u provide more such articles.
    Thanks

Comments:

  1.  
  2.  
  3.