Using Func property selector with Entity Framework 6

Question

I am trying to write a search function where I can pass in a Func that allows me to specify which string property to search. Entity Framework 6 complains that I am trying to invoke a function, which is not allowed. Is there anyway to rewrite the following so that I can use it with EF?

public List<Person> SearchPeople(string searchTerm, Func<Person, string> selector)
{                         
     return myDbContext.Persons.Where(person => selector(person).Contains(searchTerm)).ToList();                
}

The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.

Answer 1

You could use LinqKit library to solve your issue (Check this link and this for more info).

public List<Person> SearchPeople(string searchTerm, Expression<Func<Person, string>> selector)
{                         
     return myDbContext.Persons
                       .AsExpandable()
                       .Where(person => selector.Invoke(person).Contains(searchTerm))
                       .ToList();                
}

AsExpandable method creates a thin wrapper around the DLINQ Table object. Thanks to this wrapper you can use second method called Invoke that extends the Expression class to invoke the lambda expression while making it still possible to translate query to T-SQL. This works because when converting to the expression tree, the wrapper replaces all occurrences of Invoke method by expression trees of the invoked lambda expression and passes these expressions to DLINQ that is able to translate the expanded query to T-SQL.

So the only that you need to do is change the second parameter type of your SearchPeople method to Expression<Func<Person,string>>, and then you can invoke your method as I show as follow:

Expression<Func<Person,string>> exp= person=>person.Name;
//...
var people=SearchPeople("Albert",exp);

Answer 2

What you are trying to do is actually more complicated than it seems, because it involves manually combining a Person property expression with an expression that represents an invocation to string.Contains(searchTerm).

When you can directly write a complete lambda expresion like

p => p.FirstName.Contains(searchTerm)

...it's not hard at all, because the compiler does all the heavy lifting for you. But when you have to combine the expression pieces manually, as in this case, it can quickly become messy.

I haven't tried octavioccl's answer, but if it works as advertised, it's a very elegant solution, and I would definitely use that if possible.

However, if anything, to gain some appreciation for all the work the compiler does for us, here is how you can solve your problem without any external libraries (uses a few C# 6 features, but it can easily be tweaked for older versions):

public List<Person> SearchPeople(string searchTerm, Expression<Func<Person, string>> personProperty)
{
    // Note: Explanatory comments below assume that the "personProperty" lambda expression is:
    //       p => p.FirstName

    // Get MethodInfo for "string.Contains(string)" method.
    var stringContainsMethod = typeof(string).GetMethod(
        nameof(string.Contains),
        new Type[] { typeof(string) });

    // Create a closure class around searchTerm.
    // Using this technique allows EF to use parameter binding
    // when building the SQL query.
    // In contrast, simply using "Expression.Constant(searchTerm)",
    // makes EF hard-code the string in the SQL, which is not usually desirable.
    var closure = new { SearchTerm = searchTerm };
    var searchTermProperty = Expression.Property(
        Expression.Constant(closure), // closure
        nameof(closure.SearchTerm));  // SearchTerm

    // This forms the complete statement: p.FirstName.Contains(closure.SearchTerm)
    var completeStatement = Expression.Call(
        personProperty.Body,  // p.FirstName
        stringContainsMethod, // .Contains()
        searchTermProperty);  // closure.SearchTerm

    // This forms the complete lambda: p => p.FirstName.Contains(closure.SearchTerm)
    var whereClauseLambda = Expression.Lambda<Func<Person, bool>>(
        completeStatement,             // p.FirstName.Contains(closure.SearchTerm)
        personProperty.Parameters[0]); // p

    // Execute query using constructed lambda.
    return myDbContext.Persons.Where(whereClauseLambda).ToList();
}

You can then invoke the method like this:

foreach (var person in SearchPeople("joe", p => p.FirstName))
{
    Console.WriteLine($"First Name: {person.FirstName}, Last Name: {person.LastName}");
}