Looking for a JavaEE Architect or Potential Tech Co-Founder?
Please don't hesitate to contact me.

Dynamic AND OR statements in Entity Framework

What if your where clause is dynamic meaning certain fields only gets filtered when a certain condition is satisfied?

The most simply implementation of AND, OR without condition is:

entity.Model.Where(p=>p.FieldA==1 AND/OR p.FieldB==2);

In sql it's:
SELECT * FROM Model WHERE FieldA=1 AND/OR FieldB=2

What if FieldB only get's filtered when a flag is true? Now that's the problem. I've been reading the documentation and come up with Predicates and fortunately there are several noteworthy links on the internet specially these 2:

http://blogs.msdn.com/b/meek/archive/2008/05/02/linq-to-entities-combining-predicates.aspx
http://stackoverflow.com/questions/110314/linq-to-entities-building-where-clauses-to-test-collections-within-a-many-to-ma#131551

What I will explain afterwards come from the 2 links above. I just have my own personal implementation.

To proceed you need:
1.) The ExpressionVisitor because it's sealed on dotnet, I got on the internet hmmm I've lost the link. Well :-D, it's too long to post but I'm posting it here until I found the link.
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq.Expressions;

namespace ErisRegistration.Helper
{
    public abstract class ExpressionVisitor
    {
        protected ExpressionVisitor()
        {

        }

        protected virtual Expression Visit(Expression exp)
        {

            if (exp == null)

                return exp;

            switch (exp.NodeType)
            {

                case ExpressionType.Negate:

                case ExpressionType.NegateChecked:

                case ExpressionType.Not:

                case ExpressionType.Convert:

                case ExpressionType.ConvertChecked:

                case ExpressionType.ArrayLength:

                case ExpressionType.Quote:

                case ExpressionType.TypeAs:

                    return this.VisitUnary((UnaryExpression)exp);

                case ExpressionType.Add:

                case ExpressionType.AddChecked:

                case ExpressionType.Subtract:

                case ExpressionType.SubtractChecked:

                case ExpressionType.Multiply:

                case ExpressionType.MultiplyChecked:

                case ExpressionType.Divide:

                case ExpressionType.Modulo:

                case ExpressionType.And:

                case ExpressionType.AndAlso:

                case ExpressionType.Or:

                case ExpressionType.OrElse:

                case ExpressionType.LessThan:

                case ExpressionType.LessThanOrEqual:

                case ExpressionType.GreaterThan:

                case ExpressionType.GreaterThanOrEqual:

                case ExpressionType.Equal:

                case ExpressionType.NotEqual:

                case ExpressionType.Coalesce:

                case ExpressionType.ArrayIndex:

                case ExpressionType.RightShift:

                case ExpressionType.LeftShift:

                case ExpressionType.ExclusiveOr:

                    return this.VisitBinary((BinaryExpression)exp);

                case ExpressionType.TypeIs:

                    return this.VisitTypeIs((TypeBinaryExpression)exp);

                case ExpressionType.Conditional:

                    return this.VisitConditional((ConditionalExpression)exp);

                case ExpressionType.Constant:

                    return this.VisitConstant((ConstantExpression)exp);

                case ExpressionType.Parameter:

                    return this.VisitParameter((ParameterExpression)exp);

                case ExpressionType.MemberAccess:

                    return this.VisitMemberAccess((MemberExpression)exp);

                case ExpressionType.Call:

                    return this.VisitMethodCall((MethodCallExpression)exp);

                case ExpressionType.Lambda:

                    return this.VisitLambda((LambdaExpression)exp);

                case ExpressionType.New:

                    return this.VisitNew((NewExpression)exp);

                case ExpressionType.NewArrayInit:

                case ExpressionType.NewArrayBounds:

                    return this.VisitNewArray((NewArrayExpression)exp);

                case ExpressionType.Invoke:

                    return this.VisitInvocation((InvocationExpression)exp);

                case ExpressionType.MemberInit:

                    return this.VisitMemberInit((MemberInitExpression)exp);

                case ExpressionType.ListInit:

                    return this.VisitListInit((ListInitExpression)exp);

                default:

                    throw new Exception(string.Format("Unhandled expression type: '{0}'", exp.NodeType));

            }

        }



        protected virtual MemberBinding VisitBinding(MemberBinding binding)
        {

            switch (binding.BindingType)
            {

                case MemberBindingType.Assignment:

                    return this.VisitMemberAssignment((MemberAssignment)binding);

                case MemberBindingType.MemberBinding:

                    return this.VisitMemberMemberBinding((MemberMemberBinding)binding);

                case MemberBindingType.ListBinding:

                    return this.VisitMemberListBinding((MemberListBinding)binding);

                default:

                    throw new Exception(string.Format("Unhandled binding type '{0}'", binding.BindingType));

            }

        }



