LINQ to SQL : Deferred and Immediate Loading
LINQ to SQL has support for both deferred and immediate loading. By default it supports deferred loading but you can configure your code to load the data immediately. Let’s consider the Northwind database. As we know the there are two tables Customer and Orders and one customer can have multiple orders. These two tables have relationship defined in the database. If you use Linq to Sql (.dbml) designer which generates the background code for us. Once you drag both Customers and Orders you will get the following view in designer
Then in the console I will write the following code,
static void Main(string[] args)
{
//This takes the connection string from file Settings.settings
//which gets generated while creating Linq to Sql (.dbml) file
NorthwindDBDataContext db = new NorthwindDBDataContext();
//Get the Customers from database
var query = from c in db.Customers
where c.City == "London"
select c;
db.Log = Console.Out;
foreach (var c in query)
{
Console.WriteLine("++++++++++++++++++++++++++++++");
Console.WriteLine("Customer Id : " + c.CustomerID);
Console.WriteLine("++++++++++++++++++++++++++++++");
foreach (var o in c.Orders)
{
//Getting the orders value for
//each Customers
Console.WriteLine(o.OrderID);
}
Console.WriteLine();
}
}
The output will show up
++++++++++++++++++++++++++++++
Customer Id : AROUT
++++++++++++++++++++++++++++++
SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate],
t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[S
ipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPost
lCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
WHERE [t0].[CustomerID] = @p0
-- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) NOT NULL [AROUT]
SqlProvider\AttributedMetaModel
10355
10383
10453
10558
10707
10741
10743
10768
10793
10864
10920
10953
11016
++++++++++++++++++++++++++++++
Customer Id : BSBEV
++++++++++++++++++++++++++++++
SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate],
t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[S
ipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPost
lCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
WHERE [t0].[CustomerID] = @p0
-- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) NOT NULL [BSBEV]
SqlProvider\AttributedMetaModel
10289
10471
10484
10538
10539
10578
10599
10943
10947
11023
Every time for each customer this will run a query to fetch the corresponding orders. This is welcomed behavior in most web scenarios as we may need to perform some action like click to view the data.
But if you would like to execute the query at one shot and then display the order values (immediate loading) then you need to modify the above code
static void Main(string[] args)
{
//This takes the connection string from file Settings.settings
//which gets generated while creating Linq to Sql (.dml) file
NorthwindDBDataContext db = new NorthwindDBDataContext();
//This part is new
var shape = new DataShape();
//This indicates that I am loading Orders with Customer
shape.LoadWith<Customer>(c => c.Orders);
db.Shape = shape;
//end of new part
//Get the Customers from database
var query = from c in db.Customers
where c.City == "London"
select c;
db.Log = Console.Out;
foreach (var c in query)
{
Console.WriteLine("++++++++++++++++++++++++++++++");
Console.WriteLine("Customer Id : " + c.CustomerID);
Console.WriteLine("++++++++++++++++++++++++++++++");
foreach (var o in c.Orders)
{
//Getting the orders value for
//each Customers
Console.WriteLine(o.OrderID);
}
Console.WriteLine();
}
Now the generated query will look like,
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[Contact
itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Cou
try], [t0].[Phone], [t0].[Fax], [t1].[OrderID], [t1].[CustomerID] AS [CustomerI
2], [t1].[EmployeeID], [t1].[OrderDate], [t1].[RequiredDate], [t1].[ShippedDate
, [t1].[ShipVia], [t1].[Freight], [t1].[ShipName], [t1].[ShipAddress], [t1].[Sh
pCity], [t1].[ShipRegion], [t1].[ShipPostalCode], [t1].[ShipCountry], (
SELECT COUNT(*)
FROM [dbo].[Orders] AS [t2]
WHERE [t2].[CustomerID] = [t0].[CustomerID]
) AS [count]
FROM [dbo].[Customers] AS [t0]
LEFT OUTER JOIN [dbo].[Orders] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID]
WHERE [t0].[City] = @p0
ORDER BY [t0].[CustomerID], [t1].[OrderID]
-- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) NOT NULL [London]
SqlProvider\AttributedMetaModel
++++++++++++++++++++++++++++++
Customer Id : AROUT
++++++++++++++++++++++++++++++
10355
10383
10453
10558
10707
10741
10743
10768
10793
10864
10920
10953
11016
++++++++++++++++++++++++++++++
Customer Id : BSBEV
++++++++++++++++++++++++++++++
10289
10471
10484
10538
10539
10578
10599
10943
10947
11023
Namoskar!!!