LINQ to SQL and prefetching data
The default behavior with LINQ to SQL is to use deferred loading and works just great most of the time. But sometimes you might just want to load all related data at the same time because you know you are going to need it anyway and it saves a number of round trips to the database.
Using the LINQ DataContext this is quite easy to do. In fact all you need to do is configure a DataLoadOptions object and assign it to the LoadOptions property of the data context. The code looks something like this:
Dim context As
New AdventureWorksDataContext
Dim loadOptions As
New DataLoadOptions
loadOptions.LoadWith( _
Function(cust As Customer) cust.CustomerAddresses)
loadOptions.LoadWith( _
Function(custAddress As CustomerAddress) custAddress.Address)
context.LoadOptions = loadOptions
context.Log = Console.Out
Dim query = From cust In context.Customers _
Where cust.CompanyName.Contains("bike") _
Select cust
CustomerBindingSource.DataSource = query
BTW setting the DataContext Log property Console.Out means you can watch the SQL queries in the Output window of Visual Studio.
Speaking about SQL queries the load options used here actually reduce the three entity collections used in the form, Customer, CustomerAddress and Address, just need a single query to load. This is the query generated:
SELECT [t0].[CustomerID], [t0].[NameStyle], [t0].[Title], [t0].[FirstName], [t0].[MiddleName], [t0].[LastName], [t0].[Suffix], [t0].[CompanyName], [t0].[SalesPerson], [t0].[EmailAddress], [t0].[Phone], [t0].[PasswordHash], [t0].[PasswordSalt], [t0].[rowguid], [t0].[ModifiedDate], [t1].[CustomerID] AS [CustomerID2], [t1].[AddressID], [t1].[AddressType], [t1].[rowguid] AS [rowguid2], [t1].[ModifiedDate] AS [ModifiedDate2], [t2].[AddressID] AS [AddressID2], [t2].[AddressLine1], [t2].[AddressLine2], [t2].[City], [t2].[StateProvince], [t2].[CountryRegion], [t2].[PostalCode], [t2].[rowguid] AS [rowguid3], [t2].[ModifiedDate] AS [ModifiedDate3], (
SELECT COUNT(*)
FROM [SalesLT].[CustomerAddress] AS [t3]
INNER JOIN [SalesLT].[Address] AS [t4] ON [t4].[AddressID] = [t3].[AddressID]
WHERE [t3].[CustomerID] = [t0].[CustomerID]
) AS [value]
FROM [SalesLT].[Customer] AS [t0]
LEFT OUTER JOIN ([SalesLT].[CustomerAddress] AS [t1]
INNER JOIN [SalesLT].[Address] AS [t2] ON [t2].[AddressID] = [t1].[AddressID]) ON [t1].[CustomerID] = [t0].[CustomerID]
WHERE [t0].[CompanyName] LIKE @p0
ORDER BY [t0].[CustomerID], [t1].[AddressID]
-- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [%bike%]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
Compare that to the original three separate queries and don't forget that the second and third are executed for each customer:
SELECT [t0].[CustomerID], [t0].[NameStyle], [t0].[Title], [t0].[FirstName], [t0].[MiddleName], [t0].[LastName], [t0].[Suffix], [t0].[CompanyName], [t0].[SalesPerson], [t0].[EmailAddress], [t0].[Phone], [t0].[PasswordHash], [t0].[PasswordSalt], [t0].[rowguid], [t0].[ModifiedDate]
FROM [SalesLT].[Customer] AS [t0]
WHERE [t0].[CompanyName] LIKE @p0
-- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [%bike%]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
'WindowsApplication1.vshost.exe' (Managed): Loaded 'Anonymously Hosted DynamicMethods Assembly'
SELECT [t0].[CustomerID], [t0].[AddressID], [t0].[AddressType], [t0].[rowguid], [t0].[ModifiedDate]
FROM [SalesLT].[CustomerAddress] AS [t0]
WHERE [t0].[CustomerID] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
SELECT [t0].[AddressID], [t0].[AddressLine1], [t0].[AddressLine2], [t0].[City], [t0].[StateProvince], [t0].[CountryRegion], [t0].[PostalCode], [t0].[rowguid], [t0].[ModifiedDate]
FROM [SalesLT].[Address] AS [t0]
WHERE [t0].[AddressID] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [832]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
One gotcha to keep in mind is that you need to configure the DataLoadOptions before assigning them. If you try the following code:
Dim context As
New AdventureWorksDataContext
context.LoadOptions = New DataLoadOptions
context.LoadOptions.LoadWith( _
Function(cust As Customer) cust.CustomerAddresses)
context.LoadOptions.LoadWith( _
Function(custAddress As CustomerAddress) custAddress.Address)
All you get will be an InvalidOperationException with the following message: "LoadWith is not allowed after freeze or attach to DataContext.". Fortunately the error message is clear enough 
Enjoy LINQ to SQL!
www.TheProblemSolver.nl
http://wiki.WindowsWorkflowFoundation.eu