如何生成表达式以查询匹配的实体或其相关实体的任何成员？

Question

我使用以下代码来对我的EFCORE数据进行搜索。由于数据设置如此之大，因此我必须开始使用动态 /通用类型。我已经能够查询实体级别的属性，但是我正在努力查询本来可以定义为的实体

。代码以及第二个标记为“本节无效”的代码以显示我的想法。我知道它并不完美，但对于我们的内部用例，它非常有效。大多数人只是一遍又一遍地使用基本的字符串搜索。

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));
}

Answer 1

如果我做对了，您的目标是生成一个等同于这样的查询：

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

在不起作用的一部分中，

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

我认为它会生成u.cityname而不是<代码> u.city.cityname 。

您需要获得与Invigation关联的属性名称（在我的示例中，它是city），然后将其注入lambda。

要检索导航属性名称，只需使用inavigation.name

在这里/a>是此实现的工作示例：

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);
    }
}