How to build a simple property selector expression in ef6

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.

you might want to look into [PredicateBuilder](http://www.albahari.com/nutshell/predicatebuilder.aspx) to help you do this — DLeh, Jul 10 '15 at 15:09
The guy who posted the answer before. That worked! Thanks mysterious person who deleted your post. — Price Jones, Jul 10 '15 at 15:42
i deleted my answer because it wasn't actually compiling to SQL, it was just in memory. I'm looking at how to make it properly compile to sql. check back in a bit, hopefully i'll have a working answer. — DLeh, Jul 10 '15 at 15:43
I haven't yet found a good way to be able to reuse the `ToLower().Contains()` logic on multiple properties. I'm sure there's a way to do it by building the expressions, i don't have enough experience working with expressions to figure it out yet. I'd be cautious with using my deleted answer, since it doesn't compile to SQL (As far as i could tell), which means you'd be pulling your whole table down, then filtering it, which is not what you want to do. — DLeh, Jul 10 '15 at 19:03

score 3 · Accepted Answer · edited May 23 '17 at 12:06

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

Thank you for mentioning the db collation. It turns out the db is set to CI so I can completely ignore ToLower() in this case. which is really helpful. The problem with using Contains() is that although code I show in my question can be thought of in terms of Contains(), the actual query has another clause that requires IndexOf(), so even if I use Contains() in the query above I will eventually need to work out the IndexOf() solution. — Price Jones, Jul 13 '15 at 12:22
for MSSql in linq queries you can try SqlFunctions.CharIndex (https://msdn.microsoft.com/en-us/library/dd487160%28v=vs.110%29.aspx). Be aware that that will only work with a provider that understands that, e.g you wont be able to compile and execute the expression tree in memory! — MBoros, Jul 13 '15 at 13:14

How to build a simple property selector expression in ef6

1 Answers1