How to generate an Expression to query a match on any members of an entity or of its related Entities?

user_1649043 0
Viewed 107

I have the following code that I'm using to perform searches on my efcore data. Since the data set it so huge, I had to start using dynamic / generic types. I've been able to query on entity level properties, but I'm struggling to query entities that would have been defined as .Include(x => x.SomeInclusionEntity)

I've included my working code, as well as the second labelled "THIS SECTION DOESNT WORK" to show my ideas. I know its not perfect, but it works reasonably well for our internal use cases. Most people just use basic string searches for the same things over and over.

public IQueryable<T> GetBySearchTerm(IQueryable<T> queryable, string search)
{
    T thisEntityBaseModel = new T();
   
    IEntityType set = _dbContext.Model.GetEntityTypes().First(x => x.ClrType.Name.ToUpper() == thisEntityBaseModel.ModelName.ToUpper());
    List<Expression<Func<T, bool>>> predicateArray = new List<Expression<Func<T, bool>>>();

    MethodInfo containsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string) });
    
    foreach (IProperty columnProp in set.GetProperties()) {
        if (columnProp.ClrType == typeof(string)) {
            // Define the parameter
            ParameterExpression xParam = Expression.Parameter(typeof(T), "x");
            // Create the expression representing what column to do the search on
            MemberExpression colExpr = Expression.Property(xParam, columnProp.Name);
            // Create a constant representing the search value
            ConstantExpression constExpr = Expression.Constant(search);
            // Generate a method body that represents "column contains search"
            MethodCallExpression lambdaBody = Expression.Call(colExpr, containsMethod, constExpr);
            // Convert the full expression into a useable query predicate
            Expression<Func<T, bool>> lambda = Expression.Lambda<Func<T, bool>>(lambdaBody, xParam);
            predicateArray.Add(lambda);
        }
    }


/*  THIS SECTION DOESNT WORK===========================================================
    // Traverse declared navigation
    foreach (INavigation declaredNavigation in set.GetDeclaredNavigations())
    {
        // These are the navigations included by EFcore that aren't part of the data model.  Search them too
        IEnumerable<IProperty> x = declaredNavigation.TargetEntityType.GetProperties();
        foreach (IProperty columnProp in x)
        {
            if (columnProp.ClrType == typeof(string))
            {
                // Define the parameter
                ParameterExpression xParam = Expression.Parameter(declaredNavigation.ClrType, "z");
                // Create the expression representing what column to do the search on
                MemberExpression colExpr = Expression.Property(xParam, columnProp.Name);
                // Create a constant representing the search value
                ConstantExpression constExpr = Expression.Constant(search);
                // Generate a method body that represents "column contains search"
                MethodCallExpression lambdaBody = Expression.Call(colExpr, containsMethod, constExpr);
                // Convert the full expression into a useable query predicate
                LambdaExpression zz = Expression.Lambda(lambdaBody, xParam);

                //Expression<Func<T, bool>> lambda = Expression.Lambda<Func<T, bool>>(lambdaBody, xParam);
                //predicateArray.Add(lambda);
            }
        }
    }
THIS SECTION DOESNT WORK===========================================================*/


    // This performs an "OR" method on the predicates, since by default it wants to do "AND"
    var predicate = PredicateBuilder.False<T>();
    foreach (Expression<Func<T, bool>> expression in predicateArray) {
        predicate = predicate.Or(expression);
    }

    // Process the ors
    return (queryable.Where(predicate));
}
entity-framework-core expression c#
1 Answers

If I get it right, your target is to generate a query that will be equivalent to something like this:

