连接表查询中动态Where子句的实现方案咨询

核心思路 #

利用LINQ表达式树（Expression Tree）动态构建谓词，通过And/Or组合多组条件，最终将组合后的谓词应用到查询上。需要提前引入System.Linq.Expressions命名空间。

具体实现步骤 #

1. 初始化基础谓词 #

先创建一个始终返回true的初始谓词，作为后续条件拼接的基础：

using System.Linq.Expressions;

// 初始谓词：默认匹配所有数据
Expression<Func<CollectionNPictures, bool>> predicate = c => true;

2. 实现And/Or拼接的扩展方法 #

需要手动编写扩展方法来处理表达式树的组合，避免参数不匹配问题：

public static class ExpressionExtensions
{
    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> left, Expression<Func<T, bool>> right)
    {
        var parameter = Expression.Parameter(typeof(T));
        var visitor = new ParameterReplaceVisitor(right.Parameters[0], parameter);

        var leftBody = visitor.Visit(left.Body);
        var rightBody = visitor.Visit(right.Body);
        var combined = Expression.AndAlso(leftBody, rightBody);

        return Expression.Lambda<Func<T, bool>>(combined, parameter);
    }

    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> left, Expression<Func<T, bool>> right)
    {
        var parameter = Expression.Parameter(typeof(T));
        var visitor = new ParameterReplaceVisitor(right.Parameters[0], parameter);

        var leftBody = visitor.Visit(left.Body);
        var rightBody = visitor.Visit(right.Body);
        var combined = Expression.OrElse(leftBody, rightBody);

        return Expression.Lambda<Func<T, bool>>(combined, parameter);
    }

    private class ParameterReplaceVisitor : ExpressionVisitor
    {
        private readonly ParameterExpression _oldParam;
        private readonly ParameterExpression _newParam;

        public ParameterReplaceVisitor(ParameterExpression oldParam, ParameterExpression newParam)
        {
            _oldParam = oldParam;
            _newParam = newParam;
        }

        protected override Expression VisitParameter(ParameterExpression node)
        {
            return node == _oldParam ? _newParam : base.VisitParameter(node);
        }
    }
}

3. 动态拼接条件 #

假设你有一组搜索条件（比如键值对，键为实体属性名，值为搜索内容），循环遍历条件并拼接：

示例：针对Collection表的列拼接Contains条件

// 模拟搜索条件：键是Collection的属性名，值是要搜索的内容
var collectionSearchConditions = new Dictionary<string, string>
{
    { "Name", "test" },
    { "Description", "sample" }
};

foreach (var condition in collectionSearchConditions)
{
    var columnName = condition.Key;
    var searchValue = condition.Value;

    // 构建参数表达式
    var parameter = Expression.Parameter(typeof(CollectionNPictures), "c");
    // 获取c.Collection的表达式
    var collectionProperty = Expression.Property(parameter, nameof(CollectionNPictures.Collection));
    // 获取Collection对应属性的表达式
    var columnProperty = Expression.Property(collectionProperty, columnName);
    // 构建Contains方法调用表达式
    var containsMethod = typeof(string).GetMethod(nameof(string.Contains), new[] { typeof(string) });
    var searchValueConstant = Expression.Constant(searchValue, typeof(string));
    var containsExpression = Expression.Call(columnProperty, containsMethod, searchValueConstant);

    // 转换为完整的谓词表达式
    var currentPredicate = Expression.Lambda<Func<CollectionNPictures, bool>>(containsExpression, parameter);

    // 用And拼接当前条件到总谓词
    predicate = predicate.And(currentPredicate);
}

示例：针对Pictures表的列拼接条件

逻辑与Collection表一致，只需调整属性获取路径：

var picturesSearchConditions = new Dictionary<string, string>
{
    { "FileName", "image" },
    { "Tag", "nature" }
};

foreach (var condition in picturesSearchConditions)
{
    var columnName = condition.Key;
    var searchValue = condition.Value;

    var parameter = Expression.Parameter(typeof(CollectionNPictures), "c");
    var picturesProperty = Expression.Property(parameter, nameof(CollectionNPictures.Pictures));
    var columnProperty = Expression.Property(picturesProperty, columnName);
    var containsMethod = typeof(string).GetMethod(nameof(string.Contains), new[] { typeof(string) });
    var searchValueConstant = Expression.Constant(searchValue, typeof(string));
    var containsExpression = Expression.Call(columnProperty, containsMethod, searchValueConstant);

    var currentPredicate = Expression.Lambda<Func<CollectionNPictures, bool>>(containsExpression, parameter);

    predicate = predicate.And(currentPredicate);
}

4. 将谓词应用到查询 #

把拼接好的动态条件加入到原有查询中即可：

var baseQuery = from collection in _context.Collection
                join pictures in _context.Pictures
                on collection.Nummer equals pictures.Nummer
                select new CollectionNPictures { Collection = collection, Pictures = pictures };

// 应用动态过滤条件
var filteredQuery = baseQuery.Where(predicate);

// 执行查询（示例：转为列表）
var result = filteredQuery.ToList();

注意事项 #

• 确保属性名与实体类定义完全一致，否则Expression.Property会抛出异常。
• 若属性类型不是字符串，需调整条件逻辑（比如数字类型用==/>等比较，避免调用Contains）。
• 如需不区分大小写的模糊搜索，可将Contains替换为IndexOf：

  var indexOfMethod = typeof(string).GetMethod(nameof(string.IndexOf), new[] { typeof(string), typeof(StringComparison) });
  var containsExpression = Expression.GreaterThan(
      Expression.Call(columnProperty, indexOfMethod, searchValueConstant, Expression.Constant(StringComparison.OrdinalIgnoreCase)),
      Expression.Constant(-1)
  );
  

内容的提问来源于stack exchange，提问作者mark_da_rat