EF Core - Expression Tree Equivalent for IQueryable Search

.net-core c# entity-framework-core

Question

I have an initial workflow put together that allows me to perform inclusive searches for string properties of objects contained in an IQueryable:

public static IQueryable ApplySearch(this IQueryable queryable, string search)
{
    // validation omitted for brevity
    var expression = queryable
        .Cast<object>()
        .Where(item => item.SearchStringTree(search))
        .Expression;

    var result = queryable.Provider.CreateQuery(expression);
    return result;
}

static bool SearchStringTree<T>(this T value, string search) =>
    value.GetObjectStrings().Any(s => s.Contains(search.ToLower()));

static IEnumerable<string> GetObjectStrings<T>(this T value)
{
    var strings = new List<string>();

    var properties = value.GetType()
        .GetProperties()
        .Where(x => x.CanRead);

    foreach (var prop in properties)
    {
        var t = prop.PropertyType.ToString().ToLower();
        var root = t.Split('.')[0];

        if (t == "system.string")
        {
            strings.Add(((string)prop.GetValue(value)).ToLower());
        }
        else if (!(root == "system"))
        {
            strings.AddRange(prop.GetValue(value).GetObjectStrings());
        }
    }

    return strings;
}

Would it be possible to apply this concept in a way that Entity Framework can translate prior to DbContext execution?

I've been looking into potentially using Expression Trees to accomplish this.

Here's a working Repl.it showing the IQueryable implementation above.

1
2
1/15/2020 3:41:06 PM

Accepted Answer

You definitely need to build expression tree, basically multi or (C# ||) predicate expression for all (nested) string properties.

Something like this (expression version of your code):

public static class FilterExpression
{
    public static IQueryable<T> ApplySearch<T>(this IQueryable<T> source, string search)
    {
        if (source == null) throw new ArgumentNullException(nameof(source));
        if (string.IsNullOrWhiteSpace(search)) return source;

        var parameter = Expression.Parameter(typeof(T), "e");
        // The following simulates closure to let EF Core create parameter rather than constant value (in case you use `Expresssion.Constant(search)`)
        var value = Expression.Property(Expression.Constant(new { search }), nameof(search));
        var body = SearchStrings(parameter, value);
        if (body == null) return source;

        var predicate = Expression.Lambda<Func<T, bool>>(body, parameter);
        return source.Where(predicate);
    }

    static Expression SearchStrings(Expression target, Expression search)
    {
        Expression result = null;

        var properties = target.Type
          .GetProperties()
          .Where(x => x.CanRead);

        foreach (var prop in properties)
        {
            Expression condition = null;
            var propValue = Expression.MakeMemberAccess(target, prop);
            if (prop.PropertyType == typeof(string))
            {
                var comparand = Expression.Call(propValue, nameof(string.ToLower), Type.EmptyTypes);
                condition = Expression.Call(comparand, nameof(string.Contains), Type.EmptyTypes, search);
            }
            else if (!prop.PropertyType.Namespace.StartsWith("System."))
            {
                condition = SearchStrings(propValue, search);
            }
            if (condition != null)
                result = result == null ? condition : Expression.OrElse(result, condition);
        }

        return result;
    }
}

The non generic version is not much different - just instead of Where extension method you need to generate a "call" to it in the query expression tree:

public static IQueryable ApplySearch(this IQueryable source, string search)
{
    if (source == null) throw new ArgumentNullException(nameof(source));
    if (string.IsNullOrWhiteSpace(search)) return source;

    var parameter = Expression.Parameter(source.ElementType, "e");
    var value = Expression.Property(Expression.Constant(new { search }), nameof(search));
    var body = SearchStrings(parameter, value);
    if (body == null) return source;

    var predicate = Expression.Lambda(body, parameter);
    var filtered = Expression.Call(
        typeof(Queryable), nameof(Queryable.Where), new[] { source.ElementType },
        source.Expression, Expression.Quote(predicate));
    return source.Provider.CreateQuery(filtered);
}

While this works, it's not much useful because all LINQ extensions methods (including AsEnumerable(),ToList()` etc.) work with generic interface.

Also in both cases, the type of the query element must be known in advance, e.g. T in the generic version, query.ElementType in the non generic version. This is because expression tree are processed in advance, when there are no "objects", hence it can't use item.GetType(). For the same reason, IQueryable translators like EF Core don't like Cast "calls" inside the query expression tree.

2
1/16/2020 10:26:57 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow