Generic IQueryable 'OR' search on multiple properties using expression trees

by John Nye

03 Apr
2013

Now available as a nuget package. Search for 'SearchExtensions' or run the following:

PM> Install-Package NinjaNye.SearchExtensions

Source code can be found here: https://github.com/ninjanye/searchextensions


Following on from my previous post on creating a generic search extension method for IQueryable, I decided to take the concept a step further and create an additional method that allows you to search multiple properties for a particular search term. The syntax I wanted to use for this new method was as follows:

//Search users where...
var ninjaUsers = dataContext.Users.Search("ninja", x => x.UserName,
                                                   x => x.FirstName,
                                                   x => x.LastName);

After a visit to stackoverflow and some expert guidance from @MarcGravell, this is the resulting code:

    public static IQueryable<T> Search<T>(this IQueryable<T> source, 
                                          string searchTerm, 
                                          params Expression<Func<T, string>>[] stringProperties)
    {
        if (String.IsNullOrEmpty(searchTerm))
        {
            return source;
        }

        var searchTermExpression = Expression.Constant(searchTerm);

        //Variable to hold merged 'OR' expression
        Expression orExpression = null;
        //Retrieve first parameter to use accross all expressions
        var singleParameter = stringProperties[0].Parameters.Single();

        //Build a contains expression for each property
        foreach (var stringProperty in stringProperties)
        {
            //Syncronise single parameter accross each property
            var swappedParamExpression = SwapExpressionVisitor.Swap(stringProperty, stringProperty.Parameters.Single(), singleParameter);

            //Build expression to represent x.[propertyX].Contains(searchTerm)
            var containsExpression = BuildContainsExpression(swappedParamExpression, searchTermExpression);

            orExpression = BuildOrExpression(orExpression, containsExpression);
        }

        var completeExpression = Expression.Lambda<Func<T, bool>>(orExpression, singleParameter);
        return source.Where(completeExpression);
    }

    private static Expression BuildOrExpression(Expression existingExpression, Expression expressionToAdd)
    {
        if (existingExpression == null)
        {
            return expressionToAdd;
        }

        //Build 'OR' expression for each property
        return Expression.OrElse(existingExpression, expressionToAdd);
    }

    private static MethodCallExpression BuildContainsExpression<T>(Expression<Func<T, string>> stringProperty, ConstantExpression searchTermExpression)
    {
        return Expression.Call(stringProperty.Body, typeof(string).GetMethod("Contains"), searchTermExpression);
    }

//Create SwapVisitor to merge the parameters from each property expression into one
public class SwapVisitor : ExpressionVisitor
{
    private readonly Expression from, to;
    public SwapVisitor(Expression from, Expression to)
    {
        this.from = from;
        this.to = to;
    }
    public override Expression Visit(Expression node)
    {
        return node == from ? to : base.Visit(node);
    }
    public static Expression Swap(Expression body, Expression from, Expression to)
    {
        return new SwapVisitor(from, to).Visit(body);
    }
}

Performing the following code against a DBContext (connected to a sql db):

//Search users where...
dataContext.Users.Search("ninja", x => x.UserName,  
                                          x => x.FirstName, 
                                          x => x.LastName).ToList();

Produces the following SQL:

SELECT [Extent1].[Id] AS [Id], 
       [Extent1].[UserName] AS [UserName], 
       [Extent1].[FirstName] AS [FirstName], 
       [Extent1].[LastName] AS [LastName], 
       [Extent1].[Email] AS [Email], 
FROM   [dbo].[Users] AS [Extent1]
WHERE ([Extent1].[UserName] LIKE N'%ninja%') 
   OR ([Extent1].[FirstName] LIKE N'%ninja%') 
   OR ([Extent1].[LastName] LIKE N'%ninja%')

Because I can see more extension methods being added to this code I have created a SearchExtensions project on github. Please feel free fork this and make your own additions. Many thanks to @MarcGravell for helping me to see this task through.

Comments 8

Ali Arslan says: 1150 days ago

Hi John,

We need and overload of the fucnction

public static IQueryable Search(this IQueryable source, string searchTerm, params Expression<Func<T, string>>[] stringProperties);

with sting[] properties parameter instead of params Expression<Func<T, string>>[] stringProperties as:

public static IQueryable Search(this IQueryable source, string searchTerm, string[] stringProperties);

where all string type properties of type T are checked to contain the searchTerm and OR'ed, becuase we dont know the type T where we need it.

Is it possible to add such an overload of search function?

Thanks for good work.

John says: 1150 days ago

Hi Ali,

If I understand correctly, you won't need the the stringProperties parameter at all if you are searching all properties. So the new Search method will be something like:

public static IQueryable Search(this IQueryable source, string searchTerm)

OR (for multiple search terms)

public static IQueryable Search(this IQueryable source, params string[] searchTerms)

This is definitely possible... I will add it to my list of features to implement and try and put something together by next week. Feel free to fork the repository and create a pull request if you want to dabble in the code

Thanks for your interest
Regards

Ali Arslan says: 1150 days ago

Hi John,

