none
PredicateBuilder - Why do I get WHERE (0=1) ? RRS feed

  • Question

  • So I'm trying to build a dynamic query using PredicateBuilder (found at http://www.albahari.com/nutshell/predicatebuilder.aspx).  If I don't select any thing then the generated sql has a WHERE (0=1) condition in it.  Otherwise it works okay.  How do I get rid of the WHERE 0=1 condition?

    Here's what I'm doing:
    <form id="form1" runat="server">
          Name<br />
                        <asp:DropDownList ID="ddlEmployees" runat="server" AppendDataBoundItems="true" DataTextField="EMPLOYEENAME"
                            DataValueField="USERID">
    <asp:ListItem>--Select--</asp:ListItem> <asp:ListItem>John</asp:ListItem> <asp:ListItem>Jane</asp:ListItem> </asp:DropDownList> <br/> Office<br /> <asp:CheckBoxList ID="cblOffices" runat="server" RepeatColumns="2"> <asp:ListItem>LA</asp:ListItem> <asp:ListItem>DC</asp:ListItem> </asp:CheckBoxList> <asp:Button ID="btnSearch" runat="server" OnClick="btnSearch_Click" Text="Search" /> </form>

    protected void btnSearch_Click(object sender, EventArgs e)
    { 
    
    var predicateUserInfo = PredicateBuilder.False<UserInformation>();
    
            // name       
            if (ddlEmployees.SelectedIndex != 0)
            {         
                predicateUserInfo = predicateUserInfo.Or(p => p.USERID == Convert.ToInt32(ddlEmployees.SelectedValue));
            }
    
              // office
            foreach(ListItem li in cblOffices.Items)
            {
                if (li.Selected)
                {
    
                    string office = li.Value; 
                    predicateUserInfo = predicateUserInfo.Or(p => p.LOCATION == office);              
                }
            }
    
            var q1 = from u in db.UserInformations
                         .Where(predicateUserInfo)
                     select u
    
    }
    
    
    Tuesday, May 12, 2009 8:16 PM

Answers

  • Your problem is in your business logic. If the user enters no criteria, then it's actually a special case - you want NO filter.

    You can address this in a couple of ways. The first is to is to add the following code after initializing PredicateBuilder:


       if (ddlEmployees.SelectedIndex == 0 && cblOffices.SelectedItems.Count == 0)
          predicateUserInfo = PredicateBuilder.True<UserInformation>();


    Alternatively, add the predicate only if a filtering selection has been made:


       IQueryable<UserInformation> query = db.UserInformations;
       if (ddlEmployees.SelectedIndex> 0 || cblOffices.SelectedItems.Count > 0)
          query = query.Where (predicateUserInfo);


    Joe


    Write LINQ queries interactively - www.linqpad.net
    • Marked as answer by dm80 Wednesday, May 13, 2009 3:32 AM
    Wednesday, May 13, 2009 2:01 AM
    Answerer

All replies

  • Hi,

    I am probably wrong and I know that in the link that you provided, Joe states: "Our SearchProducts method still works if no keywords are supplied" but... Could it be that it is because you use .False<>() to start with? 

    good luck.

    LS
    Tuesday, May 12, 2009 8:48 PM
  • Since I'm trying to build an OR'd query I used .False<>().  I did try .True<>() and it doesn't work.
    Tuesday, May 12, 2009 10:20 PM
  • Your problem is in your business logic. If the user enters no criteria, then it's actually a special case - you want NO filter.

    You can address this in a couple of ways. The first is to is to add the following code after initializing PredicateBuilder:


       if (ddlEmployees.SelectedIndex == 0 && cblOffices.SelectedItems.Count == 0)
          predicateUserInfo = PredicateBuilder.True<UserInformation>();


    Alternatively, add the predicate only if a filtering selection has been made:


       IQueryable<UserInformation> query = db.UserInformations;
       if (ddlEmployees.SelectedIndex> 0 || cblOffices.SelectedItems.Count > 0)
          query = query.Where (predicateUserInfo);


    Joe


    Write LINQ queries interactively - www.linqpad.net
    • Marked as answer by dm80 Wednesday, May 13, 2009 3:32 AM
    Wednesday, May 13, 2009 2:01 AM
    Answerer
  • Thanks Joe! 
    Wednesday, May 13, 2009 3:39 PM