EF extension method: “This function can only be invoked from LINQ to Entities.”

I have made an extension method to EF entities:

public static IEnumerable<T> WildcardSearch<T>(this IEnumerable<T> entity, 
                                                 string param, Func<T, 
                                                 string> selector)
{
    return entity.Where(l => SqlFunctions.PatIndex(param, selector(l)) > 0);
}

//exception: This function can only be invoked from LINQ to Entities.
result = context.FOO.WildcardSearch(id, x => x.Id).ToList();

I get the exception above if I try to use it.

However, if I run (what I assume) the very same code directly on my collection, it works as intented.

Why do I get the exception, and is there any way to fix this?

Similar threads suggest changing the type to IQueryable, but it doesn’t seem to help.

//this works
result = context.FOO.Where(x => 
              SqlFunctions.PatIndex(id, x.Id) > 0).ToList();

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

It needs to be IQueryable<T> and Expression<Func<...>>; unfortunately this means rebuilding the expression tree – for example:

public static IQueryable<T> WildcardSearch<T>(this IQueryable<T> entity,
    string param, Expression<Func<T, string>> selector)
{
    var lambda = Expression.Lambda<Func<T, bool>>(
        Expression.GreaterThan(
            Expression.Call(
                typeof(SqlFunctions), "PatIndex", null,
                Expression.Constant(param, typeof(string)),
                selector.Body),
            Expression.Constant(0)),
        selector.Parameters);
    return entity.Where(lambda);
}

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0