The Brave Programmer - Blogging and coding
Not for the faint hearted
 

Blog Posts From The Brave Programmer

Minimize

Entity Framework 5 – Lazy, Eager, Explicit Loading

Nov27

Written by:
2013/11/27 11:48 AM RssIcon

A normal database is never just one table. In fact a normal database is a collection of tables. In a relational database we have many tables related to each other. Querying these tables efficiently brings enhanced performance to a database based application.

Entity Framework offers several different ways to load the entities that are related to each other. For example, when you query for SalesOrderDetails, there are different ways that the related Products will be queried and loaded.

The question that one needs to consider when loading related entities is whether to use Lazy Loading or Eager Loading.

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. This means that related objects (child objects) are not loaded automatically with its parent object until they are requested. LINQ supports lazy loading by default.

How does lazy loading help us? If properly and appropriately used, it can boost a programs efficiency and performance, by only loading data when needed. Often times a user might not want to view all related data.

In other words when objects (data) are returned by a query, related objects (data) are not loaded at the same time. Instead they are loaded automatically when the navigation property is accessed. That is when a user, the application, specifically requests this data.

Data is only loaded by EF when the data is actually iterated over. So a query like this does nothing

var p = from s in Products select s;

When looking at SQL Profiler we can see that no SQL query is actually executed. To actually retrieve data we need to iteration over the object returned. So something like this will work.

int cnt = p.Count();

or casting the result to a list will work as well:

var myList = q.ToList();

Taking our example above of selecting SalesOrderDetails and then getting the products when needed we see that the below Linq query only returns SalesOrderDetails data and not the related Products.

var sd = from s in SalesOrderDetails select s;

var mylist = sd.ToList();

Because of lazy loading we cast to a list. This iterates over the object and returns the result.

We can see the SQL produced by the above:

SELECT

[Extent1].[SalesOrderID]AS[SalesOrderID], 

[Extent1].[SalesOrderDetailID]AS[SalesOrderDetailID], 

[Extent1].[OrderQty]AS[OrderQty], 

[Extent1].[ProductID]AS[ProductID], 

[Extent1].[UnitPrice]AS[UnitPrice], 

[Extent1].[UnitPriceDiscount]AS[UnitPriceDiscount], 

[Extent1].[LineTotal]AS[LineTotal], 

[Extent1].[rowguid]AS[rowguid], 

[Extent1].[ModifiedDate]AS[ModifiedDate]

FROM[SalesLT].[SalesOrderDetail]AS[Extent1]

This might produce something like:

SalesOrderID

SalesOrderDetailID

ProductID

UnitPrice

LineTotal

ModifiedDate

71774

110562

836

356.898

356.898

6/1/2004

71774

110563

822

356.898

356.898

6/1/2004

71776

110567

907

63.9

63.9

6/1/2004

71780

110616

905

218.454

873.816

6/1/2004

71780

110617

983

461.694

923.388

6/1/2004

 

Say we wanted now to get a list of products from a particular Sale but quering the SalesOrderDetail table and bring back a list of product names.

We could construct a query like:

var p = from s in SalesOrderDetails

where s.SalesOrderID == 71815

select s;
var mylist1 = s.ToList();

But still no product name. Now let's use the object p created above and query the products list

var s2 = from s in p selectnew {

            SalesOrder = s.SalesOrderID,

            SalesOrderDetailID = s.SalesOrderDetailID,

            Product = s.Product.Name

            };

var mylist = s2.ToList();

Which now lazy loads the products and we get an output similar to:

SalesOrder

SalesOrderDetailID

Product

71815

111451

LL Road Frame - Black, 52

71815

111452

ML Road Frame-W - Yellow, 44

71815

111453

Racing Socks, M

 

The SQL Query is as follows:

SELECT

[Extent1].[SalesOrderID]AS[SalesOrderID], 

[Extent1].[SalesOrderDetailID]AS[SalesOrderDetailID], 

[Extent1].[OrderQty]AS[OrderQty], 

[Extent1].[ProductID]AS[ProductID], 

[Extent1].[UnitPrice]AS[UnitPrice], 

[Extent1].[UnitPriceDiscount]AS[UnitPriceDiscount], 

[Extent1].[LineTotal]AS[LineTotal], 

[Extent1].[rowguid]AS[rowguid], 

[Extent1].[ModifiedDate]AS[ModifiedDate]

FROM[SalesLT].[SalesOrderDetail]AS[Extent1]

WHERE 71815 =[Extent1].[SalesOrderID]

GO

 

SELECT

[Extent1].[SalesOrderID]AS[SalesOrderID], 

[Extent1].[SalesOrderDetailID]AS[SalesOrderDetailID], 

[Extent2].[Name]AS[Name]

FROM[SalesLT].[SalesOrderDetail]AS[Extent1]

