Generic Linq to Entities filter method that accepts filter criteria and properties to be filtered

Question

I've looked into many generic linq filtering questions and their answers here in SO but none of them satisfy my needs so I thought I should create a question.

I've created many of what I call "filter provider" classes, one for each entity class in my model, to provide a simplistic search for my application. I didn't want to go into more advanced solutions like Lucene.Net because a basic filtering with matching score would suffice.

Inside each one of these provider classes there are multiple methods that will receive the filtering terms and query specific properties, returning a score for each match based on the relevance of the property. Most methods will filter multiple properties at once, but not all.

Here are two of these methods:

private IQueryable<Retailer> MatchHighRelevanceFields(string searchTerm, IQueryable<Retailer> retailers)
{
    var results = retailers.Where(r =>
        (r.CompanyName != null && r.CompanyName.ToUpper().Contains(searchTerm))
        || (r.TradingName != null && r.TradingName.ToUpper().Contains(searchTerm))
    );

    return results;
}

private IQueryable<Retailer> MatchMediumRelevanceFields(string searchTerm, IQueryable<Retailer> retailers)
{
    var results = retailers.Where(r =>
        (r.Address.Street != null && r.Address.Street.ToUpper().Contains(searchTerm))
        || (r.Address.Complement != null && r.Address.Complement.ToUpper().Contains(searchTerm))
    );

    return results;
}

These methods are replicated ad nauseum throughout each provider class and I hope I could replace them for a single method that would receive the properties to be included in the query.

Something like:

public static IQueryable<T> Match<T>(string searchTerm, IQueryable<T> data, Expression<Func<T, string>> filterProperties)
{
    var results = **build the query for each property in filterProperties**

    return results;
}

But I really can't figure it out. I tried using reflection but it only worked with Linq to Objects and I need a solution for Linq to Entities.

score 6 · Accepted Answer · answered Aug 26 '14 at 16:58

So to solve this problem we need a few puzzle pieces first. The first puzzle piece is a method that can take an expression that computes a value, and then another expression that computes a new value taking the same type the first returns, and creates a new expression that represents the result of passing the result of the first function as the parameter to the second. This allows us to Compose expressions:

public static Expression<Func<TFirstParam, TResult>>
    Compose<TFirstParam, TIntermediate, TResult>(
    this Expression<Func<TFirstParam, TIntermediate>> first,
    Expression<Func<TIntermediate, TResult>> second)
{
    var param = Expression.Parameter(typeof(TFirstParam), "param");

    var newFirst = first.Body.Replace(first.Parameters[0], param);
    var newSecond = second.Body.Replace(second.Parameters[0], newFirst);

    return Expression.Lambda<Func<TFirstParam, TResult>>(newSecond, param);
}

This relies on the following tool to replace all instances of one expression with another:

public static Expression Replace(this Expression expression,
    Expression searchEx, Expression replaceEx)
{
    return new ReplaceVisitor(searchEx, replaceEx).Visit(expression);
}
internal class ReplaceVisitor : ExpressionVisitor
{
    private readonly Expression from, to;
    public ReplaceVisitor(Expression from, Expression to)
    {
        this.from = from;
        this.to = to;
    }
    public override Expression Visit(Expression node)
    {
        return node == from ? to : base.Visit(node);
    }
}

We'll also need a tool to help us OR two predicate expressions together:

public static class PredicateBuilder
{
    public static Expression<Func<T, bool>> True<T>() { return f => true; }
    public static Expression<Func<T, bool>> False<T>() { return f => false; }

    public static Expression<Func<T, bool>> Or<T>(
        this Expression<Func<T, bool>> expr1,
        Expression<Func<T, bool>> expr2)
    {
        var secondBody = expr2.Body.Replace(
            expr2.Parameters[0], expr1.Parameters[0]);
        return Expression.Lambda<Func<T, bool>>
              (Expression.OrElse(expr1.Body, secondBody), expr1.Parameters);
    }

    public static Expression<Func<T, bool>> And<T>(
        this Expression<Func<T, bool>> expr1,
        Expression<Func<T, bool>> expr2)
    {
        var secondBody = expr2.Body.Replace(
            expr2.Parameters[0], expr1.Parameters[0]);
        return Expression.Lambda<Func<T, bool>>
              (Expression.AndAlso(expr1.Body, secondBody), expr1.Parameters);
    }
}

Now that we have this we can use Compose on each property selector to map it from the property results to whether or not that property value is non-null and contains the search term. We can then OR all of those predicates together to get a filter for your query:

public static IQueryable<T> Match<T>(
    IQueryable<T> data,
    string searchTerm,
    IEnumerable<Expression<Func<T, string>>> filterProperties)
{
    var predicates = filterProperties.Select(selector =>
            selector.Compose(value => 
                value != null && value.Contains(searchTerm)));
    var filter = predicates.Aggregate(
        PredicateBuilder.False<T>(),
        (aggregate, next) => aggregate.Or(next));
    return data.Where(filter);
}

@Servy This is great. One problem I'm having though is `value.Contains(...)`. That's the OP's search logic, correct? I want to match on any word in the search term with any word in the property value. I have this in it's own method that splits the values into arrays and compares but this doesn't work in EF because it doesn't understand the method. How would I do that? Can take to chat if you want — Sinaesthetic, Aug 16 '16 at 18:18