        protected virtual ElementInit VisitElementInitializer(ElementInit initializer)
        {

            ReadOnlyCollection<Expression> arguments = this.VisitExpressionList(initializer.Arguments);

            if (arguments != initializer.Arguments)
            {

                return Expression.ElementInit(initializer.AddMethod, arguments);

            }

            return initializer;

        }



        protected virtual Expression VisitUnary(UnaryExpression u)
        {

            Expression operand = this.Visit(u.Operand);

            if (operand != u.Operand)
            {

                return Expression.MakeUnary(u.NodeType, operand, u.Type, u.Method);

            }

            return u;

        }



        protected virtual Expression VisitBinary(BinaryExpression b)
        {

            Expression left = this.Visit(b.Left);

            Expression right = this.Visit(b.Right);

            Expression conversion = this.Visit(b.Conversion);

            if (left != b.Left || right != b.Right || conversion != b.Conversion)
            {

                if (b.NodeType == ExpressionType.Coalesce && b.Conversion != null)

                    return Expression.Coalesce(left, right, conversion as LambdaExpression);

                else

                    return Expression.MakeBinary(b.NodeType, left, right, b.IsLiftedToNull, b.Method);

            }

            return b;

        }



        protected virtual Expression VisitTypeIs(TypeBinaryExpression b)
        {

            Expression expr = this.Visit(b.Expression);

            if (expr != b.Expression)
            {

                return Expression.TypeIs(expr, b.TypeOperand);

            }

            return b;

        }



        protected virtual Expression VisitConstant(ConstantExpression c)
        {

            return c;

        }



        protected virtual Expression VisitConditional(ConditionalExpression c)
        {

            Expression test = this.Visit(c.Test);

            Expression ifTrue = this.Visit(c.IfTrue);

            Expression ifFalse = this.Visit(c.IfFalse);

            if (test != c.Test || ifTrue != c.IfTrue || ifFalse != c.IfFalse)
            {

                return Expression.Condition(test, ifTrue, ifFalse);

            }

            return c;

        }



        protected virtual Expression VisitParameter(ParameterExpression p)
        {

            return p;

        }



        protected virtual Expression VisitMemberAccess(MemberExpression m)
        {

            Expression exp = this.Visit(m.Expression);

            if (exp != m.Expression)
            {

                return Expression.MakeMemberAccess(exp, m.Member);

            }

            return m;

        }



        protected virtual Expression VisitMethodCall(MethodCallExpression m)
        {

            Expression obj = this.Visit(m.Object);

            IEnumerable<Expression> args = this.VisitExpressionList(m.Arguments);

            if (obj != m.Object || args != m.Arguments)
            {

                return Expression.Call(obj, m.Method, args);

            }

            return m;

        }



        protected virtual ReadOnlyCollection<Expression> VisitExpressionList(ReadOnlyCollection<Expression> original)
        {

            List<Expression> list = null;

            for (int i = 0, n = original.Count; i < n; i++)
            {

                Expression p = this.Visit(original[i]);

                if (list != null)
                {

                    list.Add(p);

                }

                else if (p != original[i])
                {

                    list = new List<Expression>(n);

                    for (int j = 0; j < i; j++)
                    {

                        list.Add(original[j]);

                    }

                    list.Add(p);

                }

            }

            if (list != null)
            {

                return list.AsReadOnly();

            }

            return original;

        }



        protected virtual MemberAssignment VisitMemberAssignment(MemberAssignment assignment)
        {

            Expression e = this.Visit(assignment.Expression);

            if (e != assignment.Expression)
            {

                return Expression.Bind(assignment.Member, e);

            }

            return assignment;

        }



        protected virtual MemberMemberBinding VisitMemberMemberBinding(MemberMemberBinding binding)
        {

            IEnumerable<MemberBinding> bindings = this.VisitBindingList(binding.Bindings);

            if (bindings != binding.Bindings)
            {

                return Expression.MemberBind(binding.Member, bindings);

            }

            return binding;

        }



        protected virtual MemberListBinding VisitMemberListBinding(MemberListBinding binding)
        {

            IEnumerable<ElementInit> initializers = this.VisitElementInitializerList(binding.Initializers);

            if (initializers != binding.Initializers)
            {

                return Expression.ListBind(binding.Member, initializers);

            }

            return binding;

        }



        protected virtual IEnumerable<MemberBinding> VisitBindingList(ReadOnlyCollection<MemberBinding> original)
        {

            List<MemberBinding> list = null;

            for (int i = 0, n = original.Count; i < n; i++)
            {

                MemberBinding b = this.VisitBinding(original[i]);

                if (list != null)
                {

                    list.Add(b);

                }

                else if (b != original[i])
                {

                    list = new List<MemberBinding>(n);

                    for (int j = 0; j < i; j++)
                    {

                        list.Add(original[j]);

                    }

                    list.Add(b);

                }

            }

            if (list != null)

                return list;

            return original;

        }



