What is difference between lazy/deferred loading and eager loading?

Lazy/Deferred Loading: In case of lazy loading, related objects (child objects) are not loaded automatically with its parent object until they are requested. By default LINQ supports lazy loading.
For Example:

DataContext context = new DataContext();
var query = context.Department.Take(3); // Lazy loading
foreach(var Dept in query) {
	Console.WriteLine(Dept.Name);
	foreach(var Emp in Dept.Employee) {
		Console.WriteLine(Emp.EmpID);
	}
}

Generated SQL Query by LINQ Pad will be:

SELECT
TOP (3) [c].[DeptID] AS [DeptID],
[c].[Name] AS [Name],
[c].[CreatedDate] AS [CreatedDate]
FROM
[dbo].[Department] AS [d]
GO
-- Region Parameters
DECLARE @EntityKeyValue1 Int = 1
-- EndRegion
SELECT
[Extent1].[EmpID] AS [EmpID],
[Extent1].[Name] AS [Name],
[Extent1].[Salary] AS [Salary],
[Extent1].[DeptID] AS [DeptID],
[Extent1].[CreatedDate] AS [CreatedDate],
FROM
[dbo].[Employee] AS [Extent1]
WHERE
[Extent1].[DeptID] = @EntityKeyValue1
GO
-- Region Parameters
DECLARE @EntityKeyValue1 Int = 2
-- EndRegion
SELECT
[Extent1].[EmpID] AS [EmpID],
[Extent1].[Name] AS [Name],
[Extent1].[Salary] AS [Salary],
[Extent1].[DeptID] AS [DeptID],
[Extent1].[CreatedDate] AS [CreatedDate],
FROM
[dbo].[Employee] AS [Extent1]
WHERE
[Extent1].[DeptID] = @EntityKeyValue1
GO
-- Region Parameters
DECLARE @EntityKeyValue1 Int = 3
-- EndRegion
SELECT
[Extent1].[EmpID] AS [EmpID],
[Extent1].[Name] AS [Name],
[Extent1].[Salary] AS [Salary],
[Extent1].[DeptID] AS [DeptID],
[Extent1].[CreatedDate] AS [CreatedDate],
FROM
[dbo].[Employee] AS [Extent1]
WHERE
[Extent1].[DeptID] = @EntityKeyValue1

In above example, you have 4 SQL queries which means calling the database 4 times, one for the Categories and three times for the Products associated to the Categories. In this way, child entity is populated when it is requested.
Eager loading: In case of eager loading, related objects (child objects) are loaded automatically with its parent object. To use Eager loading you need to use Include() method.
For Example:

DataContext context = new DataContext();
var query = context.Department.Include("Employee").Take(3); // Eager loading
foreach(var Dept in query) {
	Console.WriteLine(Dept.Name);
	foreach(var Emp in Dept.Employee) {
		Console.WriteLine(Emp.EmpID);
	}
}

Generated SQL Query will be:

SELECT
[Project1].[DeptID] AS [DeptID],
[Project1].[Name] AS [Name],
[Project1].[CreatedDate] AS [CreatedDate],
[Project1].[D1] AS [D1],
[Project1].[EmpID] AS [EmpID],
[Project1].[Name] AS [Name1],
[Project1].[Salary] AS [Salary],
[Project1].[DeptID] AS [DeptID1],
[Project1].[CreatedDate] AS [CreatedDate1]
FROM
(
SELECT
[Limit1].[DeptID] AS [DeptID],
[Limit1].[Name] AS [Name],
[Limit1].[CreatedDate] AS [CreatedDate],
[Extent2].[EmpID] AS [EmpID],
[Extent2].[Name] AS [Name1],
[Extent2].[Salary] AS [Salary],
[Extent2].[DeptID] AS [DeptID1],
[Extent2].[CreatedDate] AS [CreatedDate1] CASE WHEN ([Extent2].[EmpID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [D1]
FROM
(
SELECT
TOP (3) [d].[DeptID] AS [DeptID],
[d].[Name] AS [Name],
[c].[CreatedDate] AS [CreatedDate]
FROM
[dbo].[Department] AS [d]
) AS [Limit1]
LEFT OUTER JOIN [dbo].[Employee] AS [Extent2] ON [Limit1].[DeptID] = [Extent2].[EmpID]
) AS [Project1]
ORDER BY
[Project1].[DeptID] ASC,
[Project1].[D1] ASC

In above example, you have only one SQL queries which means calling the database only one time, for the Categories and the Products associated to the Categories. In this way, child entity is populated with parent entity.

Tagged , . Bookmark the permalink.

Leave a Reply