Generic IQueryable 'OR' search for multiple search terms using expression trees

by John Nye

28 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


Continuing my latest theme of search extension methods, my new method allows users to search a property against multiple search terms.

The code has been added to my existing search extensions project which can be found on github by going to https://github.com/ninjanye/SearchExtensions

public static class QueryableExtensions
{
    public static IQueryable<T> Search<T>(this IQueryable<T> source, 
                                          Expression<Func<T, string>> stringProperty, 
                                          params string[] searchTerms)
    {
        if (!searchTerms.Any())
        {
            return source;
        }

        Expression orExpression = null;
        foreach (var searchTerm in searchTerms)
        {
            //Create expression to represent x.[property].Contains(searchTerm)
            var searchTermExpression = Expression.Constant(searchTerm);
            var containsExpression = BuildContainsExpression(stringProperty, searchTermExpression);

            orExpression = BuildOrExpression(orExpression, containsExpression);
        }

        var completeExpression = Expression.Lambda<Func<T, bool>>(orExpression, stringProperty.Parameters);
        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);
    }
}

This allows the following linq statement:

var users = context.Users.Search(u => u.UserName, "john", "bob", "fred");

... which, if used via entity framework against a SQL database converts to 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'%john%')   
   OR ([Extent1].[UserName] LIKE N'%bob%')   
   OR ([Extent1].[UserName] LIKE N'%fred%')

Comments 2

Cedric S says: 1141 days ago

Hi John,

I was pleased to see this nice extension. But while I was trying to implement I bounced to the following question. How do you handle if the property which you are trying to compare is a number (nullable). E.g. u.ManagerID

var users = context.Users.Search(u => u.ManagerID, 10, 20, 30);

I tried to find something like the SQL alternative for WHERE ManagerID IN (10:20:30)

For the example I have already changed the Contains to Equals but then I dont know how to handle the comparison. Do you have ideas on how to challenge this ?

Thanks

Cedric

John says: 1139 days ago

Hi Cedric,

Thanks for getting in touch. Your timing is good, I have literally just released a new version of NinjaNye.SearchExtensions. At the moment this only allows you to search against string fields but you could adapt the code from GitHub

An alternative might be using existing linq capability, as follows:

var managerIds = new[]{10,20,30};
var users = context.Users.Where(u =&gt; managerIds.Any(mi =&gt; mi == u.ManagerId))

Hope this helps.
Cheers
John

Leave a message...

21 Nov
2017