LINQ PredicateBuilder not work in my dynamic search

Question

I have a book table in my database and I have book view named vwShared in my edmx. I want to create dynamic search with operators for user to find books. I have 2 SearchColumns dropdownlist contains "Title, Authors, Published Year, Subject". I have 2 SearchType dropdownlist contains "StartsWith, Contains, EndsWith, Equals". I have another dropdownlist contains "AND, OR" to combine 2 search results. The following is my code.

var predicate = PredicateBuilder.True<DataLayer.vwShared>();

if (joinOperator == "AND")
{
if (SearchColumn1 == "Title" && SearchType1 == "Contains")
predicate = predicate.And(e1 => e1.Title.Contains(txtSearch1.Text));
if (SearchColumn2 == "Authors" && SearchType2 == "Contains")
predicate = predicate.And(e1 => e1.Authors.Contains(txtSearch2.Text));
}
else if (joinOperator == "OR")
{
if (SearchColumn1 == "Title" && SearchType1 == "Contains")
predicate = predicate.Or(e1 => e1.Title.Contains(txtSearch1.Text));
if (SearchColumn2 == "Authors" && SearchType2 == "Contains")
predicate = predicate.Or(e1 => e1.Authors.Contains(txtSearch2.Text));
}

List<DataLayer.vwShared> bookList= new DataLayer.Solib_DMREntities().SP_SharedData_GetAll("AllLocal").ToList<DataLayer.vwShared>();

var bookList= from books in bookList.AsQueryable().Where(predicate)
                select books ;

gvBooks.DataSource = bookList.ToList();
gvBooks.DataBind();

The above code not return proper results. Is there something wrong. ? The following is my references website. http://www.albahari.com/nutshell/predicatebuilder.aspx Please give me advice.

Thanks.

Answer 1

step 1:Model

public class Person
{
    public int PersonId { get; set; }
    public int? Age { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int? Salary { get; set; }

}


public class PersonDBContext : DbContext
{
    public virtual DbSet<Person> People { get; set; }
}

step 2 : controller

public class PersonController
{

    PersonDBContext db = new PersonDBContext();

    public void Add(Person person)
    {
        db.People.Add(person);
        db.SaveChanges();
    }
    public List<Person> GetAll()
    {
        return db.People.ToList();
    }

    public List<Person> GetByPredicateValue(Expression<Func<Person, bool>> predicate)
    {
        if (predicate != null)
            return db.People.Where(predicate.Compile()).ToList();
        else
            return null;
    }

    public List<Person> GetByPredicateAndPersonListValue(List<Person> PeopleList,Expression<Func<Person, bool>> predicate)
    {
        if (predicate != null)
            return PeopleList.Where(predicate.Compile()).ToList();
        else
            return null;
    }


}

step 3: code behind

public partial class MainWindow : Window
    {
        PersonController pc;
        public static List<Person> PeopleFilterList = null;
        public static string CurrentColumn = null;
        public MainWindow()
        {

            pc = new PersonController();        
            InitializeComponent();
            grid.ItemsSource = pc.GetAll();
            grid.PreviewMouseDown += grid_PreviewMouseDown;
        }

        void grid_PreviewMouseDown(object sender, MouseButtonEventArgs e)
        {
            TableViewHitInfo hi = ((TableView)grid.View).CalcHitInfo(e.OriginalSource as DependencyObject);
            if (hi == null) return;
            if (hi.HitTest == TableViewHitTest.ColumnHeader || hi.HitTest == TableViewHitTest.ColumnHeaderFilterButton)
            {
                grid.UnselectAll();
                GridColumn currentColumn = ((DevExpress.Xpf.Grid.GridViewHitInfoBase)(hi)).Column;
                CurrentColumn = currentColumn.FieldName;
                (grid.View as TableView).SelectCells(0, currentColumn, grid.VisibleRowCount - 1, currentColumn);            
            }
        }

        Expression<Func<Person, bool>> _result=null;
        string str="";
        private void IdForm_ButtonClicked(object sender, IdentityUpdateEventArgs e)
        {
            _result = e.FirstName;
        }

        public MainWindow(Expression<Func<Person, bool>> result)
        {
            Window1 f = new Window1();
            f.IdentityUpdated += new Window1.IdentityUpdateHandler(IdForm_ButtonClicked);
            pc = new PersonController();
            InitializeComponent();                 
            _result = result;
            if (PeopleFilterList != null)
                PeopleFilterList = pc.GetByPredicateAndPersonListValue(PeopleFilterList,_result).ToList();
            else
                PeopleFilterList = pc.GetByPredicateValue(_result).ToList();
            grid.ItemsSource = PeopleFilterList;
            grid.PreviewMouseDown += grid_PreviewMouseDown;
        }


        private void Button_Click(object sender, RoutedEventArgs e)
        {
            Window1 w1 = new Window1(CurrentColumn);
            w1.Show();

        }

    }

step 4 : Filter code behind

