Learn 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 impl...
https://www.czetsuyatech.com/2011/01/c-dynamic-and-or-ef.html
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.
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;
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 websiteclass 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 abovepublic 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 abovepublic 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;
Post a Comment