How can I make Linq extension method available for the entities?

Question

I wrote a method that extends System.Linq class. My ContainsAnyWord method allows me to search all words in a string against a string instead of comparing one string to another.

Here is the method that I wrote to extend Linq

public static class LinqExtension
{

    public static bool ContainsAnyWord(this string value, string searchFor)
    {
        if (!string.IsNullOrWhiteSpace(value) && !string.IsNullOrWhiteSpace(searchFor))
        {
            value = value.ToLower();

            IEnumerable<string> tags = StopwordTool.GetPossibleTags(searchFor);

            return tags.Contains(value);

        }

        return false;

    }

}

This extension method works great on an IEnumerable object. But when I want to use it on IQueryable object I get the following error

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

The following example works great because I am working with a list.

using(var conn = new AppContext())
{

    var allUsers = conn.Users.GetAll().ToList();
    var foundUsers = allUsers.Where
    (
        user => 
               user.FirstName.ContainsAnyWord("Some Full Name Goes Here")
            || user.LastName.ContainsAnyWord("Some Full Name Goes Here")
    ).ToList();

}

But the following example does not work because I am working with IQueryable which gives me the error listed above.

using(var conn = new AppContext())
{

    var allUsers = conn.Users.Where
    (
        user => 
            user.FirstName.ContainsAnyWord("Some Full Name Goes Here") 
         || user.LastName.ContainsAnyWord("Some Full Name Goes Here")
    ).ToList();

}

How can I fix this issue and make this method available for IQueryable object?

UPDATED

Based on the feedback I got below, I tried to implement this using IQueryable like so

    public static IQueryable<T> ContainsAnyWord<T>(this IQueryable<T> query, string searchFor)
    {
        if (!string.IsNullOrWhiteSpace(searchFor))
        {
            IEnumerable<string> tags = StopwordTool.GetPossibleTags(searchFor);

            return query.Where(item => tags.Contains(item));

        }

        return query;

    }

But this is also giving me an error

Answer 1

You can't call methods inside the query expression the expression parser provided by the Linq to Entites does not know how to handle. The only thing you can really do is write a extension method that takes in a IQueryable<T> and build up the expression internally inside the function instead of calling Where(

This below code is totally untested and is something I came up with off the top of my head. But you would need to do something like this

public static IQueryable<T> WhereContainsAnyWord<T>(this IQueryable<T> @this, Expression<Func<T, string>> selector, string searchFor)
{
    var tags = StopwordTool.GetPossibleTags(searchFor); //You might need a .ToArray() here.

    var selectedItems = @this.GroupBy(selector);
    var filteredItems = selectedItems.Where(selectedItem => tags.Contains(selectedItem.Key.ToLower()));
    var result = filteredItems.SelectMany(x => x);
    return result;
}

used like

using(var conn = new AppContext())
{

    var allUsers = conn.Users.WhereContainsAnyWord(user => user.FirstName, "Some Full Name Goes Here").ToList();

}

Here is a simpiler non generic version

public static IQueryable<User> WhereContainsAnyWord(this IQueryable<User> @this, string searchFor)
{
    var tags = StopwordTool.GetPossibleTags(searchFor); //You might need a .ToArray() here.

    return @this.Where(user => tags.Contains(user.DisplayName.ToLower()));
}

used like

using(var conn = new AppContext())
{

    var allUsers = conn.Users.WhereContainsAnyWord("Some Full Name Goes Here").ToList();

}

Answer 2

You have to keep in mind that this has to be translated to SQL at the end. Obviously ContainsAnyWord cannot be translated to SQL...
So ,save your names in a List/Array and try

 user =>  yourlist.Contains(  user.FirstName)

EF will translate this to a WHERE ..IN

There is no need for a method but if for some reason you want it

internal static class MyClass2
{
    public static IQueryable<T> ContainsAnyWord<T>(this IQueryable<T> value, string searchFor) where T: User
    {
        var names = searchFor.Split(' ').ToList();
        return value.Where(u => names.Contains(u.DisplayName));
    }
}

You can use it like

var result=conn.Users.ContainsAnyWord("abc def ght");

Answer 3

Linq to Entities converts Methods into database expressions. You can not implement an extension to System.String and expect it to be translated into a T-SQL expression, wich is what internally Entity Framework does.

My suggestion is trying to work around using pre-defined Linq methods. Try using Contains method.