ORing LINQ Query, built using expression trees

Question

So, this is rather complicated.

I have a set of rules in a collection, a rule contains these three properties.

Field, Op, and Data (all strings)

So a rule might look like "State", "eq", "CA"

My general rules are that all rules are ANDed together. However, with the caveat that, if they have the same Field value, those all ORed. This allows us to say "State", "eq", "CA", OR "State", "eq", "TX" AND "FirstName", "eq", "John".

The issue is that my current way of applying rules won't work, because it just keeps building up the linq expression using each rule to make it more and more explicit.

var result = rules.Aggregate(_repository.All, (current, rule) => current.ExtendQuery(rule))

ExtendQuery is an extension method I wrote, it uses ExpressionTrees, to generate a new query, that applies the current rule to the passed in query. (effectively ANDing them all together)

Now it wouldn't be hard for me to modify the .Aggregate line to group the rules by Field, and then generate the a unique query for each Field, but then how do I get it to "OR" them together instead of "AND"?

And then with each of those queries, how would I "AND" them together? A Union?

ExtendQuery looks like this

    public static IQueryable<T> ExtendQuery<T>(this IQueryable<T> query, QueryableRequestMessage.WhereClause.Rule rule) where T : class
    {
        var parameter = Expression.Parameter(typeof(T), "x");
        Expression property = Expression.Property(parameter, rule.Field);
        var type = property.Type;

        ConstantExpression constant;
        if (type.IsEnum)
        {
            var enumeration = Enum.Parse(type, rule.Data);
            var intValue = (int)enumeration;
            constant = Expression.Constant(intValue);
            type = typeof(int);
            //Add "Id" by convention, this is all because enum support is lacking at this point in Entity Framework
            property = Expression.Property(parameter, rule.Field + "Id");
        }
        else if(type == typeof(DateTime))
        {
            constant = Expression.Constant(DateTime.ParseExact(rule.Data, "dd/MM/yyyy", CultureInfo.CurrentCulture));
        }
        else if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
        {
            //This will convert rule.Data to the baseType, not a nullable type (because that won't work)
            var converter = TypeDescriptor.GetConverter(type);
            var value = converter.ConvertFrom(rule.Data);
            constant = Expression.Constant(value);

            //We change the type of property to get converted to it's base type
            //This is because Expression.GreaterThanOrEqual can't compare a decimal with a Nullable<decimal>
            var baseType = type.GetTypeOfNullable();
            property = Expression.Convert(property, baseType);
        }
        else
        {
            constant = Expression.Constant(Convert.ChangeType(rule.Data, type));
        }

        switch (rule.Op)
        {
            case "eq": //Equals
            case "ne": //NotEquals
                {
                    var condition = rule.Op.Equals("eq")
                                        ? Expression.Equal(property, constant)
                                        : Expression.NotEqual(property, constant);
                    var lambda = Expression.Lambda(condition, parameter);
                    var call = Expression.Call(typeof(Queryable), "Where", new[] { query.ElementType }, query.Expression, lambda);
                    query = query.Provider.CreateQuery<T>(call);
                    break;
                }
            case "lt": //Less Than
                    query = type == typeof (String) 
                        ? QueryExpressionString(query, Expression.LessThan, type, property, constant, parameter)
                        : QueryExpression(query, Expression.LessThan, property, constant, parameter); break;
            case "le": //Less Than or Equal To
                query = type == typeof (String)
                        ? QueryExpressionString(query, Expression.LessThanOrEqual, type, property, constant, parameter)
                        : QueryExpression(query, Expression.LessThanOrEqual, property, constant, parameter); break;
            case "gt": //Greater Than
                query = type == typeof (String)
                        ? QueryExpressionString(query, Expression.GreaterThan, type, property, constant, parameter)
                        : QueryExpression(query, Expression.GreaterThan, property, constant, parameter); break;
            case "ge": //Greater Than or Equal To 
                query = type == typeof (String)
                        ? QueryExpressionString(query, Expression.GreaterThanOrEqual, type, property, constant, parameter)
                        : QueryExpression(query, Expression.GreaterThanOrEqual, property, constant, parameter); break;
            case "bw": //Begins With
            case "bn": //Does Not Begin With
                query = QueryMethod(query, rule, type, "StartsWith", property, constant, "bw", parameter); break;
            case "ew": //Ends With
            case "en": //Does Not End With
                query = QueryMethod(query, rule, type, "EndsWith", property, constant, "cn", parameter); break;
            case "cn": //Contains
            case "nc": //Does Not Contain
                query = QueryMethod(query, rule, type, "Contains", property, constant, "cn", parameter); break;
            case "nu": //TODO: Null
            case "nn": //TODO: Not Null
                break;
        }

        return query;
    }

    private static IQueryable<T> QueryExpression<T>(
        IQueryable<T> query,
        Func<Expression, Expression, BinaryExpression> expression,
        Expression property,
        Expression value,
        ParameterExpression parameter
    ) where T : class
    {
        var condition = expression(property, value);
        var lambda = Expression.Lambda(condition, parameter);
        var call = Expression.Call(typeof(Queryable), "Where", new[] { query.ElementType }, query.Expression, lambda);
        query = query.Provider.CreateQuery<T>(call);
        return query;
    }

    private static IQueryable<T> QueryExpressionString<T>(
        IQueryable<T> query,
        Func<Expression, Expression, BinaryExpression> expression,
        Type type, 
        Expression property, 
        Expression value, 
        ParameterExpression parameter)
    {
        var containsmethod = type.GetMethod("CompareTo", new[] { type });
        var callContains = Expression.Call(property, containsmethod, value);
        var call = expression(callContains, Expression.Constant(0, typeof(int)));
        return query.Where(Expression.Lambda<Func<T, bool>>(call, parameter));
    }

    private static IQueryable<T> QueryMethod<T>(
        IQueryable<T> query,
        QueryableRequestMessage.WhereClause.Rule rule,
        Type type,
        string methodName,
        Expression property,
        Expression value,
        string op,
        ParameterExpression parameter
    ) where T : class
    {
        var containsmethod = type.GetMethod(methodName, new[] { type });
        var call = Expression.Call(property, containsmethod, value);
        var expression = rule.Op.Equals(op)
                             ? Expression.Lambda<Func<T, bool>>(call, parameter)
                             : Expression.Lambda<Func<T, bool>>(Expression.IsFalse(call), parameter);
        query = query.Where(expression);
        return query;
    }

