ADO.NET Entity Framework 4.0 : Loading Data in 4 ways
We are working with Relational Database and using ADO.NET Entity Framework 4.0, it is inevitable that we need to load data. EF4 has improved Lazy Loading feature as compared to its previous version. We can also combine both Lazy and Eager loading.
Based on the amount of data we bring in and type of application we develop, we need to decide which one to choose. There is no single solution.
I have used two tables from Northwind database – Customer and Order (1 to many).
Now, each Customer will have n number of orders, so ideal for Lazy or Immediate loading.
Where is the catch?
This uses a feature introduced in ADO.NET 2.0 known as MultipleActiveResultSets (MARS). This gets added to the connection string of EF4.
Scenario 1: When Lazy loading is enabled (default)
By default in EF4 Lazy Loading is enabled through the Model designer. Which means if you write the below code, to get for every Order, it would send a SQL statement to the SQL Server.
using (var ctx = new NorthwindEntities())
{
var q = from c in ctx.Customers
select c;
foreach (var cust in q)
{
Console.WriteLine("Customer : {0}", cust.CompanyName);
Console.WriteLine("Respective Orders...");
foreach (var ord in cust.Orders)
{
Console.WriteLine("Order given on {0}", ord.OrderDate);
}
Console.WriteLine();
Console.ReadKey();
}
}
In the background for the first foreach loop it would execute the query in SQL Server to fetch the data from only Customers table. Then for the inner foreach loop it would run individual query to get respective set of orders for each customer. So if there are 100 customers and 1000 orders, the total number of queries would be 1001.
Pros and Cons
This is acceptable in situations like where you have paged navigation and only when user asks for it, you load it, else leave it. This involves multiple round trips.
But when you want to pass data thorough layers, you would not be using it as because there will be no active open context. So when it would try to navigate to the child information no data will be found.
Scenario 2: Explicit Loading or conditional Lazy Loading
If you want to control the lazy loading on your own by loading the data only if logic requires, you should be disabling the Lazy Loading option and explicitly call it.
In the code, you need to add the line ctx.ContextOptions.LazyLoadingEnabled = false; This will switch off the Lazy Loading.
//Lazy loading OFF
ctx.ContextOptions.LazyLoadingEnabled = false;
var q = from c in ctx.Customers
select c;
foreach (var cust in q)
{
Console.WriteLine("Customer : {0}", cust.CompanyName);
Console.WriteLine("Respective Orders...");
//Conditionally Load the child information
if (1 == 1)
cust.Orders.Load();
foreach (var ord in cust.Orders)
{
Console.WriteLine("Order given on {0}", ord.OrderDate);
}
Console.WriteLine();
Console.ReadKey();
}
Pros and Cons
This helps us to control the number of roundtrips based on application need. However, we need to be extra careful about testing our code before final deployment.
Scenario 3: When Eager Loading is enabled
If we switch off the Lazy Loading as discussed in Scenario 2 and want all the records to be available in the memory, then we need to use .Include(Entity).
//Lazy loading OFF
ctx.ContextOptions.LazyLoadingEnabled = false;
var q = from c in ctx.Customers.Include("Orders")
select c;
foreach (var cust in q)
{
Console.WriteLine("Customer : {0}", cust.CompanyName);
Console.WriteLine("Respective Orders...");
foreach (var ord in cust.Orders)
{
Console.WriteLine("Order given on {0}", ord.OrderDate);
}
Console.WriteLine();
Console.ReadKey();
}
Pros and Cons
This brings all the Customer and Order information via a single query. This is required when we pass data from one layer to another layer. It also reduces the number of roundtrips. However, it can capture large about of your memory space.
If we check in the profiler we would see a single query like (removed few characters)
SELECT
[Project1].[C1] AS [C1],
[Project1].[CustomerID] AS [CustomerID],...
FROM ( SELECT
[Extent1].[CustomerID] AS [CustomerID], ...
1 AS [C1],
[Extent2].[OrderID] AS [OrderID], ...
CASE WHEN ([Extent2].[OrderID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
FROM [dbo].[Customers] AS [Extent1]
LEFT OUTER JOIN [dbo].[Orders] AS [Extent2] ON [Extent1].[CustomerID] = [Extent2].[CustomerID]
) AS [Project1]
ORDER BY [Project1].[CustomerID] ASC, [Project1].[C2] ASC
Scenario 4: Combination of Eager and Lazy Loading
We could also use .Include and enable lazy loading if we wish to load few data lazily and some one-shot. There is no harm doing it as we are taking the benefit of both the features.
MSDN References
Loading Related Objects (Entity Framework)
https://msdn.microsoft.com/en-us/library/bb896272(VS.100).aspx
I would like to present this blog post to my sweet daughter Wrishika and lovely wife Saswati – without them I would have never had so much of energy to continue.
Namoskar!!!