Aug 13 2007

Deferred Loading in DLinq - Orcas Beta 2

Category: DLinq | LinqBil@l @ 23:30

In a previous post of mine (Deferred Loading in DLinq -, I explained to you how deferred loading works in DLinq. There have been some changes to the object used to enable/disable deferred loading in Orcas Beta 2! The new object used is now called System.Data.Linq.DataLoadOptions.

Suppose you have two tables: Customer and Orders from the Northwind database. Now suppose you executed this query:

var getCustomers= from c in db.Customers select c;

The above query will get only the customer's data from the database as this SQL Statement show:

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]

From the above query, it seems that the Orders records was not retrieved. This means, if you were binding this collection of objects to a GridView, and you have a column that repsents the OrderID of each order done by a customer, this means we will have to access the database with the number of customers bound to the GridView + 1 (representing the first query to get Customers). And this is really bad!

The solution is by diabling Deferred loading and asking the Linq To SQL to get the associated objects as follows:

NorthwindDataContext db= new NorthwindDataContext("...");
db.LoadOptions= new System.Data.Linq.DataLoadOptions().LoadWith<Customer>(c => c.Orders);
var query= from c in db.Customers select c;

Linq To SQL will generate the following query:

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax], [t1].[OrderID], [t1].[CustomerID] AS [CustomerID2], [t1].[EmployeeID], [t1].[OrderDate], [t1].[RequiredDate], [t1].[ShippedDate], [t1].[ShipVia], [t1].[Freight], [t1].[ShipName], [t1].[ShipAddress], [t1].[ShipCity], [t1].[ShipRegion], [t1].[ShipPostalCode], [t1].[ShipCountry], (
    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]
ORDER BY [t0].[CustomerID], [t1].[OrderID]

As you can see, the above Linq statment forced the query to get customers to load with it the information about the orders of each customer. All this is done in one database access,

Hope this helps you,

Tags: ,

Comments are closed