Removing the WHERE clause in a Linq query

Question

I have a table of product information with many bit columns. This table can be queried from an interface which has a checkbox for each column. The checkboxes are grouped into several related groups.

For example, three of the columns describe the products suitability for various markets, being Automotive, Aviation and Marine.

If none of these checkboxes are checked, I would like the following SQL to be executed.

SELECT * FROM Products

if Automotive was checked the following SQL should be executed

SELECT * FROM Products WHERE Automotive = 1

and if more than one is checked I would like the options to be OR'd together

SELECT * FROM Products WHERE 
    Automotive = 1 
    OR 
    Aviation = 1

In good old C# & SQL I could achieve this logic by conditionally concating the SQL together but I'm having trouble producing the same logic with Linq.

My problem is how do I conditionally add the WHERE clause and the elements of it to my query.

I'd prefer to only have a single point where the query is executed so, if possible, I would like to avoid using C# if's to branch into different queries.

Answer 1

Having the values be an "Or" vs. an "And" makes this problem a bit more difficult. If it were "And" then the following should do fine

public IEnumerable<Products> GetQuery() {
  var query = db.GetTable<Products>();
  if ( automotiveBox.Checked ) {
    query = query.Where(x => x.Automotive == 1);
  }
  if ( aviation.Checked ) {
    query = query.Where(x => x.Aviation == 1);
  }
  return query
}

With it being an "Or" though I think your only option is to build up the expression tree by hand and use it as a Where clause.

Answer 2

If you're trying to "OR" statements together that check a single column, you can use Contains to accomplish this. Here's a good blog post showing how this works.

However, if you're trying to dynamically check different columns, this will not work. In that case, multiple statements will probably be required.

Answer 3

I would use a PredicateBuilder for this.

var query = db.GetTable<Products>().AsQueryable();

var predicate = PredicateBuilder.False<Products>();
if (automotive.Checked)
{
    predicate = predicate.Or( p => p.Automotive == 1 );
}
if (aviation.Checked) 
{
    predicate = predicate.Or( p => p.Aviation == 1 );
}
query = query.Where( predicate );