LINQ to Entities: Orderby statement involving extension method

Question

I'm constructing a simple search function for a site. The aim is to allow the user to find 'swimmers' and add them to a list via a search box, using a query like "bob staff".

The first part of this I decided to tackle was allowing the user to search via a group name (In the database all swimmers are part of a group). The following is the code I have at the moment.

[HttpGet]
    public JsonResult searchForSwimmers(string q)
    {
        //designed to return [{name='foo',id='bar'}]

        String[] QueryTerms = q.Split(' '); //all the search terms are sep. by " "
        var groupResults = _db.SwimGroups.Where(g => g.Name.ContainsAny(QueryTerms)) 
            .OrderByDescending(g => g.Name.StartsWithAny(QueryTerms) ? 1 : 0)
            .ThenBy( g => g)
            .Select(g => new { name = g.Name, id = g.ID });


        return Json(groupResults,JsonRequestBehavior.AllowGet);
    }

On line 8, there is a method invoked called StartsWithAny. This is an extension method I defined in the following file:

public static class StringUtils
    {
     public static Boolean StartsWithAny(this String str, params String[] Fragments)
        {
            foreach (String fragment in Fragments)
            {
                if (str.StartsWith(fragment))
                {
                    return true;
                }
            }
            return false;
        }
    }

The idea is that if a Name starts with one of the terms then it should be ranked higher in relevancy. I recognize that this logic is naïve and has flaws however I thought it would be a good example to illustrate the problem I've having. The code compiles however when searchForSimmers is invoked in my cshtml page with the following: (using the tokenInput library)

<script type="text/javascript">
     $(document).ready(function () {
          $("#demo-input-local").tokenInput("/Admin/searchForSwimmers");
     });
</script>

I get a 500 internal server error. The error message is as follows:

LINQ to Entities does not recognize the method 'Boolean ContainsAny(System.String, System.String[])' method, and this method cannot be translated into a store expression.

The ContainsAny method

public static Boolean ContainsAny(this String str, List<String> Fragments)
        {
            foreach (String fragment in Fragments)
            {
                if(str.Contains(fragment))
                {
                    return true;
                }
            }
            return false;
        }

I've had a look around but couldn't find a solution to the problem. Any help would be greatly appreciated, cheers.

Answer 1

Check out my blog where I create a search extension method for IQueryable.

UPDATED: I have created a new blog post showing the extension method required to acheive your goal

http://www.ninjanye.co.uk/2013/04/generic-iqueryable-or-search-for.html

http://jnye.co/Posts/8/generic-iqueryable-or-search-for-multiple-search-terms-using-expression-trees

The problem is linq does not know how to translate your code into SQL.

By adding the following extension method:

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);
    }

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

}

This will allow you to write the following lambda:

[HttpGet]
public JsonResult searchForSwimmers(string q)
{
    //designed to return [{name='foo',id='bar'}]

    String[] QueryTerms = q.Split(' '); //all the search terms are sep. by " "
    var groupResults = _db.SwimGroups.Search(g => g.Name, queryTerms) 
                                     .OrderByDescending(g => g.Name.StartsWithAny(QueryTerms) ? 1 : 0)
                                     .ThenBy( g => g)
                                     .Select(g => new { name = g.Name, id = g.ID });

    return Json(groupResults,JsonRequestBehavior.AllowGet);
}    

Answer 2

That's because neither your ContainsAny or StartsWithAny extension methods can be translated into SQL.

As your database table is small (as noted in comments), just resolve your query, by calling .ToList() before you do the Where and OrderBy.

Try this:

var groupResults = _db.SwimGroups
        .ToList() //evaluate the query, bring it into memory
        .Where(g => g.Name.ContainsAny(QueryTerms)) 
        .OrderByDescending(g => g.Name.StartsWithAny(QueryTerms) ? 1 : 0)
        .ThenBy( g => g)
        .Select(g => new { name = g.Name, id = g.ID });