Wednesday, 23 April 2008

LINQDataSource Many-to-many filtering Hell!!

OK new LINQ is all wizzy do and great at Teched and Microsoft demo's etc, but what about when you want to do something real world? Like using drag-and-drop to build an asp.net form with a gridview for editing a many-to-many child table?

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

                    Exit For

                End If

            Next



            If Not keepProduct Then

                ' Remove it from e.result list.

                prodList.Remove(aProduct)

            
End If

        Next

    End Sub



1 comment:

Kevin said...

instead of handling the Selected event, handle the Selecting event and write a little LINQ code.

You also won't have to enable updates/deletes this way.

Try this:

Protected Sub LinqDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.LinqDataSourceSelectEventArgs) Handles LinqDataSource1.Selecting
Dim orderId As Integer = 10248

If Request("orderid") IsNot Nothing Then
orderId = CInt(Request("orderid"))
End If

Dim nl As New NorthwindLinqDataContext()
Dim products = From p In nl.Products _
Join od In nl.Order_Details _
On p.ProductID Equals od.ProductID _
Where od.OrderID = orderId _
Select p


e.Result = products


End Sub