Have you considered using grouping by on the Field name, then checking if more than one exists to use an "OR"? If just one exists in the group then process normally? — Sam
– Sam, Commented Feb 11, 2013 at 17:54

Rafal · Accepted Answer · 2013-02-11 19:40:13Z

So it is quite easy actually.

Now your code generates one where for each rule and what you need is one where with a little bit complicated condition so some modifications to your code are in order:

private static Expression GetComparisonExpression(this Rule rule, ParameterExpression parameter)
    {
        Expression property = Expression.Property(parameter, rule.Field);
        ConstantExpression constant = Expression.Constant(4);

        /* the code that generates constant and does some other stuff */

        switch (rule.Op)
        {
            case "eq": //Equals
            case "ne": //NotEquals
                {
                    var condition = rule.Op.Equals("eq")
                                        ? Expression.Equal(property, constant)
                                        : Expression.NotEqual(property, constant);
                    return condition;
                }

            default:
                throw new NotImplementedException();
        }
    }

This is snippet of what is needed from your original code. This method will not wrap query but simply generate comparison expression on given parameter with whatever is in your rule.

Now starting with this statement that generates your query:

 var result = rules.Generate(_repository.All);

Generate method groups your rules by property name Field and for each group generates and also (this is simply && operator) condition expression:

(group1Comparision) && (group2Comparison) && so on


public static IQueryable<T> Generate<T>(this  IEnumerable<Rule> rules, IQueryable<T> query) where T : class
{
    if (rules.Count() == 0)
        return query;

    var groups = rules.GroupBy(x => x.Field).ToArray();

    var parameter = Expression.Parameter(typeof(T));
    var comparison = groups.First().GetComparisonForGroup(parameter);

    foreach (var group in groups.Skip(1))
    {
        var otherComparions = group.GetComparisonForGroup(parameter);
        comparison = Expression.AndAlso(comparison, otherComparions);
    }

    var lambda = Expression.Lambda(comparison, parameter);
    var call = Expression.Call(typeof(Queryable), "Where", new[] { query.ElementType }, query.Expression, lambda);
    return query.Provider.CreateQuery<T>(call);

}

Note that grouping by property name renders original order of rules irrelevant.

The last thing is to create comparison for groups so || operator:

public static Expression GetComparisonForGroup(this IEnumerable<Rule> group, ParameterExpression parameter)
{
        var comparison = group.Select((rule) => rule.GetComparisonExpression(parameter)).ToArray();

        return comparison.Skip(1).Aggregate(comparison.First(),
            (left, right) => Expression.OrElse(left, right));
}

So no external library is necessary, for given rules list:

