How to build a simple property selector expression in ef6

c# entity-framework expression-trees linq

Question

How can I create a property selector for entity framework like this?

public static List<T> StandardSearchAlgorithm<T>(this IQueryable<T> queryable, Func<T, string> property, string query)
{

    return queryable.Where(e => property(e).ToLower().IndexOf(query) > -1).ToList();

}

I want the calling code to be able to be clean and simple like this:

var usernameResults = _db.Users.StandardSearchAlgorithm(u => u.Username, query);

I get a "The LINQ expression node type 'Invoke' is not supported in LINQ to Entities." error. I cannot work out how to get the expression built.

UPDATE:

Based on the answer by MBoros here is the code I ended up with. It works great.

The key to expression trees is to understand expression trees are all about breaking up what you normally write in code (like "e => e.Username.IndexOf(query)") into a series of objects: "e" gets its own object, "Username" its own object, "IndexOf()" its own object, the "query" constant its own object, and so on. The second key is to know that you can use a series of static methods on the Expression class to create various kinds of these objects, as shown below.

    PropertyInfo pinfo = (PropertyInfo)((MemberExpression)property.Body).Member;
    ParameterExpression parameter = Expression.Parameter(typeof(T), "e");
    MemberExpression accessor = Expression.Property(parameter, pinfo);
    ConstantExpression queryString = Expression.Constant(query, typeof(string));
    ConstantExpression minusOne = Expression.Constant(-1, typeof(int));
    MethodInfo indexOfInfo = typeof(string).GetMethod("IndexOf", new[] { typeof(string) }); // easiest way to do this
    Expression indexOf = Expression.Call(accessor, indexOfInfo, queryString);
    Expression expression = Expression.GreaterThan(indexOf, minusOne);
    Expression<Func<T, bool>> predicate = Expression.Lambda<Func<T, bool>>(expression, parameter);
    //return predicate.Body.ToString(); // returns "e => e.Username.IndexOf(query) > -1" which is exactly what we want.

    var results = queryable.Where(predicate).ToList();
    return results;

Now I have a real problem, but I will ask it in a separate question. My real query looks like this:

public static List<T> StandardSearchAlgorithm<T>(this IQueryable<T> queryable, Func<T, string> property, string query)
{

    return queryable.Where(e => property(e).IndexOf(query) > -1).Select(e=> new { Priority = property(e).IndexOf(query), Entity = e } ).ToList();

}

So I need to build an expression that returns an Anonymous Type!! Or even if I create a class to help, I need to write an expression that returns a new object. But I will include this in a separate question.

Accepted Answer

You cannot invoke CLR delegates so simply in sql. But you can pass in the property selector as an Expression tree., so your signature would be:

public static List<T> StandardSearchAlgorithm<T>(this IQueryable<T> queryable, Expression<Func<T, string>> property, string query)

Calling would look the same. But now that you have an expression in your hand, you can have a look at this answer: Pass expression parameter as argument to another expression It gives you the tools to simply put an expression tree inside another one. In your case it would look like:

Expression<Func<T, bool>> predicate = e => property.AsQuote()(e).Contains(query);
predicate = predicate.ResolveQuotes();
return queryable.Where(predicate).ToList();

Once you are there, you still have the .ToLower().Contains() calls (use .Contains instead of the .IndexOf()> 1). This is actually tricky. Normally the db uses its default collation, so if it set to CI (case insensitive), then it will do the compare that way. If you don't have any constraints, and can adjust the db collation, I would go for that. In this case you can omit the .ToLower() call. Otherwise check out this anser: https://stackoverflow.com/a/2433217/280562



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why