Working with the new NHibernate Linq provider I needed a simple way to dynamically buildup a ‘filtering’ lambda expression to be used as the where clause in a select operation; nothing you cannot already do with the ICriteria/QueryOver API or with HQL...but I thought it would be cool to have the same feature with the Linq provider.
This way I can maybe reuse part of the filtering expression for in memory elaborations, create my EQO easier uing Linq to NHibernate or I can change it on the fly in other layers of the application (maybe not the best practice when it comes to data access strategies, but nonetheless it’s a feature you can use in some sceneries)
Currently concatenating expressions using an ‘AND’ operator isn’t that difficult: all you have to do is to keep calling ‘Where(Func<T, TResult>())’ before your non deferred operators.
Let’s see an example:
Dal.List<PersonaleReparto>().Where(pr => pr.Name == "Max").Where(pr => pr.Attributes.IsLike("%M%")).ToList();which generates the following SQL:
select
personaler0_.Id as Id158_,
personaler0_.Name as Name158_,
personaler0_.Surname as Surname158_,
personaler0_.Title as Title158_,
personaler0_.BuiltIn as BuiltIn158_,
personaler0_.SortOrder as SortOrder158_,
personaler0_.Description as Descript7_158_,
personaler0_.Deleted as Deleted158_,
personaler0_.Attributes as Attributes158_
from
Neurologia.dbo.tbl_PersonaleReparto personaler0_
where
(
(
personaler0_.Name is null
)
and (
@p0 is null
)
or personaler0_.Name=@p0
)
and (
personaler0_.Attributes like @p1
);
@p0 = 'Max' [Type: String (4000)], @p1 = '%M%' [Type: String (4000)]
The problem comes if we want to express some of our conditions as ‘OR’ or some other operators; we cannot do it easily! What we can do is to play around with our Expression Trees and buildup a proper function that we’ll use in the where clause. In short we want to be able to do something like this:
// Create a filtering predicate
Expression<Func<PersonaleReparto, bool>> predicate = pr => !pr.Deleted && pr.Attributes.IsLike("%M%");
// use our PredicateBuilder function to add some more filetring expressions...your complex logic goes here!
// AND
predicate = predicate.And(pr => pr.Attributes.IsLike("%C%"));
// OR
predicate = predicate.Or(p => p.Id == 0);
var result = Dal.List<PersonaleReparto>().Where(predicate).OrderBy(pr => pr.SortOrder).ToList();We define a basic predicate and we keep concatenating filtering expressions using our custom defined AND and OR extension methods.
In this proof of concept we’re going to implement only the And and Or operators.
The basic idea is quite simple:
- The Where() extension method accept a Func<T, TResult> as its argument, so we will always deal with those type of functions.
- To be able to concatenate two (or more) filtering functions we need to act on the function bodies, the argument will always be the same.
- Our ‘And’ and ‘Or’ operators are binary operators that taken two lambda expressions (expr1, expr2) generate a new expression which is the concatenation of the two - expr1.body AND/OR expr2.body, the argument of this new function will be the argument of the starting predicate.
- Expressions are immutable! We might need to ‘adapt’ the second expression to make the arguments coherent before merging the bodies, we do this visiting the second expression (if needed) and creating a new expression replacing the argument.
- The And() and Or() will be implemented as extension methods that act on Expression<Func<T, TResult>> arguments.
We can write the ‘Or’ extension method like this:
public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
Expression<Func<T, bool>> expr2)
{
// create a new expression tree that replace the argument of the second expression with the argument on the first one
LambdaExpression lambdaExpression = RebuildExpressionIfNeeded(expr1.Parameters[0], expr2);
return Expression.Lambda<Func<T, bool>>
(Expression.OrElse(expr1.Body, lambdaExpression.Body), expr1.Parameters);
}In line 5 we rebuild a new expression if needed (more on this second).
In line 7 we build a new lambda function that is based on the bodies of the two function arguments and accept as its own argument the argument of the first function expression.
The tricky part is the RebuildExpressionIfNeeded function:
/// <summary>
/// Rebuilds the expression if we need to change the parameter of the second expression because of different names
/// </summary>
/// <param name="p">The parameter of the first expression.</param>
/// <param name="expr2">The expr2.</param>
/// <returns></returns>
private static LambdaExpression RebuildExpressionIfNeeded<T>(ParameterExpression p, Expression<Func<T, bool>> expr2)
{
LambdaExpression lambdaExpression;
if (p.Name != expr2.Parameters[0].Name)
{
Expression expression = (new ParameterModifier()).Modify(expr2, p);
lambdaExpression = ((LambdaExpression)expression);
}
else
lambdaExpression = expr2;
return lambdaExpression;
}If the parameters have the same names we have nothing to do, and we can safely return expr2, otherwise we need to visit the whole expression and create a duplicate replacing the parameter with the one we got from expr1. Writing a full expression tree visitor takes a lot of time, thankfully we have a working implementation in this MSDN article: How to: Implement an Expression Tree Visitor.
All we have to do is inherit from this class and override the VisitParameter() function to return the the parameter we passed in instead of the original one:
internal class ParameterModifier : ExpressionVisitor
{
public Expression Modify(Expression expression, ParameterExpression newParam)
{
_newParam = newParam;
return Visit(expression);
}
private ParameterExpression _newParam;
protected override Expression VisitParameter(ParameterExpression p)
{
return _newParam;
}
}That’s all, if now look at the SQL generated for our example we see that everything work!
select
personaler0_.Id as Id158_,
personaler0_.Name as Name158_,
personaler0_.Surname as Surname158_,
personaler0_.Title as Title158_,
personaler0_.BuiltIn as BuiltIn158_,
personaler0_.SortOrder as SortOrder158_,
personaler0_.Description as Descript7_158_,
personaler0_.Deleted as Deleted158_,
personaler0_.Attributes as Attributes158_
from
Neurologia.dbo.tbl_PersonaleReparto personaler0_
where
not (personaler0_.Deleted=1)
and (
personaler0_.Attributes like @p0
)
and (
personaler0_.Attributes like @p1
)
or personaler0_.Id=@p2
order by
personaler0_.SortOrder asc;
@p0 = '%M%' [Type: String (4000)],
@p1 = '%C%' [Type: String (4000)],
@p2 = 0 [Type: Int32 (0)]
As a plus, this technique actually works with Linq to Objects too (as you noticed we didn’t used any NHibernate hidden magic here), so you can use it to dynamically filter you in memory structures too.
Oh... I was forgetting... here’s the full source code for the PredicateBuilder class:
/// <summary>
/// a class used to dynamically build lambda functions to be used as filters for Linq providers
/// </summary>
public static class PredicateBuilder
{
public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
Expression<Func<T, bool>> expr2)
{
// create a new expression tree that replace the argument of the second expression with the argument on the first one
LambdaExpression lambdaExpression = RebuildExpressionIfNeeded(expr1.Parameters[0], expr2);
return Expression.Lambda<Func<T, bool>>
(Expression.OrElse(expr1.Body, lambdaExpression.Body), expr1.Parameters);
}
public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,
Expression<Func<T, bool>> expr2)
{
// create a new expression tree that replace the argument of the second expression with the argument on the first one
LambdaExpression lambdaExpression = RebuildExpressionIfNeeded(expr1.Parameters[0], expr2);
return Expression.Lambda<Func<T, bool>>
(Expression.AndAlso(expr1.Body, lambdaExpression.Body), expr1.Parameters);
}
/// <summary>
/// Rebuilds the expression if we need to change the parameter of the second expression because of different names
/// </summary>
/// <param name="p">The parameter of the first expression.</param>
/// <param name="expr2">The expr2.</param>
/// <returns></returns>
private static LambdaExpression RebuildExpressionIfNeeded<T>(ParameterExpression p, Expression<Func<T, bool>> expr2)
{
LambdaExpression lambdaExpression;
if (p.Name != expr2.Parameters[0].Name)
{
Expression expression = (new ParameterModifier()).Modify(expr2, p);
lambdaExpression = ((LambdaExpression)expression);
}
else
lambdaExpression = expr2;
return lambdaExpression;
}
}Ouch..this post ended up being quite full of code 
.
Related Content
- NHibernate - Customize the Linq provider to call your user defined SQL functions (10/01/2010)
- NHibernate 3 - Extending the Linq Provider to fix some System.NotSupportedException (26/08/2015)
- Linq to NHibernate - String.Equals with StringComparison option (26/08/2015)
- Linq to NHibernate: how to query on a property without setter (26/08/2015)
- Breaking news: the First European NHibernate Day had been officially announced (26/08/2015)
- More related document (21)
