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?



Answers
  • 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));
By : David B


You can also toggle this setting from the keyboard by Ctrl+~ (that's the tilde, the first key in th numbers row)

So, if you want to quickly see the formula in a cell and come back to the normal view, just press Ctrl+~ twice



Since there doesn’t appear to be an easy way to convert excel spreadsheets into ActionScript I suggest you view the formulas and try to solve them by hand if the amount of data isn’t to large or by writing a throw away script if a lot of data is required. This way you will understand the calculations and can implement them in ActionScript rather then relying on a wizard whose code you may or may not understand.

By : Jared


This video can help you solving your question :)
By: admin