 public partial class Window1 : Window
    {
        public delegate void IdentityUpdateHandler(object sender, IdentityUpdateEventArgs e);
        public event IdentityUpdateHandler IdentityUpdated;


        string _currentColumn = null;
        public Window1()
        {
            InitializeComponent();
        }

        public Window1(string currentColumn)
        {
            _currentColumn = currentColumn;
            InitializeComponent();
        }

        public static Expression<Func<Person, bool>> predicateValue;
        IdentityUpdateEventArgs args;
        private void Button_Click(object sender, RoutedEventArgs e)
        {

            string operatorName = "";


            if (!String.IsNullOrEmpty(txtSmaller.Text.Trim()))
                operatorName = txtSmaller.Name;
            else if (!String.IsNullOrEmpty(txtElder.Text.Trim()))
                operatorName = txtElder.Name;
            else if (!String.IsNullOrEmpty(txtContains.Text.Trim()))
                operatorName = txtContains.Name;
            else if (!String.IsNullOrEmpty(txtStartWith.Text.Trim()))
                operatorName = txtStartWith.Name;
            else if (!String.IsNullOrEmpty(txtEqual.Text.Trim()))
                operatorName = txtEqual.Name;
            else if (!String.IsNullOrEmpty(txtNotEqual.Text.Trim()))
                operatorName = txtNotEqual.Name;
            else if (!String.IsNullOrEmpty(txtSmallerOrEqual.Text.Trim()))
                operatorName = txtSmallerOrEqual.Name;
            else if (!String.IsNullOrEmpty(txtElderOrEqual.Text.Trim()))
                operatorName = txtElderOrEqual.Name;

            else if (!String.IsNullOrEmpty(txtSmallerThan.Text.Trim()) && !String.IsNullOrEmpty(txtBiggerThan.Text.Trim()))
                operatorName = txtSmallerThan.Name;


            switch (operatorName)
            {
                case "txtSmaller":          
                    predicateValue = (x => (int)x.GetType().GetProperty(_currentColumn).GetValue(x, null) < Convert.ToInt32(txtSmaller.Text));
                    break;

                case "txtElder":
                    predicateValue = (x => (int)x.GetType().GetProperty(_currentColumn).GetValue(x, null) > Convert.ToInt32(txtElder.Text));
                    break;

                case "txtSmallerOrEqual":
                    predicateValue = (x => (int)x.GetType().GetProperty(_currentColumn).GetValue(x, null) <= Convert.ToInt32(txtSmallerOrEqual.Text));
                    break;

                case "txtElderOrEqual":
                    predicateValue = (x => (int)x.GetType().GetProperty(_currentColumn).GetValue(x, null) >= Convert.ToInt32(txtElderOrEqual.Text));
                    break;

                case "txtEqual":
                    predicateValue = (x => (int)x.GetType().GetProperty(_currentColumn).GetValue(x, null) == Convert.ToInt32(txtEqual.Text));
                    break;

                case "txtNotEqual":
                    predicateValue = (x => (int)x.GetType().GetProperty(_currentColumn).GetValue(x, null) != Convert.ToInt32(txtNotEqual.Text));
                    break;

                case "txtSmallerThan":
                    predicateValue = (x => (int)x.GetType().GetProperty(_currentColumn).GetValue(x, null) >= Convert.ToInt32(txtBiggerThan.Text)
                    && (int)x.GetType().GetProperty(_currentColumn).GetValue(x, null) <= Convert.ToInt32(txtSmallerThan.Text));
                    break;

                case "txtStartWith":
                    predicateValue = (x => x.GetType().GetProperty(_currentColumn).GetValue(x, null).ToString().StartsWith(txtStartWith.Text));      
                    break;

                case "txtContains":
                    predicateValue = (x => x.GetType().GetProperty(_currentColumn).GetValue(x,null).ToString().Contains(txtContains.Text));                    
                    break;                  
            }




            MainWindow mw = new MainWindow(predicateValue);
            mw.Show();
            this.Close();
        }

        void Window1_IdentityUpdated(object sender, IdentityUpdateEventArgs e)
        {
            e = args;
        }

    }

    public class IdentityUpdateEventArgs : System.EventArgs
    {
        private Expression<Func<Person, bool>> mFirstName;

        public IdentityUpdateEventArgs(Expression<Func<Person, bool>> sFirstName)
        {
            this.mFirstName = sFirstName;
        }

        public Expression<Func<Person, bool>> FirstName
        {
            get
            {
                return mFirstName;
            }
        }    

    }

Answer 2

Answering your concrete question. The problem is in the branch for building OR predicate, in which case you should start with PredicateBuilder.False, otherwice there will not be filtering at all (as we know from the school, true or something is always true :)

// ...
else if (joinOperator == "OR")
{
    predicate = PredicateBuilder.False<DataLayer.vwShared>();
    // ...
}
// ...