I tried to implement the requirement follows:

    public static IQueryable<T> Search<T>(this IQueryable<T> source, string searchText)
    {
        var t = Expression.Parameter(typeof(T));
        Expression body = Expression.Constant(false);

        var containsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string) });
        var toStringMethod = typeof(object).GetMethod("ToString");

        var stringProperties = typeof(T).GetProperties().Where(property => property.PropertyType == typeof(string));

        foreach (var property in stringProperties)
        {
            var stringValue = Expression.Call(Expression.Property(t, property.Name), toStringMethod);
            var nextExpression = Expression.Call(stringValue, containsMethod, Expression.Constant(searchText));

            body = Expression.Or(body, nextExpression);
        }

        return source.Where(Expression.Lambda<Func<T, bool>>(body, t));
    }

For my stuation the problem is that Wcf Data Services doesnt support "Contains" method.

Regards.

John says: 1150 days ago

I had a similar problem, the way I got around it was to use the IndexOf method.

Here is the Expression helper the below was taken from https://github.com/ninjanye/SearchExtensions/blob/master/SearchExtensions/ExpressionHelper.cs#L44

static readonly MethodInfo IndexOfMethod = typeof(string).GetMethod("IndexOf", 
                                                                    new[] { typeof(string),     
                                                                    typeof(StringComparison) });

public static BinaryExpression BuildIndexOfExpression<T>(Expression<Func<T, string>> stringProperty, ConstantExpression searchTermExpression, StringComparison stringComparison)
{
    var coalesceExpression = Expression.Coalesce(stringProperty.Body, EmptyStringExpression);
    var stringComparisonExpression = Expression.Constant(stringComparison);
    var indexOfCallExpresion = Expression.Call(coalesceExpression, 
                                               IndexOfMethod, 
                                               searchTermExpression, 
                                               stringComparisonExpression);
    return Expression.GreaterThanOrEqual(indexOfCallExpresion, ZeroConstantExpression);
}
John says: 1147 days ago

Hi Ali,

I have just released a new version of NinjaNye.SearchExtensions that includes support for automatically searching all string properties on an object. It is really simple to use, just omit the property parameter

//Finds and searches on all string properties
var result = data.Search("searchTerm")

This has been released as an update to the nuget package so feel free to download it and try it out.

Please note, as part of this update I have altered the method signature for consistency so you may have to re-jig your code slightly.

I am currently writing up a new blog post describing the changes in the update. I'll be sure to post a link here

Thanks again for your input and suggesting the new feature

Cheers
John

Ali Arslan says: 1143 days ago

Hi John,

I tried your new "Search" method, but couldn't make it work for my stuation; I am using DataServiceQuery object for my wcf data service query. The error message is like "((((([10007].UserName ?? "").Contains("e0") OrElse ...) is not supported"

I have tired query expressions that doesnt contain ?? operator and they worked. I dont know if ?? operator works for other stuations.

When you publish your code i will test it by removing ?? operators from code and let you know about result.

Regards.

John says: 1143 days ago

Hi Ali,

I think I understand your problem a bit better now. I will look into this and try to provide a solution in the next day or so. After a bit of a browse, I did come across this MSDN page. And it does show some limitations with this class. I will try and have a look this evening and hopefully get a solution out to you.

Regards

Ali Arslan says: 1141 days ago

Hi John,

By using the code at

http://stackoverflow.com/questions/17855416/linq-filter-implementation-with-expressions

which uses your "BuildOrExpression" i extracted a working extension as follows:

    public static IQueryable<T> Search<T>(this IQueryable<T> source, string filterText)
    {
        if (string.IsNullOrEmpty(filterText))
        {
            return source;
        }

        string [] properties = typeof(T).GetProperties().Where(p => p.PropertyType == typeof(string)).Select(p => p.Name).ToArray();

        ParameterExpression parameter = Expression.Parameter(typeof(T));
        Expression[] propertyExpressions = properties.Select(
            x => !string.IsNullOrEmpty(x) ? GetDeepPropertyExpression(parameter, x) : null).ToArray();

        Expression like = propertyExpressions.Select(expression => Expression.Call(expression, typeof(string).GetMethod("ToLower", Type.EmptyTypes))).Select(toLower => Expression.Call(toLower, typeof(string).GetMethod("Contains"), Expression.Constant(filterText.ToLower()))).Aggregate<MethodCallExpression, Expression>(null, (current, ex) => BuildOrExpression(current, ex));

        return source.Where(Expression.Lambda<Func<T, bool>>(like, parameter));
    }

    private static Expression BuildOrExpression(Expression existingExpression, Expression expressionToAdd)
    {
        if (existingExpression == null)
        {
            return expressionToAdd;
        }

        //Build 'OR' expression for each property
        return Expression.OrElse(existingExpression, expressionToAdd);
    }

    private static Expression GetDeepPropertyExpression(Expression initialInstance, string property)
    {
        Expression result = null;
        foreach (string propertyName in property.Split('.'))
        {
            Expression instance = result ?? initialInstance;
            result = Expression.Property(instance, propertyName);
        }
        return result;
    }

Best regards.

Leave a message...

30 Mar
2017