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

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>