NHibernate Linq provider: dynamic filtering using lambda expressions

Print Content | More

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 A bocca aperta.



NHibernate, Linq

4 comments

Related Post

  1. #1 da Calin - Sunday November 2010 alle 09:27

    Hi,

    Thanks for sharing, I was looking for this for a wile now.

  2. #2 da Ilan Galini - Monday January 2011 alle 12:27

    The parameter modifier is problematic when nested lambdas are present. It renames the parameter name of the inner lambda and thus changing the meaning of the expression and in the case of different parameter types even throws an exception.

    I have altered the VisitParameter method to handle the case of different parameter types but it's still not the ideal solution. It helped me with what I was stuck with now but eventually I will have to look for a more elegant solution that "resets" the parameter modifier when a nested lambda is detected.

    Anyway, my modified code is like this:
    protected override Expression VisitParameter(ParameterExpression p)
    {
    if (p.Type == _newParam.Type)
    return _newParam;
    else
    return p;
    }

  3. #3 da Fabian Schmied - Monday January 2011 alle 09:22

    I've posted a few comments about your code (including two bugs) to http://groups.google.com/group/nhusers/browse_thread/thread/9eda0a23bd4f59d1 .

    I'm not able to post the full code here (get an ASP.NET error), but suggested fixes can be found here: http://groups.google.com/group/nhusers/msg/a13a67bb48028c8c .

  4. #4 da alessandro giorgetti - Monday January 2011 alle 10:14

    Thank you guys for your feedback and suggestions, I'm going to check them and apply them to my code.

All fields are required and you must provide valid data in order to be able to comment on this post.


(will not be published)
(es: http://www.mysite.com)


  1. #1 da http://www.alvinashcraft.com/2010/10/21/dew-drop-october-21-2010/

    Dew Drop &ndash; October 21, 2010 | Alvin Ashcraft&#039;s Morning Dew