INNERJOIN[SalesLT].[Product]
AS
[Extent2]
ON
[Extent1].[ProductID]
=
[Extent2].[ProductID]

WHERE 71815 =[Extent1].[SalesOrderID]

 

Note that there are two queries. This is because the second query was only executed when we needed it. AKA Lazy Loading.

Eager Loading

Eager loading is the opposite of Lazy. It loads a specific set of related objects along with the objects that were explicitly requested in the query. Eager loading lets you bring all of the data back from the database in one trip. Entity Framework provides the Include method to enable this. Include takes a string representing a navigation path to related data.

How does eager loading help us? Well it cuts down on multiple trips to the database and multiple queries to the database. This also cut down on network traffic. If you know exactly what data will be needed then Eager loading is the best bet.

The following is an example of eager loading using the same example as above.

var p = from s in SalesOrderDetails.Include(i=> i.Product)

where s.SalesOrderID == 71815

selectnew {

            SalesOrder = s.SalesOrderID,

            SalesOrderDetailID = s.SalesOrderDetailID,

            Product = s.Product.Name

            };

var mylist = p.ToList();

This produces the same output

SalesOrder

SalesOrderDetailID

Product

71815

111451

LL Road Frame - Black, 52

71815

111452

ML Road Frame-W - Yellow, 44

71815

111453

Racing Socks, M

 

But the SQL is totally different. Notice that now we have a more concise SQL statement, with only one round trip to the server.

SELECT

[Extent1].[SalesOrderID]AS[SalesOrderID], 

[Extent1].[SalesOrderDetailID]AS[SalesOrderDetailID], 

[Extent2].[Name]AS[Name]

FROM[SalesLT].[SalesOrderDetail]AS[Extent1]

INNERJOIN[SalesLT].[Product]
AS
[Extent2]
ON
[Extent1].[ProductID]
=
[Extent2].[ProductID]

WHERE 71815 =[Extent1].[SalesOrderID]

Explicit Loading

Explicit loading is very similar to Lazy loading in that you only retrieve the data when you explicitly need it. You may want to leave lazy loading disabled and have more explicit control over when related data is loaded. In addition to explicitly loading with Include, the Entity Framework allows you to selectively and explicitly retrieve related data using one of its Load methods.

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.

In this example we are querying one record:

var x = SalesOrderDetails.Where(w=> w.SalesOrderID == 71815).First();

After some calculation and process we want to get the products related to this SalesOrderDetail. We can explicitly load the products using the Load method. This creates a new SQL Query and another round trip to the database server. The beauty of this method, like Lazy Loading, is that you only load what you need when you need it.

Entry(x).Reference(c=> c.Product).Load();

Now we can refer to the product with normal dot notation and navigate to the product object, as in:

var productName = x.Product.Name;

 

Which one to use relies on the application and the goal of the application and query. Each option has it's pro's and con's. But used correctly, they will enhance the usability and performance to your application.

Tags:
Categories:
blog comments powered by Disqus

1 comment(s) so far...


Gravatar

Re: Entity Framework 5 – Lazy, Eager, Explicit Loading

This was VERY helpful! Keep up the good work. ;)

By NixSec on  2013/12/16 10:28 AM
 
Blog Updates Via E-mail
 Blog Updates Via E-mail
Minimize

Do you want to receive blog updates via e-mail. Then just click on the link below. You will be redirected to Google's feed burner, where you can fill out a form. Supplying your e-mail address.

The subscription is managed entirely by Google's Feedburner. We cannot and do not collect your email address.

Subscribe to The Brave Programmer by Email

Print  
 

 

Latest Comments
 Latest Comments
Minimize
Powered by Disqus

Sign up with Disqus to enjoy a  surprise box of features

Print  
 
Blog Roll
 Blog Roll
Minimize
Print  
 
Categories
 Categories
Minimize
Print  
 
<h1>Search Blogs From The Brave Programmer</h1>
 

Search Blogs From The Brave Programmer

Minimize
Print  
 
Archive
 Archive
Minimize
Archive
<October 2024>
SunMonTueWedThuFriSat
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789
Monthly
Go
Print  
 
<h1>News Feeds (RSS)</h1>
 

News Feeds (RSS)

Minimize
Print  
 

Follow robertbravery on Twitter

Blog Engage Blog Forum and Blogging Community, Free Blog Submissions and Blog Traffic, Blog Directory, Article Submissions, Blog Traffic

View Robert Bravery's profile on LinkedIn

Mybyte

 

Robert - Find me on Bloggers.com

Tags
 Tags
Minimize
Print  
 
Contact Us Now
 Contact Us Now
Minimize
 

Email  us now or call us on 082-413-1420,  to host your website.

We design and develop websites. We develop websites that make a difference. We do Dotnetnuke Module development.

Web Masters Around The World
Power By Ringsurf
Print