Problem Populating a LinqDataSource''s EntityRef Association with a Stored Proc
Matt Warren demonstrated inhttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2132697&SiteID=1 andhttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2143547&SiteID=1 how to provide a user override for the LoadEntitySet() function that calls a stored procedure to load EntitySet associations. The following example loads the Order_Details EntitySet, but won't add EntityRefs with stored procs using this syntax:
ProtectedSub ldsOrders_Selecting(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.LinqDataSourceSelectEventArgs)Handles ldsOrders.Selecting
Dim dlOrderAsNew 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)
dlOrder.LoadWith(Of Order)(Function(o) o.Order_Details)
dcNwind.LoadOptions = dlOrder
e.Arguments.RetrieveTotalRowCount =False
e.Arguments.TotalRowCount = 122 'Fixed value for brevity
e.Result = dcNwind.usp_GetOrdersByCountry("USA").ToList()
EndSub
The following function works as expected,
PrivateFunction LoadOrder_Details(ByVal OrdAs Order)As IEnumerable(Of Order_Detail)
Return usp_GetOrder_DetailsByOrderID(Ord.OrderID)
EndFunction
None of the following functions are called (the EntityRef syntax is assumed to be symmetric with that for EntitySets because I haven't found any docs on dealing with this association type):
PrivateFunction LoadCustomer(ByVal CustAs Customer)As Customer
Return usp_GetCustomerByID(Cust.CustomerID).FirstOrDefault()
EndFunction
PrivateFunction LoadEmployee(ByVal EmplAs Employee)As Employee
Return usp_GetEmployeeByID(Empl.EmployeeID).FirstOrDefault()
EndFunction
PrivateFunction LoadShipper(ByVal ShipAs Shipper)As Shipper
Return usp_GetShipperByID(Ship.ShipperID).FirstOrDefault()
EndFunction
If you uncomment the Customer or Employee many:1 prefetch commands, you incur a "Sequence contains more than one element" exception on the last (e.Result = dcNwind.usp...) line. If you uncomment the LoadWith instruction for Shipper, you incur an "Invalid column name 'ShipVia'" SQL Server exception because the LinqDataSource sent
SELECT [t0].[ShipperID], [t0].[CompanyName], [t0].[Phone]
FROM [dbo].[Shippers] AS [t0]
WHERE [t0].[ShipperID] = (ShipVia)
Instead of
SELECT [t0].[ShipperID], [t0].[CompanyName], [t0].[Phone]
FROM [dbo].[Shippers] AS [t0]
WHERE [t0].[ShipperID] = 1
All the above features work as expected with dynamic SQL generation. The two exceptions look like bugs to me. However, I don't have docs that show the correct syntax to intercept the SELECT for the EntityRef association.
Thanks in advance for any light you can shed on these issues,
--rj
