Problem with dynamic joins RRS feed

  • Question

  • I currently have something like this:

    Protected Sub LDSState_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.LinqDataSourceSelectEventArgs) Handles LDSState.Selecting
            Dim sState As String = Location.SelectedValue
            Dim db As New BooksBehindBarsDataContext
            Dim searchbooks = db.BooksBBs.AsQueryable

            If txtSearch.Text = "" And Location.SelectedValue = "Select State" Then
                LblError.Text = "Please enter a state and/or text to search for."
                LblError.Visible = True
                currentPage = 1
                searchbooks = searchbooks.Where(Function(post) (post.Approved = "True"))

                If Location.SelectedValue <> "Select State" Then
                    searchbooks = searchbooks.Where(Function(post) (post.SendState = sState Or post.National = "true"))
                    'searchbooks = searchbooks.Where(Function(post) (post.National = "True"))
                End If
                If txtSearch.Text.Trim <> "" Then
                    'create the array to build the query string
                    Dim sString As String = txtSearch.Text
                    Dim sArray As Array
                    Dim iCount As Integer = 0
                    sString = Replace(sString, " ", "")
                    sArray = Split(sString, ",")
                    For iCount = 0 To UBound(sArray)
                        sString = sArray(iCount)
                        searchbooks = searchbooks.Where(Function(post) (post.Type.Contains(sString)))
                    searchbooks = searchbooks.OrderBy(Function(post) (post.Organization))
                End If
                LblError.Visible = False
                End If
                Dim DataPager1 As DataPager = DirectCast(ListView1.FindControl("DataPager1"), DataPager)
                totalPages = Math.Ceiling(searchbooks.Count / DataPager1.PageSize)
                e.Result = searchbooks
        End Sub

    for dynamic queries against my LinqDataSource.  I'm able to apply a datapager to the result which is what I need to be able to do.  Now I need to include a join.  I've seen examples of how to add a join to a dynamic query, but I still need to apply a datapager to the result, and the examples I've seen haven't let me do that.  Some of the examples I've seen were a bit over my head as well.  Can someone help me with this?  I need all the records in a table called resume, and any records in a second table called members where = resume.inId.  To make matters more interesting, if I don't have a members record, resume holds the data I need.  If I do have a members record, some of the data will be in there instead of in resumes.  Is there a way to do this?

    I already have this working using a stored procedure, but I can't use the datapager.  If it would be simpler, is there a way to use the stored procedure to return the data and still use the datapager?  My customer wants me to convert all the formviews and detailviews that return more than one record to ListViews with a datapager.
    Tuesday, September 1, 2009 6:40 PM