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

• 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));