As it says somewhere in the bowels of the stupendous MSDN help, LINQDataSource is only for use with a single table: the where clause can only refer to fields within that table or an error will be generated.
I looked all over the net and could find no simple way to implement Northwind's Order - OrderDetails - Products example using LINQDataSource to show an editable gridview containing all the products for a given order - i.e. productList.aspx?orderID=123.
The only (relatively) easy method I could figure out was this:
- Create your LINQ DBML file in the usual way by dragging on all tables from the Server Explorer.
- Do a compile here or you will be frustrated in the next step...
- Add a LINQDataSource(LDS) and Gridview to the form and wire up the LDS to your Products table and the GridView in the usual Dev for Dummies way on the designer.
- Leave the WHERE clause of the LDS empty for now...
- Make sure you tick the * in the SELECT fields, otherwise the cast (below) won't work!!
- Make sure you tick enable update, delete, insert or it won't work (don't ask me why).
- Add an event handler for your LINQDataSource's Selected event. The e.Result parameter gives you access to the returned records as a generic list that you can remove items from, yay!
- Add the code below to the event handler...
- Voila! Almost drag and drop - only a little bit of code required.
Protected Sub LinqDataSource1_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.LinqDataSourceStatusEventArgs) Handles LinqDataSource1.Selected
' Cast result to generic list of (LINQ) Products
Dim prodList As List(Of Product) = CType(e.Result, List(Of Product))
' iterate through each Product and remove any that aren't associated with the passed OrderID
For Each aProduct In New List(Of Product)(prodList)
Dim keepProduct As Boolean = False
' Asking for the Order_Details will cause a new request to SQL for these rows
For Each anOrderDetail As Order_Detail In aProduct.Order_Details
If Request("orderID") IsNot Nothing AndAlso anOrderDetail.OrderID = CInt(Request("orderID")) Then
' We have a match, this product is in the request order, keep it!!
keepProduct = True
If Not keepProduct Then
' Remove it from e.result list.