score 2 · Answer 2 · answered Aug 26 '14 at 17:05

You can do it with expression trees but it's not as simple as you might think.

public static IQueryable<T> Match<T>(this IQueryable<T> data, string searchTerm,
                                         params Expression<Func<T, string>>[] filterProperties)
{
    var parameter = Expression.Parameter(typeof (T), "source");

    Expression body = null;

    foreach (var prop in filterProperties)
    {
        // need to replace all the expressions with the one parameter (gist taken from Colin Meek blog see link on top of class)

        //prop.body should be the member expression
        var propValue =
            prop.Body.ReplaceParameters(new Dictionary<ParameterExpression, ParameterExpression>()
                {
                    {prop.Parameters[0], parameter}
                });


        // is null check
        var isNull = Expression.NotEqual(propValue, Expression.Constant(null, typeof(string)));

        // create a tuple so EF will parameterize the sql call
        var searchTuple = Tuple.Create(searchTerm);
        var matchTerm = Expression.Property(Expression.Constant(searchTuple), "Item1");
        // call ToUpper
        var toUpper = Expression.Call(propValue, "ToUpper", null);
        // Call contains on the ToUpper
        var contains = Expression.Call(toUpper, "Contains", null, matchTerm);
        // And not null and contains
        var and = Expression.AndAlso(isNull, contains);
        // or in any additional properties
        body = body == null ? and : Expression.OrElse(body, and);
    }

    if (body != null)
    {
        var where = Expression.Call(typeof (Queryable), "Where", new[] {typeof (T)}, data.Expression,
                                    Expression.Lambda<Func<T, bool>>(body, parameter));
        return data.Provider.CreateQuery<T>(where);
    }
    return data;
}

 public static Expression ReplaceParameters(this Expression exp, IDictionary<ParameterExpression, ParameterExpression> map)
{
    return new ParameterRebinder(map).Visit(exp);
}

Now you need to have a expressionvisitor to make all the expressions use one parameter

//http://blogs.msdn.com/b/meek/archive/2008/05/02/linq-to-entities-combining-predicates.aspx
public class ParameterRebinder : ExpressionVisitor
{
    private readonly IDictionary<ParameterExpression, ParameterExpression> _map;

    public ParameterRebinder(IDictionary<ParameterExpression, ParameterExpression> map)
    {
        _map = map;
    }

    protected override Expression VisitParameter(ParameterExpression node)
    {
        if (_map.ContainsKey(node))
        {
            return _map[node];
        }
        return base.VisitParameter(node);
    }
}

Would use it like

var matches = retailers.Match("7", r => r.Address.Street, x => x.Address.Complement).ToList();

Warning - I checked this with linq to objects using the AsQueryable but didn't run it against EF.

Yuliam Chandra · Answer 3 · 2014-08-26T17:43:35.280

0

You can use Linq.Dynamic to build the query.

public static IQueryable<T> Match<T>(
    string searchTerm, 
    IQueryable<T> data, 
    params Expression<Func<T, string>>[] filterProperties) where T : class
{
    var predicates = new List<string>();
    foreach (var prop in filterProperties)
    {
        var lambda = prop.ToString();
        var columnName = lambda.Substring(lambda.IndexOf('.') + 1);
        var predicate = string.Format(
            "({0} != null && {0}.ToUpper().Contains(@0))", columnName);
        predicates.Add(predicate);
    }

    var filter = string.Join("||", predicates);
    var results = data.Where(filter, searchTerm);
    return results;
}

Usage.

var retailers = Match(
    "asd", db.Retailers, r => r.CompanyName, r => r.TradingName);

var retailers = Match(
    "asd", db.Retailers, r => r.Address.Street, r => r.Address.Complement);

Limitation.

The filter can only accept basic expression.

r => r.Name
r => r.PropA.Name
r => r.PropA.PropB.Name

edited Aug 26 '14 at 17:43

answered Aug 26 '14 at 16:43

Yuliam Chandra

14,494
12
52
67

This is *extremely* fragile, cannot support many different types of selector expressions, and can easily have its syntax broken as a result of attempting to parse the string representation of an unknown expression. – Servy Aug 26 '14 at 17:13
@Servy, I built it based on OP sample code, if you can provide another example that I can test, that would be great – Yuliam Chandra Aug 26 '14 at 17:23
I did. I posted it in my answer. – Servy Aug 26 '14 at 17:24
@Servy, sample usage that you said could break this method – Yuliam Chandra Aug 26 '14 at 17:25
Anything that's not *just* an accessor of a property, say, `item => "foo" + item.Property`, the use of a closure `item => someClosedOverVariable`, or the use of `EntityFunctions` `item => EntityFunctions.Left(item.Something, 5)`. – Servy Aug 26 '14 at 17:32

score 0 · Answer 4 · answered Dec 10 '14 at 12:51

0

Try to use Expressions like those all

http://www.codeproject.com/Articles/493917/Dynamic-Querying-with-LINQ-to-Entities-and-Express

answered Dec 10 '14 at 12:51

Hamit YILDIRIM

4,224
1
32
35

Generic Linq to Entities filter method that accepts filter criteria and properties to be filtered

4 Answers4

Linked