Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 737
sandesh247 Avatar asked Oct 24 '08 17:10

sandesh247


2 Answers

@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.

like image 136
Aaron Powell Avatar answered Oct 19 '22 16:10

Aaron Powell


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.

like image 44
James Curran Avatar answered Oct 19 '22 16:10

James Curran