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
comments powered by Disqus
Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.