users.Where(u => u.Name.Contains("Foo") ||
                 u.Alias.Contains("Foo") ||
                 ... ||
                 u.City.CityName.Contains("Foo") ||
                 ... ||
                 u.Pets.Any(p => p.Name.Contains("Foo") ||
                 ...
            );

In the part that did not work, in the line

MemberExpression colExpr = Expression.Property(xParam, columnProp.Name);

I think it generates u.CityName instead of u.City.CityName.

You need to get the property name associated with the INavigation (in my example, it's City), and inject it in the lambda.

To retrieve the navigation property name just use INavigation.Name

Here is a working example of this implementation:

public static class DbSetExtension
{
    public static IQueryable<T> DeepSearch<T>(this DbSet<T> dbSet, string search)
        where T : class
    {
        return DeepSearch(dbSet, dbSet, search);
    }

    public static IQueryable<T> DeepSearch<T>(this IQueryable<T> queryable, DbContext dbContext, string search)
        where T : class
    {
        var set = dbContext.Set<T>();
        return DeepSearch(queryable, set, search);
    }

    public static IQueryable<T> DeepSearch<T>(this IQueryable<T> queryable, DbSet<T> originalSet, string search)
        where T : class
    {
        var entityType = originalSet.EntityType;
        var containsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string) })!;

        // Ack to retrieve Enumerable.Any<>(IEnumerable<>, Func<,>)
        var anyMethod = typeof(Enumerable)
                       .GetMethods()
                       .Single(m => m.Name == "Any" &&
                                    m.GetParameters().Length == 2);

        // {x}
        var xParam = Expression.Parameter(typeof(T), "x");

        // {search}
        var constExpr = Expression.Constant(search);

        // {x.Name.Contains(search)} list
        var xDotNames = entityType.GetProperties()
                                  .Where(p => p.ClrType == typeof(string))
                                  .Select(p => Expression.Property(xParam, p.Name))
                                  .Select(e => (Expression)Expression.Call(e, containsMethod, constExpr));

        // {x.Navigation.Name.Contains(search)} list
        var xDotOtherDotNames = entityType.GetDeclaredNavigations()
                                          .Where(n => !n.IsCollection)
                                          .SelectMany(n => n.TargetEntityType
                                                            .GetProperties()
                                                            .Where(p => p.ClrType == typeof(string))
                                                            .Select(p => NestedProperty(xParam, n.Name, p.Name)))
                                          .Select(e => Expression.Call(e, containsMethod, constExpr));

        // {x.Navigations.Any(n => n.Name.Contains(search))} list
        var xDotOthersDotNames = entityType.GetDeclaredNavigations()
                                           .Where(n => n.IsCollection)
                                           .SelectMany(n =>
                                            {
                                                var nType = n.TargetEntityType.ClrType;

                                                // Enumerable.Any<NType>
                                                var genericAnyMethod = anyMethod.MakeGenericMethod(nType);

                                                // {n}
                                                var nParam = Expression.Parameter(nType, "n");

                                                // {x.Navigations}
                                                var xDotNavigations = Expression.Property(xParam, n.Name);

                                                return n.TargetEntityType
                                                        .GetProperties()
                                                        .Where(p => p.ClrType == typeof(string))
                                                        .Select(p =>
                                                         {
                                                             // {n.Name}
                                                             var nDotName = Expression.Property(nParam, p.Name);

                                                             // {n.Name.Contains(search)}
                                                             var contains =
                                                                 Expression.Call(nDotName, containsMethod, constExpr);

                                                             // {n => n.Name.Contains(search)}
                                                             var lambda = Expression.Lambda(contains, nParam);

                                                             // {Enumerable.Any(x.Navigations, n => n.Name.Contains(search))
                                                             return Expression.Call(null, genericAnyMethod, xDotNavigations, lambda);
                                                         });
                                            })
                                           .ToList();

        // { || ... }
        var orExpression = xDotNames.Concat(xDotOtherDotNames)
                                    .Concat(xDotOthersDotNames)
                                    .Aggregate(Expression.OrElse);

        var lambda = Expression.Lambda<Func<T, bool>>(orExpression, xParam);

        return queryable.Where(lambda);
    }

    private static Expression NestedProperty(Expression expression, params string[] propertyNames)
    {
        return propertyNames.Aggregate(expression, Expression.PropertyOrField);
    }
}
user_3611770
Related