IQueryable: Creating dynamically an OR filtering

Question

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&lt;T&gt;( result );

}

SDReyes

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.

Tomas Petricek

View more on Stack Overflow

SDReyes · Accepted Answer · 5/23/2017 12:13:38 PM

Accepted Answer

In terms of performance and ease of implementation how is this a better approach than using the Dynamic Query Library? I believe this way you have better control over the SQL output of your expression trees.

By RaÃºl Roa

SDReyes

IQueryable: Creating dynamically an OR filtering

Question

BONUS Our solution:

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator