Dynamic where clause in LINQ - with column names available at runtime

Question

Disclaimer: I've solved the problem using Expressions from System.Linq.Expressions, but I'm still looking for a better/easier way.

Consider the following situation :

var query = 
    from c in db.Customers
    where (c.ContactFirstName.Contains("BlackListed") || 
           c.ContactLastName.Contains("BlackListed")  ||
           c.Address.Contains("BlackListed"))
    select c;

The columns/attributes that need to be checked against the blacklisted term are only available to me at runtime. How do I generate this dynamic where clause?

An additional complication is that the Queryable collection (db.Customers above) is typed to a Queryable of the base class of 'Customer' (say 'Person'), and therefore writing c.Address as above is not an option.

Answer 1

@Geoff has the best option, justing Dynamic LINQ.

If you want to go the way of building queries at runtime using Lambda though I'd recomment that you use the PredicateBuilder (http://www.albahari.com/nutshell/predicatebuilder.aspx) and have something such as this:

Expression<Fun<T,bool>> pred = null; //delcare the predicate to start with. Note - I don't know your type so I just used T 
if(blacklistFirstName){
  pred = p => p.ContactFirstName.Contains("Blacklisted");
}
if(blacklistLastName){
  if(pred == null){
    pred = p => p.ContactLastName.Contains("Blacklisted"); //if it doesn't exist just assign it
  }else{
    pred = pred.And(p => p.ContactLastName.Contains("Blacklisted"); //otherwise we add it as an And clause
  }
}

And so on for all the columns you want to include. When you get to your query you just need something like this:

var results = db.Customers.Where(pred).Select(c => c);

I've used this to do building of LINQ for searching where there are about 20 different options and it produces really good SQL.

Answer 2

var query = from C in db.Customers select c;

if (seachFirstName)
         query = query.Where(c=>c.ContactFirstname.Contains("Blacklisted"));

if (seachLastName)
         query = query.Where(c=>c.ContactLastname.Contains("Blacklisted"));

if (seachAddress)
         query = query.Where(c=>c.Address.Contains("Blacklisted"));

Note that they aren't mutually exclusive.