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

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:

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 select
				new {

            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:

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]
										

INNER
						JOIN
								[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
			

select
				new {

            SalesOrder = s.SalesOrderID,

            SalesOrderDetailID = s.SalesOrderDetailID,

            Product = s.Product.Name

            };

var mylist = p.ToList();

This produces the same output

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]
										

INNER
						JOIN
								[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.

Trackback Print

Tags:

Categories:

Location: Blogs The Brave Programmer