条件组合函数构建LINQ过滤器遇EF翻译异常，求最优解决方法

需要实现TestEntity的条件查询：当Name、Type、Status均为null（或空字符串）时返回所有数据；当这些字段不为null/空时，用对应值过滤，忽略为null/空的字段。

TestEntityFilter定义：

public class TestEntityFilter
{
    public string Name { get; set; } = null!;
    public string Type { get; set; } = null!;
    public Status? Status { get; set; }
    public int Take { get; set; }
    public int PageNumber { get; set; }
}

尝试通过组合Func委托的方式实现查询，但抛出EF Core SQL翻译异常，实现代码如下：

public async Task<IEnumerable<TestEntity>> GetTestEntitiesAsync(TestEntityFilter testEntityFilter, CancellationToken cancellationToken)
{
    var combinedFilter = BuildFilers(testEntityFilter);
    return await _dbContext.TestEntity
        .Where(testEntity => combinedFilter(testEntity))
        .ToListAsync(cancellationToken);
}
    
public Func<TestEntity, bool> BuildFilers(TestEntityFilter testEntityFilter)    
{   
    Func<TestEntity, bool> mainFilter = testEntityFilter => true;
    Func<TestEntity, bool> filterByName = string.IsNullOrEmpty(testEntityFilter.Name) ? 
        (TestEntity j) => true :
        (TestEntity j) => testEntityFilter.Name == j.Name;

    Func<TestEntity, bool> filterByType = string.IsNullOrEmpty(testEntityFilter.Type) ?
        (TestEntity j) => true :
        (TestEntity j) => testEntityFilter.Type == j.Type; 

    Func<TestEntity, bool> filterByStatus = testEntityFilter.Status is null?
        (TestEntity j) => true :
        (TestEntity j) => testEntityFilter.Status == j.Status;

    Func<TestEntity, bool> combinedFilter = (TestEntity j) => mainFilter(j) && filterByName(j) && filterByType(j) && filterByStatus(j);
    return combinedFilter; // 原代码遗漏return语句，此处补充
}

抛出的异常信息：

'The LINQ expression 'DbSet<TestEntity>()
    .Where(j => Invoke(__combinedFilter_0, j))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

方式1：链式拼接Where（最直观易用） #

直接根据过滤条件动态拼接Where子句，EF Core会自动组合生成正确的SQL查询，性能最优且代码易读：

public async Task<IEnumerable<TestEntity>> GetTestEntitiesAsync(TestEntityFilter filter, CancellationToken cancellationToken)
{
    var query = _dbContext.TestEntity.AsQueryable();

    // 非空时应用Name过滤
    if (!string.IsNullOrEmpty(filter.Name))
    {
        query = query.Where(e => e.Name == filter.Name);
    }

    // 非空时应用Type过滤
    if (!string.IsNullOrEmpty(filter.Type))
    {
        query = query.Where(e => e.Type == filter.Type);
    }

    // 不为null时应用Status过滤
    if (filter.Status.HasValue)
    {
        query = query.Where(e => e.Status == filter.Status.Value);
    }

    // 按需添加分页逻辑
    if (filter.Take > 0 && filter.PageNumber > 0)
    {
        query = query.Skip((filter.PageNumber - 1) * filter.Take).Take(filter.Take);
    }

    return await query.ToListAsync(cancellationToken);
}

方式2：Expression表达式组合（适合复杂复用场景） #

如果过滤逻辑需要在多处复用，可使用Expression<Func<T, bool>>组合表达式树，EF Core能正常解析并翻译为SQL：

using System.Linq.Expressions;

public async Task<IEnumerable<TestEntity>> GetTestEntitiesAsync(TestEntityFilter filter, CancellationToken cancellationToken)
{
    var filterExpressions = new List<Expression<Func<TestEntity, bool>>>();

    if (!string.IsNullOrEmpty(filter.Name))
    {
        filterExpressions.Add(e => e.Name == filter.Name);
    }

    if (!string.IsNullOrEmpty(filter.Type))
    {
        filterExpressions.Add(e => e.Type == filter.Type);
    }

    if (filter.Status.HasValue)
    {
        filterExpressions.Add(e => e.Status == filter.Status.Value);
    }

    var combinedExpression = CombineFilterExpressions(filterExpressions);
    var query = combinedExpression != null 
        ? _dbContext.TestEntity.Where(combinedExpression) 
        : _dbContext.TestEntity;

    // 分页逻辑
    if (filter.Take > 0 && filter.PageNumber > 0)
    {
        query = query.Skip((filter.PageNumber - 1) * filter.Take).Take(filter.Take);
    }

    return await query.ToListAsync(cancellationToken);
}

// 组合多个表达式为AndAlso逻辑
private Expression<Func<T, bool>> CombineFilterExpressions<T>(List<Expression<Func<T, bool>>> expressions)
{
    if (expressions.Count == 0) return null;

    var combined = expressions[0];
    for (int i = 1; i < expressions.Count; i++)
    {
        combined = CombineAndAlso(combined, expressions[i]);
    }
    return combined;
}

// 合并两个表达式的AndAlso逻辑
private Expression<Func<T, bool>> CombineAndAlso<T>(Expression<Func<T, bool>> left, Expression<Func<T, bool>> right)
{
    var parameter = Expression.Parameter(typeof(T), "e");
    var parameterReplacer = new ParameterReplacer(right.Parameters[0], parameter);
    var rightBody = parameterReplacer.Visit(right.Body);
    var combinedBody = Expression.AndAlso(left.Body, rightBody);
    return Expression.Lambda<Func<T, bool>>(combinedBody, parameter);
}

// 替换表达式中的参数，解决参数不一致问题
private class ParameterReplacer : ExpressionVisitor
{
    private readonly ParameterExpression _oldParam;
    private readonly ParameterExpression _newParam;

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

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

原代码报错原因 #

EF Core无法翻译Func<TestEntity, bool>类型的委托，因为它是客户端执行的逻辑，EF Core无法将其转换为SQL语句。必须使用Expression<Func<TestEntity, bool>>类型的表达式树，EF Core才能解析并生成对应的SQL查询。此外原代码的BuildFilers方法遗漏了return combinedFilter;语句，也是导致问题的一个原因。

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