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.

Leyba

I'd be interested to see your Expressions solution, any chance of adding to your answer? Cheers. – Euchre

See this question and my subsequent answer regarding dynamic LINQ queries – Milwaukee

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.

Entomophagous

Shouldnt the predicate be compiled? – Consequential

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.

Openeyed

Answer 3

You can turn your where clauses on and off using some logic expressions.

//Turn on all where clauses
bool ignoreFirstName = false;
bool ignoreLastName = false;;
bool ignoreAddress = false;

//Decide which WHERE clauses we are going to turn off because of something.
if(something)
    ignoreFirstName = true; 

//Create the query
var queryCustomers = from c in db.Customers 
    where (ignoreFirstName || (c.ContactFirstName.Contains("BlackListed")))
    where (ignoreLastName || (c.ContactLastName.Contains("BlackListed")))
    where (ignoreAddress || (c.Address.Contains("BlackListed"))
    select j;  

If ignoreFirstName is true in the query then the condition on the other side of the or statement will be ignored.

Paregmenon

Answer 4

Since this is not LINQ to Objects, but rather LINQ to SQL, you have no other alternative beside using either Expressions or a stored procedure.

Lange