var rules = new Rule[]
            {
                new Rule{ Field = "A", Data = "4", Op="ne"},
                new Rule{ Field = "B", Data = "4", Op="eq"},
                new Rule{ Field = "A", Data = "4", Op="eq"},
                new Rule{ Field = "C", Data = "4", Op="ne"},
                new Rule{ Field = "A", Data = "4", Op="eq"},
                new Rule{ Field = "C", Data = "4", Op="eq"},
            };

I generated such condition that is introduced to single Where call to your query:

($var1.A != 4 || $var1.A == 4 || $var1.A == 4) && $var1.B == 4 && ($var1.C != 4 || $var1.C == 4)

svick · Accepted Answer · 2013-02-11 19:18:16Z

You can use PredicateBuilder from LINQKit to do this. If you create an Expression for each rule, you can then use the And() and Or() methods to combine them (possibly using PredicateBuilder.True() and False() as base cases). Finally, call Expand(), so that the query is in a form understandable by your query provider.

Assuming you change ExtendQuery() to return an Expression and rename it to CreateRuleQuery(), your code could look like this:

static IQueryable<T> ApplyRules<T>(
    this IQueryable<T> source, IEnumerable<Rule> rules)
{
    var predicate = PredicateBuilder.True<T>();

    var groups = rules.GroupBy(r => r.Field);

    foreach (var group in groups)
    {
        var groupPredicate = PredicateBuilder.False<T>();

        foreach (var rule in group)
        {
            groupPredicate = groupPredicate.Or(CreateRuleQuery(rule));
        }

        predicate = predicate.And(groupPredicate);
    }

    return source.Where(predicate.Expand());
}

Usage would be something like:

IQueryable<Person> source = …;

IQueryable<Person> result = source.ApplyRules(rules);

If you use this on these rules:

Name, eq, Peter
Name, eq, Paul
Age, ge, 18

Then the body of the predicate will be (from the predicate's Debug View):

True && (False || $f.Name == "Peter" || $f.Name == "Paul") && (False || $f.Age >= 18)

All those Trues and Falses shouldn't be a problem, but you could get rid of them by making ApplyRules() somewhat more complicated.

svick · Accepted Answer · 2013-02-11 20:40:44Z

An alternative to LINQKit which avoids the need for .AsExpandable() is my ExpressionBuilder class below. It combines two Expression<Func<T,bool>> by using an And or an Or to create a new Expression and it does it using pure Expression code (no .Compile() or such-like). I can't remember the original sources for some of the ideas in this but would be happy to add them if someone knows them.

using System;
using System.Linq;
using System.Linq.Expressions;
using System.Collections.Generic;

public static class ExpressionBuilder
{
    public static Expression<Func<T, bool>> True<T>() { return f => true; }
    public static Expression<Func<T, bool>> False<T>() { return f => false; }

    public static Expression<T> Compose<T>(this Expression<T> first, 
         Expression<T> second, 
         Func<Expression, Expression, Expression> merge)
    {
        // build parameter map (from parameters of second to parameters of first)
        var map = first.Parameters
                     .Select((f, i) => new { f, s = second.Parameters[i] })
                     .ToDictionary(p => p.s, p => p.f);

        // replace parameters in the second lambda expression with parameters from 
        // the first
        var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);

        // apply composition of lambda expression bodies to parameters from 
        // the first expression 
        return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);
    }

    public static Expression<Func<T, bool>> And<T>(
        this Expression<Func<T, bool>> first,
        Expression<Func<T, bool>> second)
    {
        return first.Compose(second, Expression.And);
    }

    public static Expression<Func<T, bool>> Or<T>(
        this Expression<Func<T, bool>> first,
        Expression<Func<T, bool>> second)
    {
        return first.Compose(second, Expression.Or);
    }

    public class ParameterRebinder : ExpressionVisitor
    {
        private readonly Dictionary<ParameterExpression, ParameterExpression> map;

        public ParameterRebinder(
            Dictionary<ParameterExpression, 
            ParameterExpression> map)
        {
            this.map = map??new Dictionary<ParameterExpression,ParameterExpression>();
        }

        public static Expression ReplaceParameters(
            Dictionary<ParameterExpression, 
            ParameterExpression> map, 
            Expression exp)
        {
            return new ParameterRebinder(map).Visit(exp);
        }

        protected override Expression VisitParameter(ParameterExpression p)
        {
            ParameterExpression replacement;
            if (map.TryGetValue(p, out replacement))
            {
                p = replacement;
            }
            return base.VisitParameter(p);
        }
    }
}

Collectives™ on Stack Overflow

ORing LINQ Query, built using expression trees

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related