Tech-Today
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;
- Hibernate - Get Classes That Referenced A Given Entity
There are times when we want to list the entities that referenced (via foreign keys) a given entity x. For example, when deleting entity x that is being referenced, it will throw a generic ConstraintViolationException. Oftentimes, we need to display this...
- How To Implement A Lazydatamodel With Size In Primefaces
This solution is ideal if you have a list of entities or a named queries that return a list of entities. For example let's say we have an entity user and we want to return all the users with role=x. @NamedQueries({ @NamedQuery(name = "User.listUsersByRoles",...
- How To Implement An Image Streamer For Primefaces Graphicimage Component
This page provide code samples for using primefaces graphic image component. In this case we are assuming that the byte data of an image is stored in an entity and that entity is access by its id. See the param id in #1. The param id is read by the streamer...
- Caps Converter - Convert Uppercase To Lowercase To Normal Case To Capital Case And Vise Versas
Below are the list of tools I use in case converting, they are useful if you are on a printing business and your clients just type on whatever case they want :-) Notepad++ - converts uppercase -> lowercase and vice versa Microsoft word - a feature...
- How To Catch Closing Event Of A Console Application In C#
On application closing we certainly always need to perform a clean up. But on the case of a c# console application there is no clear way to do it. And this is what I came up, to import a function from kernel32.dll, which is SetConsoleCtrlHandler. I'll...
Tech-Today
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;2.) ParameterRebinder, also from the above website
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;
}
}
}
class ParameterRebinder : ExpressionVisitor3.) ExpressionExtension, from the link above
{
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);
}
}
public static class ExpressionExtensions4.) And last the PredicateBuilder, from the link above
{
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 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;
- Hibernate - Get Classes That Referenced A Given Entity
There are times when we want to list the entities that referenced (via foreign keys) a given entity x. For example, when deleting entity x that is being referenced, it will throw a generic ConstraintViolationException. Oftentimes, we need to display this...
- How To Implement A Lazydatamodel With Size In Primefaces
This solution is ideal if you have a list of entities or a named queries that return a list of entities. For example let's say we have an entity user and we want to return all the users with role=x. @NamedQueries({ @NamedQuery(name = "User.listUsersByRoles",...
- How To Implement An Image Streamer For Primefaces Graphicimage Component
This page provide code samples for using primefaces graphic image component. In this case we are assuming that the byte data of an image is stored in an entity and that entity is access by its id. See the param id in #1. The param id is read by the streamer...
- Caps Converter - Convert Uppercase To Lowercase To Normal Case To Capital Case And Vise Versas
Below are the list of tools I use in case converting, they are useful if you are on a printing business and your clients just type on whatever case they want :-) Notepad++ - converts uppercase -> lowercase and vice versa Microsoft word - a feature...
- How To Catch Closing Event Of A Console Application In C#
On application closing we certainly always need to perform a clean up. But on the case of a c# console application there is no clear way to do it. And this is what I came up, to import a function from kernel32.dll, which is SetConsoleCtrlHandler. I'll...