EF extension method: "This function can only be called from LINQ to Entities."

Question

I made an extension method for EF objects:

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 (as I assume) the same code directly in my collection, it works as intentional.

Why am I getting an exception, and is there a way to fix this?

Similar topics 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(); 

Answer 1

It should be IQueryable<T> and Expression<Func<...>> ; unfortunately, this means restoring 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); }