IQueryable: Creating dynamically an OR filtering
c# expression-trees iqueryable linq linq-to-entities
Question
I have a set of search criterias in this form:
member | value | operator
--------+---------+---------
height | 10 | >
height | 2 | <
name | Carl | ==
And I want to query all the objects that match any of these criterias.
Right now, I'm doing it by:
- building an expression for each one of the criterias
- concatenating every expression using an 'OR' expression
- building a lambda expression containing the concatenated expression
- passing the lambda expression to the IQueryable<>.Where() method
Do you know a easiest way to filter dinamycally an IQueryable collection using consecutive OR?
BONUS Our solution:
Based on IlyaBuiluk solution @ CodeProject
// The structure used by the new extension method
public struct SearchCriteria
{
public string Column;
public object Value;
public WhereOperation Operation;
}
// How to convert the rules structure to the search criteria structure
var searchCriterias = grid.Where.rules.Select(Rule => new SearchCriteria
{
Column = Rule.field,
Operation =
(WhereOperation)
StringEnum.Parse(
typeof (WhereOperation),
Rule.op),
Value = Rule.data
}).ToArray();
// Usage:
query = query.WhereOr(searchCriterias);
// Implementation
public static IQueryable<T> WhereOr<T>( this IQueryable<T> Query, SearchCriteria [ ] Criterias )
{
if( Criterias.Count( ) == 0 )
return Query;
LambdaExpression lambda;
Expression resultCondition = null;
// Create a member expression pointing to given column
ParameterExpression parameter = Expression.Parameter( Query.ElementType, "p" );
foreach( var searchCriteria in Criterias )
{
if( string.IsNullOrEmpty( searchCriteria.Column ) )
continue;
MemberExpression memberAccess = null;
foreach( var property in searchCriteria.Column.Split( '.' ) )
memberAccess = MemberExpression.Property
( memberAccess ?? ( parameter as Expression ), property );
// Change the type of the parameter 'value'. it is necessary for comparisons (specially for booleans)
ConstantExpression filter = Expression.Constant
(
Convert.ChangeType( searchCriteria.Value, memberAccess.Type )
);
//switch operation
Expression condition = null;
switch( searchCriteria.Operation )
{
//equal ==
case WhereOperation.Equal:
condition = Expression.Equal( memberAccess, filter );
break;
//not equal !=
case WhereOperation.NotEqual:
condition = Expression.NotEqual( memberAccess, filter );
break;
// Greater
case WhereOperation.Greater:
condition = Expression.GreaterThan( memberAccess, filter );
break;
// Greater or equal
case WhereOperation.GreaterOrEqual:
condition = Expression.GreaterThanOrEqual( memberAccess, filter );
break;
// Less
case WhereOperation.Less:
condition = Expression.LessThan( memberAccess, filter );
break;
// Less or equal
case WhereOperation.LessEqual:
condition = Expression.LessThanOrEqual( memberAccess, filter );
break;
//string.Contains()
case WhereOperation.Contains:
condition = Expression.Call( memberAccess,
typeof( string ).GetMethod( "Contains" ),
Expression.Constant( searchCriteria.Value ) );
break;
default:
continue;
}
resultCondition = resultCondition != null ? Expression.Or( resultCondition, condition ): condition;
}
lambda = Expression.Lambda( resultCondition, parameter );
MethodCallExpression result = Expression.Call(
typeof( Queryable ), "Where",
new [ ] { Query.ElementType },
Query.Expression,
lambda );
return Query.Provider.CreateQuery<T>( result );
}
Popular Answer
If you have a fixed set of operators and a fixed set of members, then you can write this almost without dealing with expression trees directly. The idea is to create simple lambda expressions for various pieces of code (e.g. Expression<Func<Entity, string>> for reading property of a member and similar for operators) and then just compose them to build an expression tree. I described the solution here. The only problem is that composing expressions isn't directly supported in C#, so you need a bit of pre-processing (see the section about "expandable utilites").
Then you can store basic functions in a dictionary and select the right one (or a combination of them) based on what the user selects. For example something like:
NorthwindDataContext db = new NorthwindDataContext();
// A query that tests whether a property
// (specified by 'selector' matches a string value
var queryBuilder = Linq.Func
((Expression<Func<Customer, string>> selector, string val) =>
from c in db.Customers.ToExpandable()
where selector.Expand(c).IndexOf(val) != -1
select c);
// Dictionary with supported members...
var dict = new Dictionary<string, Expression<Func<Customer, string>>>
{ { "CompanyName", c => c.CompanyName },
{ "Country", c => c.Country },
{ "ContactName", c => c.ContactName } };
// Ask user for a property name & value and Build the query
string field = Console.ReadLine();
string value = Console.ReadLine();
var q = queryBuilder(dict[field], value);
The article also contains an example with composing OR or AND conditions dynamically. I didn't update the code for a while, so it needs some work, but I believe that LINQ KIT project contains a version of this idea too.