LINQ, Large filter on a query

Question

I am building a section of an application that revolves around pulling information about transactions out of the database. Due to the nature of the data, there are many columns in the table that I want to filter on. I have a filter selection box with 15 fields that I want to be able to build up a where clause for the LINQ statement. The interesting part comes when I want certain fields to be null. For example I want to be able to filter on any or all of:

• Transaction Type
• Response Code
• Transaction Amount
• Many more

I can build up a predicate that looks like

Func<Transaction, bool> pred = t => t.ResponseCode == ResponseCode && t.TransactionType == TransactionType && t.TransactionAmount > 100.00;

But in order to be able to choose which fields to include in the predicate I am concatenating the predicates together:

Func<Transaction, bool> pred = t => true;
if(ResponseCode != null)
   pred.AndAlso(t => t.ResponseCode == ResponseCode);
// Rinse and repeat

And then passing that predicate to the where clause of the LINQ statement.

This works exactly the way I want it, but is rather complicated. Are there any other ways of doing this?

UPDATE: Thanks Justice for the comments. I'm not using LINQ to SQL, I'm using LINQ on a collection of objects from a repository. How would you programatically build an Expression filter?

Answer 1

First, you would need to use Expression<Func<Transaction, bool>> for LINQ-to-SQL (that's what you're trying to use, and it's not the same thing as LINQ).

Second, you can programmatically build up an Expression<Func<Transaction, bool>> using the System.Linq.Expression namespace.

You will not be able to use LINQ per se to query the database using programmatically built-up expressions. Instead of using the query operators, you will need to use the query extension methods: for example, instead of from p in db.People where p.Age > 50 select p.Name you will need to use db.People.Where(p => p.Age > 50). You can use this style to add filters: db.People.Where(myFilter), where myFilter = new Expression<Func<Person, bool>>(p => p.Age > 50). In your case, myFilter would be your programmatically built-up filter, not one created using lambda-expression syntax.

Answer 2

• In dynamic SQL... Since you only have one WHERE clause - you must concatenate predicates with AND.
• In linq query construction... you get as many WHERE clauses as you want. Linq will AND them together for you when it translates the query.

Example:

IQueryable<Transaction> query = db.Transactions;

if (filterByTransactionType)
{
  query = query.Where(t => t.TransactionType == theTransactionType);
}
if (filterByResponseCode)
{
  query = query.Where(t => t.ResponseCode == theResponseCode);
}
if (filterByAmount)
{
  query = query.Where(t => t.TransactionAmount > theAmount);
}

Another Example:

List<Expression<Func<Transaction, bool>>> filters = GetFilterExpressions();

IQueryable<Transaction> query = db.Transactions;
filters.ForEach(f => query = query.Where(f));