LINQ to SQL Listing 3: Preload Eager-Loaded Association Entities (VB.NET)
The one bright spot in the "using stored procedures for LINQ to SQL data retrieval" picture is the ability to preload EntityRef
collections that populate m:1 associations before executing the stored procedure that fills a GridView bound to a LinqDataSource. VS 2008 beta 2 requires you to use a stored procedure for both operations. This might change by VS 2008 RTM, because stored procedures will return Table<TEntity> rather than ISingleResult<T> types.
'1. Assign specific DataContext instance to the
'LinqDataSource
Protected Sub ldsOrders_ContextCreating(ByVal sender _
As Object, ByVal e _
As System.Web.UI.WebControls. _
LinqDataSourceContextEventArgs) _
Handles ldsOrders.ContextCreating
dcNwind = New NorthwindDataContext
e.ObjectInstance = dcNwind
dcNwind.Log = swLog
End Sub
'2. Occurs before any query is executed so use for LoadWith()s
Protected Sub ldsOrders_ContextCreated(ByVal sender _
As Object, ByVal e _
As System.Web.UI.WebControls. _
LinqDataSourceStatusEventArgs) _
Handles ldsOrders.ContextCreated
If chkEagerLoad.Checked Then
Dim dlOrder As New DataLoadOptions()
dlOrder.LoadWith(Of Order)(Function(o) o.Customer)
dlOrder.LoadWith(Of Order)(Function(o) o.Employee)
dlOrder.LoadWith(Of Order)(Function(o) o.Shipper)
'Preloading 1:n associations doesn't work
'dlOrder.LoadWith(Of Order)(Function(o)
'o.Order_Details)
dcNwind.LoadOptions = dlOrder
End If
End Sub
'3. Preload EntityRef associations the Selecting event
Protected Sub ldsOrders_Selecting(ByVal sender As Object, _
ByVal e As System.Web.UI.WebControls. _
LinqDataSourceSelectEventArgs) _
Handles ldsOrders.Selecting
If dcNwind Is Nothing Then
'Insurance
dcNwind = New NorthwindDataContext()
dcNwind.Log = swLog
End If
If chkPreloadAssoc.Checked Then
'Preload EntityRef associations into memory
lstCustomers.AddRange(dcNwind. _
usp_GetCustomersByCountry("USA"))
lstEmployees.AddRange(dcNwind. _
usp_GetEmployeesByCustomerCountry("USA"))
lstShippers.AddRange(dcNwind. _
usp_GetShippersByCustomerCountry("USA"))
End If
If chkStoredProc.Checked Then
'Turn AutoPage off and get row count directly
ldsOrders.AutoPage = False
e.Arguments.RetrieveTotalRowCount = False
e.Arguments.TotalRowCount = _
dcNwind.ExecuteQuery(Of Integer) _
("EXEC usp_GetOrdersCountByCountry _
'USA'").First()
'Sort this page only
e.Result = _
dcNwind.usp_GetOrdersByCountryPaged("USA", _
e.Arguments.MaximumRows, _
e.Arguments.StartRowIndex).ToList()
Else
'Turn AutoPage on and apply filter to LinqDataSource
ldsOrders.AutoPage = True
ldsOrders.Where = "Customer.Country==""USA"""
End If
End Sub