        protected virtual IEnumerable<ElementInit> VisitElementInitializerList(ReadOnlyCollection<ElementInit> original)
        {

            List<ElementInit> list = null;

            for (int i = 0, n = original.Count; i < n; i++)
            {

                ElementInit init = this.VisitElementInitializer(original[i]);

                if (list != null)
                {

                    list.Add(init);

                }

                else if (init != original[i])
                {

                    list = new List<ElementInit>(n);

                    for (int j = 0; j < i; j++)
                    {

                        list.Add(original[j]);

                    }

                    list.Add(init);

                }

            }

            if (list != null)

                return list;

            return original;

        }



        protected virtual Expression VisitLambda(LambdaExpression lambda)
        {

            Expression body = this.Visit(lambda.Body);

            if (body != lambda.Body)
            {

                return Expression.Lambda(lambda.Type, body, lambda.Parameters);

            }

            return lambda;

        }



        protected virtual NewExpression VisitNew(NewExpression nex)
        {

            IEnumerable<Expression> args = this.VisitExpressionList(nex.Arguments);

            if (args != nex.Arguments)
            {

                if (nex.Members != null)

                    return Expression.New(nex.Constructor, args, nex.Members);

                else

                    return Expression.New(nex.Constructor, args);

            }

            return nex;

        }



        protected virtual Expression VisitMemberInit(MemberInitExpression init)
        {

            NewExpression n = this.VisitNew(init.NewExpression);

            IEnumerable<MemberBinding> bindings = this.VisitBindingList(init.Bindings);

            if (n != init.NewExpression || bindings != init.Bindings)
            {

                return Expression.MemberInit(n, bindings);

            }

            return init;

        }



        protected virtual Expression VisitListInit(ListInitExpression init)
        {

            NewExpression n = this.VisitNew(init.NewExpression);

            IEnumerable<ElementInit> initializers = this.VisitElementInitializerList(init.Initializers);

            if (n != init.NewExpression || initializers != init.Initializers)
            {

                return Expression.ListInit(n, initializers);

            }

            return init;

        }



        protected virtual Expression VisitNewArray(NewArrayExpression na)
        {

            IEnumerable<Expression> exprs = this.VisitExpressionList(na.Expressions);

            if (exprs != na.Expressions)
            {

                if (na.NodeType == ExpressionType.NewArrayInit)
                {

                    return Expression.NewArrayInit(na.Type.GetElementType(), exprs);

                }

                else
                {

                    return Expression.NewArrayBounds(na.Type.GetElementType(), exprs);

                }

            }

            return na;

        }



        protected virtual Expression VisitInvocation(InvocationExpression iv)
        {

            IEnumerable<Expression> args = this.VisitExpressionList(iv.Arguments);

            Expression expr = this.Visit(iv.Expression);

            if (args != iv.Arguments || expr != iv.Expression)
            {

                return Expression.Invoke(expr, args);

            }

            return iv;

        }

    }
}
2.) ParameterRebinder, also from the above website
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);
        }
    }
3.) ExpressionExtension, from the link above
public static class ExpressionExtensions
    {
        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);
        }
    }
4.) And last the PredicateBuilder, from the link above
public class PredicateBuilder
    {
        public static Expression<Func<T, bool>> True<T>() { return f => true; }
        public static Expression<Func<T, bool>> False<T>() { return f => false; }
    }

Implementation:

IQueryable<MyModel> iQueryable = null;

Expression<Func<MyModel, bool>> predicate = PredicateBuilder.True<MyModel>();
predicate = predicate.And(p => p.Field1.Equals("valueOne"));
predicate = predicate.And(p => p.Field2.Equals("valueTwo"));

if (flag.CompareTo("All") != 0)
{
predicate.And(p => p.Field3.Contains(valueThree));
}
if (isbn.CompareTo("All") != 0)
{
predicate = predicate.And(p => p.Field4.Equals(valueFour));
}

//Actually you can create a sub statement: predicate AND (x=1 or b=2 or c=3)
//just create another predicate, example predicateAdditionalConstraints and add that to the original predicate
predicate = predicate.And(predicateAdditionalConstraints);

//execute the query
Dataset ds = iQueryable.Where(predicate) as DataSet;
Dynamic AND OR statements in Entity Framework Dynamic AND OR statements in Entity Framework Reviewed by Edward Legaspi on Friday, January 21, 2011 Rating: 5

No comments:

Powered by Blogger.