IQueryable: Creating an OR filter dynamically

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

}
1
3
9/14/2010 8:50:31 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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

-1
5/23/2017 12:13:38 PM

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

Related Questions

How can I build an ExpressionFuncMyClass, bool>> predicate dynamically?

How would I go about using an Expression Tree to dynamically create a predicate that looks something like......(p.Length== 5) && (p.SomeOtherProperty == "hello") ...So that I can stick the predicate into a lambda expression like so......q.Where(myDynamicExpression)... ...I just need to be pointed in the right direction....Update:... Sorry folks, I...
c# expression-trees lambda linq
asked by Senkwe

Create a completely dynamic where clause using an expression tree and run it via IQueryable.

This is quite hard to look at in isolation, but the first thing that occurs is that ...Compile... looks out of place for ...IQueryable... - that will ...rarely... work (LINQ-to-Objects being the exception)....An equivalent to ...WhereClause.Invoke(C.ClientID)... is to use ...Expression.Invoke... to call a sub-expression, but even this is flakey: LI...
c# expression-trees iqueryable lambda where
asked by Marc Gravell

How can I build an ExpressionFuncMyClass, bool>> predicate dynamically from an ExpressionFuncMyClass, string>> predicate?

I trying to append where predicates and my goal is to create the same expression as:...Services.Where(s => s.Name == "Modules" && s.Namespace == "Namespace"); ...I have the following code:...Expression<Func<Service,string>> sel1 = s => s.Name; Expression<Func<Service,string>> sel2 = s => s.Namespace; var val1 = Expression.Constant("Modules"); var ...
c# expression-trees linq linq-expressions
asked by Torbjörn Hansson

WCF DataContract class generated dynamically

Have you considered implementing a ...WCF Data Service...?
c# dynamic-linq expression-trees linq wcf
asked by Vedran

Dynamically creating delegates with parameter names

Hi I'm trying to create a function that dynamically creates a delegate with the same return value and the same parameters as a MethodInfo it receives as parameter and also and this is very important the same parameter names!...What I did so far is create a function that returns a lambda that receives the same parameter types and has the same return...
c# delegates dynamic expression-trees parameters
asked by Uchiha Madara

Apply a dynamically created lambda to an object instance

I have some code which dynamically create a lambda starting from strings. For example, I have a filter class like the following:...public class Criteria { public string Property { get; set; } public string Operator { get; set; } public string Value { get; set; } } ...And I am able to create a lambda like ...x => x.Name == "Foo"... start...
c# expression expression-trees lambda
asked by Lorenzo

Dynamically creating an expression which selects an objects property

Doing what you asked is bit tricky but not impossible. Since the property type is not known until run time so you can not declare the ...Expression<Func<,>>... so it would be done by reflection. ...public static class QueryableExtension { public static object Build<Tobject>(this Tobject source, string propertyName) { var propInfo = ...
c# expression-trees lambda
asked by vendettamit

Dynamically create a class by interface

I have some expirience with ....Net Expressions..., when I'm able to dynamically generate methods. It's fine, it's good....But now I need to generate a whole class, and it seems that the only way to do it is Emit whole IL which is totally inacceptable (it's impossible to support)....Assume we have following interface:...public interface IFoo { ...
.net c# code-generation expression-trees reflection
asked by Alex Zhukovskiy

Filtering non-generic DbSet with dynamically built Expression

Problem has been solved. Solution was quite obvious. Instead of...var query = dbContext.Set(entityType).Where(lambda); ...I can write...var query = dbContext .Set(entityType) .ProjectTo<T>(mapper.ConfigurationProvider) .Where(predicate); ...where ...predicate... is the input parameter of FindBy() meth...
dbset entity-framework expression-trees generics linq
asked by Andrey Weber

'Argument expression is not valid' when creating anonymous types dynamically

This is because ...call... returns dynamically created ...ReturnType... not ...IdName... thus the exception. Additionally you cannot put such dynamic types like ...ReturnType... as generic type parameters because compiler knows nothing about them so you should use ...dynamic... instead so the type will be resolved at runtime:...var qResult = allCar...
expression-trees linq typebuilder
asked by arekzyla

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow