I am new to entity framework, I read few article regarding different context loading mechanism in Entity framework. by default lazy loading is enabled but If there is a high ping between your web and sql servers you would go with Eager loading instead of loading related items 1-by-1 with lazy Loading.but one thing really confuse me is Explicit loading .It's done by disabling lazy loading but it itself not even a Eager loading ..I tried to done something for my research....
private static void DifferentLoading()
{
//Lazy - by default
using (var ctx = new NorthwindDBContext())
{
var query = ctx.Categories.Take(3);
foreach (var category in query)
{
Console.WriteLine(category.CategoryName);
foreach (var product in category.Products)
{
Console.WriteLine(product.ProductName);
}
}
}
//Eager loading
using (var ctx = new NorthwindDBContext())
{
ctx.Configuration.LazyLoadingEnabled = false;
var query = ctx.Categories.Include("Products").Take(3); // .Include() to load forcefully
foreach (var category in query)
{
Console.WriteLine(category.CategoryName);
foreach (var product in category.Products)
{
Console.WriteLine(product.ProductName);
}
}
}
//Explicit loading -- On Demand load
using (var ctx = new NorthwindDBContext())
{
ctx.Configuration.LazyLoadingEnabled = false;
var query = ctx.Categories.Take(3);
foreach (var category in query)
{
Console.WriteLine(category.CategoryName);
ctx.Entry(category).Collection(c => c.Products).Load(); // .Include() to load explicitely
foreach (var product in category.Products)
{
Console.WriteLine(product.ProductName);
}
}
}
}
I found different sql fired for different scenarios
1) Lazy
SELECT TOP (3)
[c].[CategoryID] AS [CategoryID],
[c].[CategoryName] AS [CategoryName],
[c].[Description] AS [Description],
[c].[Picture] AS [Picture]
FROM [dbo].[Categories] AS [c]
GO
-- Region Parameters
DECLARE @EntityKeyValue1 Int = 1
-- EndRegion
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
GO
-- Region Parameters
DECLARE @EntityKeyValue1 Int = 2
-- EndRegion
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
GO
-- Region Parameters
DECLARE @EntityKeyValue1 Int = 3
-- EndRegion
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
2) Eager
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
3)Explicit
SELECT TOP (3)
[c].[CategoryID] AS [CategoryID],
[c].[CategoryName] AS [CategoryName],
[c].[Description] AS [Description],
[c].[Picture] AS [Picture]
FROM [dbo].[Categories] AS [c]
GO
-- Region Parameters
DECLARE @EntityKeyValue1 Int = 1
-- EndRegion
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
GO
-- Region Parameters
DECLARE @EntityKeyValue1 Int = 2
-- EndRegion
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
GO
-- Region Parameters
DECLARE @EntityKeyValue1 Int = 3
-- EndRegion
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
I have already go through this thread , It covers my doubt on eager loading and lazy loading. but still a bit confusion on Explicit loading
Eager , Lazy and explicit loading in EF6
Above article really help me to clear my concerns
just one doubt ...why I am going to load something explicitly ...I load it explicitly then use it (Explicit)..better I load it when I need it (Lazy)..To this point there is no use of explicit loading..When to use explicit loading over lazy loading ? Any help
Now My questions are , 1) When to use what ? specially do we really need explicit loading ? if yes , when we need it ? 2) To me query generated for lazy loading and explicit loading are almost similar ? what is the actual difference between lazy and explicit loading ?
