0

Trying to build a linq query based on parameters a user selects (reporting) but it's not working out properly... the entire table is loaded instead of the filtered result set requested. Here is the code that I am using:

Public Sub GetResults()
    Using ctx As New ReportingEntities()

        Dim results As IQueryable(Of Orders) = ctx.Orders

        ' get reason params
        If lbReason.GetSelectedIndices().Count() > 0 Then
            Dim reasons = (From i As ListItem In lbReason.Items
                           Where i.Selected
                           Select Integer.Parse(i.Value)).ToList()

            If reasons IsNot Nothing Then
                Dim where =
                        CommonDataUtils.BuildOrExpressionTree(Of Orders, Integer)(reasons,
                                                                                                Function(r) r.ID)
                results.Where(where)
            End If
        End If

        ' get commodity params
        If lbCommodity.GetSelectedIndices().Count() > 0 Then
            Dim comms = (From i As ListItem In lbCommodity.Items
                           Where i.Selected
                           Select i.Value).ToList()

            If comms IsNot Nothing Then
                Dim where =
                    CommonDataUtils.BuildOrExpressionTree(Of Orders, String)( _
                        DirectCast(comms, IEnumerable(Of String)),
                        Function(r) r.Affected_Commodity)
                results.Where(where)
            End If
        End If

        ' get disposition params
        If lbDisposition.GetSelectedIndices().Count() > 0 Then
            Dim disps = (From i As ListItem In lbDisposition.Items
                       Where i.Selected
                       Select Integer.Parse(i.Value)).ToList()

            If disps IsNot Nothing Then
                Dim where =
                    CommonDataUtils.BuildOrExpressionTree(Of Orders, Integer)( _
                                                    disps,
                                                    Function(r) r.ID)
                results.Where(where)
                filter.Add(where)
            End If
        End If


        ' get facility params
        If lbProdFacility.GetSelectedIndices().Count() > 0 Then
            Dim facs = (From i As ListItem In lbProdFacility.Items
                       Where i.Selected
                       Select Integer.Parse(i.Value)).ToList()

            If facs IsNot Nothing Then
                Dim where =
                        CommonDataUtils.BuildOrExpressionTree(Of Orders, Integer)(facs,
                                                                            Function(r) r.ID)
                results.Where(where)
                filter.Add(where)
            End If
        End If

        ' get customer number params
        Dim custnums As List(Of Integer) = If(ViewState("cust_nums") Is Nothing, Nothing, ViewState("cust_nums"))
        If custnums IsNot Nothing AndAlso custnums.Count > 0 Then
            Dim where = CommonDataUtils.BuildOrExpressionTree(Of Orders, Integer) _
                        (custnums, Function(i) i.ID)
            results.Where(where)
            filter.Add(where)
        End If

        ' get reason code params
        Dim resnums As List(Of Integer) = If(ViewState("res_nums") Is Nothing, Nothing, ViewState("res_nums"))
        If resnums IsNot Nothing AndAlso resnums.Count > 0 Then
            Dim where = CommonDataUtils.BuildOrExpressionTree(Of Orders, Integer) _
                        (resnums, Function(i) i.ID)
            results.Where(where)
            filter.Add(where)
        End If

        ' get date params
        If IsDate(startDate.Value.Trim()) And IsDate(endDate.Value.Trim()) Then
            Dim sdate As DateTime
            Dim edate As DateTime
            DateTime.TryParse(startDate.Value.Trim(), sdate)
            DateTime.TryParse(endDate.Value.Trim(), edate)


            If edate > sdate Then
                Dim dateWhere As Expression(Of Func(Of Orders, Boolean)) =
                    Function(d) (d.Date_Rejected >= sdate And d.Date_Rejected <= edate)
                results.Where(dateWhere)
                filter.Add(dateWhere)
            End If



        ' populate results grid
        grd.DataSource = results
        grd.DataBind()

    End Using
End Sub
2
  • I can include the CommonDataUtils.BuildOrExpressionTree method as well - all it does is take a list of values and turn them into multiple params and creates the expression. It works successfully. Funny thing is if I chain all the expressions returned from CommonDataUtils.BuildOrExpressionTree in a .where(exp).where(exp2) etc... the query works properly... Commented Aug 9, 2011 at 18:46
  • What happens when you step through the code in debug mode? Is it going the places you expect, and producing the query expressions you expect? Any chance you can narrow down the problem a little? Going through a code snippet this size is a lot to ask of people who are helping one another out for free. Commented Aug 9, 2011 at 18:46

1 Answer 1

1

LINQ Queries are immutable, like strings. Remember how this:

s + " world"

... does not actually change the value of s? It works the same way with LINQ queries.

results.Where(where)

... does not change the query. You might be looking for:

results = results.Where(where)
Sign up to request clarification or add additional context in comments.

1 Comment

Thx warrior! Totally overlooked that!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.