Understanding Lazy Loading , Eager Loading and Explicit Loading with Entity Framework Implementation

In this article we are going to understand and differentiate between Lazy Loading, Eager Loading and Explicit Loading, first of all let’s define what are those.

Lazy Loading :

Lazy loading is a design pattern commonly used in computer programming to defer initialization of an object until the point at which it is needed. It can contribute to efficiency in the program’s operation if properly and appropriately used. In other words when objects are returned by a query, related objects are not loaded at the same time. Instead they are loaded automatically when the navigation property is accessed.

Eager Loading :

Eager loading is the opposite of Lazy loading which is : The process of loading a specific set of related objects along with the objects that were explicitly requested in the query.

Explicit Loading :

Explicit loading is defined as : when objects are returned by a query, related objects are not loaded at the same time. By default, they are not loaded until explicitly requested using the Load method on a navigation property.

Let’s try those three ways practically.

First create a project and use preferred tool to generate your DbContext , in my example I will use Northwind as a database , ADO.NET Entity Data Model and for class generator EF 4.x  DbContext Class Generator.

Add a MVC3 website project to your solution and start typing on HomeController the Index method :

start your SqlProfiler and notice in this case we have 4 queries which means calling the database 4 times , one for the categories and three times for the products associated to the categories

First query

SELECT TOP (3)
[c].[CategoryID] AS [CategoryID],
[c].[CategoryName] AS [CategoryName],
[c].[Description] AS [Description],
[c].[Picture] AS [Picture]
FROM [dbo].[Categories] AS [c]

Second query

exec sp_executesql N’SELECT
[Extent1].[ProductID] AS [ProductID],
[Extent1].[ProductName] AS [ProductName],
[Extent1].[SupplierID] AS [SupplierID],
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[QuantityPerUnit] AS [QuantityPerUnit],
[Extent1].[UnitPrice] AS [UnitPrice],
[Extent1].[UnitsInStock] AS [UnitsInStock],
[Extent1].[UnitsOnOrder] AS [UnitsOnOrder],
[Extent1].[ReorderLevel] AS [ReorderLevel],
[Extent1].[Discontinued] AS [Discontinued]
FROM [dbo].[Products] AS [Extent1]
WHERE [Extent1].[CategoryID] = @EntityKeyValue1′,N’@EntityKeyValue1 int’,@EntityKeyValue1=1

Third query

exec sp_executesql N’SELECT
[Extent1].[ProductID] AS [ProductID],
[Extent1].[ProductName] AS [ProductName],
[Extent1].[SupplierID] AS [SupplierID],
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[QuantityPerUnit] AS [QuantityPerUnit],
[Extent1].[UnitPrice] AS [UnitPrice],
[Extent1].[UnitsInStock] AS [UnitsInStock],
[Extent1].[UnitsOnOrder] AS [UnitsOnOrder],
[Extent1].[ReorderLevel] AS [ReorderLevel],
[Extent1].[Discontinued] AS [Discontinued]
FROM [dbo].[Products] AS [Extent1]
WHERE [Extent1].[CategoryID] = @EntityKeyValue1′,N’@EntityKeyValue1 int’,@EntityKeyValue1=2

Fourth query

exec sp_executesql N’SELECT
[Extent1].[ProductID] AS [ProductID],
[Extent1].[ProductName] AS [ProductName],
[Extent1].[SupplierID] AS [SupplierID],
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[QuantityPerUnit] AS [QuantityPerUnit],
[Extent1].[UnitPrice] AS [UnitPrice],
[Extent1].[UnitsInStock] AS [UnitsInStock],
[Extent1].[UnitsOnOrder] AS [UnitsOnOrder],
[Extent1].[ReorderLevel] AS [ReorderLevel],
[Extent1].[Discontinued] AS [Discontinued]
FROM [dbo].[Products] AS [Extent1]
WHERE [Extent1].[CategoryID] = @EntityKeyValue1′,N’@EntityKeyValue1 int’,@EntityKeyValue1=3

So in Lazy Loading pattern the Object comes with its associated/related Objects.

Now let’s turn off Lazy Loading and try Eager Loading in our example and notice what will happen. By default Lazy Loading is enabled in Entity Framework, so just add this line before our code and do the same process.

Notice in SqlProfiler that the query was just One , which means it ignored the Object related Objects

SELECT TOP (3)
[c].[CategoryID] AS [CategoryID],
[c].[CategoryName] AS [CategoryName],
[c].[Description] AS [Description],
[c].[Picture] AS [Picture]
FROM [dbo].[Categories] AS [c]

Now we are going to use  Include() method and notice what will happen. just change your code to be :

Notice in SqlProfiler that there is just one query looks like :

SELECT
[Project1].[CategoryID] AS [CategoryID],
[Project1].[CategoryName] AS [CategoryName],
[Project1].[Description] AS [Description],
[Project1].[Picture] AS [Picture],
[Project1].[C1] AS [C1],
[Project1].[ProductID] AS [ProductID],
[Project1].[ProductName] AS [ProductName],
[Project1].[SupplierID] AS [SupplierID],
[Project1].[CategoryID1] AS [CategoryID1],
[Project1].[QuantityPerUnit] AS [QuantityPerUnit],
[Project1].[UnitPrice] AS [UnitPrice],
[Project1].[UnitsInStock] AS [UnitsInStock],
[Project1].[UnitsOnOrder] AS [UnitsOnOrder],
[Project1].[ReorderLevel] AS [ReorderLevel],
[Project1].[Discontinued] AS [Discontinued]
FROM ( SELECT
[Limit1].[CategoryID] AS [CategoryID],
[Limit1].[CategoryName] AS [CategoryName],
[Limit1].[Description] AS [Description],
[Limit1].[Picture] AS [Picture],
[Extent2].[ProductID] AS [ProductID],
[Extent2].[ProductName] AS [ProductName],
[Extent2].[SupplierID] AS [SupplierID],
[Extent2].[CategoryID] AS [CategoryID1],
[Extent2].[QuantityPerUnit] AS [QuantityPerUnit],
[Extent2].[UnitPrice] AS [UnitPrice],
[Extent2].[UnitsInStock] AS [UnitsInStock],
[Extent2].[UnitsOnOrder] AS [UnitsOnOrder],
[Extent2].[ReorderLevel] AS [ReorderLevel],
[Extent2].[Discontinued] AS [Discontinued],
CASE WHEN ([Extent2].[ProductID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
FROM   (SELECT TOP (3) [c].[CategoryID] AS [CategoryID], [c].[CategoryName] AS [CategoryName], [c].[Description] AS [Description], [c].[Picture] AS [Picture]
FROM [dbo].[Categories] AS [c] ) AS [Limit1]
LEFT OUTER JOIN [dbo].[Products] AS [Extent2] ON [Limit1].[CategoryID] = [Extent2].[CategoryID]
)  AS [Project1]
ORDER BY [Project1].[CategoryID] ASC, [Project1].[C1] ASC

This query shows the Object Category and its related Objects Projects in One query.

Finally we will try the Explicit Loading , just change your code to be like this :

Notice in SqlProfiler it looks like the Lazy Loading result , there are 4 queries to the database.

I hope you can differentiate between the three patterns.

Ref:

Wiki

http://blogs.msdn.com

http://weblogs.asp.net

Code Sample:

LoadingsExample