Conditional Multiple Fields Searching and Filtering in LINQ

Question

Assuming that we have the following table:

Person:
  PersonID,
  Name,
  Age,
  Gender

And we are providing a search function that allows users to search the table according to the name and/or the age.

The tricky part in writing the SQL ( or LINQ) query is that the users can choose to search for both field, or any one field, or no field. If he wants to search for all then he would just have to leave the textbox blank.

The logic to do this can be written as follows:

var p;
if(Name_TextBox=='')
{
   p=from row in person
        select row ;
}
else 
{
  p= from row in person
      where row.Name=Name_TextBox
        select row ;
}
// repeat the same for age

Now after a while the code gets very long and messy... any idea how to compress the above into a single query with no if-else? }

Answer 1

One alternative which I have used in SQL which could be implemented in Linq too is

var p = from p in Person
       where p.Name == Name_TextBox || Name_TextBox == String.Empty
       select p;

(Note that your 'linq' is using SQL syntax, which won't compile. Also you can't declare a var as you are doing without directly assigning a value)

Answer 2

Try code like this

       string personName = txtPersonName.Text;
       int personAge = Convert.ToInt32(txtAge.Text);
       var opportunites =  from p in this.DataContext.Persons
                            select new
                            {
                                p.PersonID,
                                p.Name,
                                p.Age,
                                p.Gender
                            };

        if (personsID != 0)
            opportunites = opportunites.Where(p => p.PersonID == personID);

        if (personName != string.Empty)
            opportunites = opportunites.Where(p => p.Name.StartsWith(personName));

        if (personAge != 0)
            opportunites = opportunites.Where(p => p.Age == personAge);

This will work fine. If personName is not given it will be not add to where, and if given then it will added.