LINQ to SQL Listing 2: Override Built-In Methods (VB.NET)
Typically, you need to implement several kinds of overrides for built-in LoadEntitySet() and LoadEntityRef() functions that you add to a partial DataContext file when you want to substitute stored procedures for LINQ to SQL's default dynamic prepared statements that populate associations. Neither VS 2008 beta 2 documentation nor the online help covers this syntax. Unfortunately, stored procedures are limited to a single entity per execution. Therefore, lazy loading or eager loading require the same number of server round-trips with stored procedures for EntitySets. Preloading EntityRefs can reduce round-trips to populate m:1 associations.
Partial Public Class NorthwindDataContext
'Overrides to invoke stored procs instead of dynamic SQL
'Following are for EntitySets
Private Function LoadOrders(ByVal Cust As Customer) _
As IEnumerable(Of Order)
Return usp_GetOrdersByCustomerID(Cust.CustomerID)
End Function
Private Function LoadOrder_Details(ByVal Ord As Order) _
As IEnumerable(Of Order_Detail)
Return usp_GetOrder_DetailsByOrderID(Ord.OrderID)
End Function
'Following are for EntityRefs
Private Function LoadCustomer(ByVal Ord As Order) _
As Customer
Return usp_GetCustomerByID(Ord.CustomerID). _
FirstOrDefault()
End Function
Private Function LoadEmployee(ByVal Ord As Order) _
As Employee
Return usp_GetEmployeeByID(Ord.EmployeeID). _
FirstOrDefault()
End Function
Private Function LoadShipper(ByVal Ord As Order) As Shipper
Return usp_GetShipperByID(Ord.ShipVia). _
FirstOrDefault()
End Function
End Class