EF Core查询过滤器中添加空值检查的最优实现方案咨询

需求说明 #

当DbContext中的指定属性（如TenantId）有值时，查询需要返回两类记录：

• 实体中对应属性值与DbContext属性值匹配的记录
• 实体中对应属性值为null的记录

举个例子：
Flat表有3条记录：

• Flat 1：TenantId = 222
• Flat 2：TenantId = 333
• Flat 3：TenantId = NULL

当DbContext的TenantId设为333时，查询需返回Flat 2和Flat 3。

现有实现代码 #

我已编写泛型扩展方法，通过传入属性名与过滤值表达式的字典来实现通用过滤，核心代码如下：

public static class ModelBuilderExtensions
{
   public static void ApplyQueryFiltersForProperties<TProp>(this ModelBuilder builder,
        Dictionary<string, Expression<Func<TProp>>> propertiesToFilter)
    {
        foreach (var (propertyName, expression) in propertiesToFilter)
        {
            foreach (var entityType in builder.Model.GetEntityTypes())
            {
                var tenantProp = entityType.GetProperties().FirstOrDefault(p => p.Name == propertyName);
                if (tenantProp == null)
                    continue;

                var entityParam = Expression.Parameter(entityType.ClrType, "e");

                var contextPropertyAccess = expression.Body;

                var propertyExpression = GetPropertyExpression(entityParam, tenantProp);
                if (propertyExpression.Type != contextPropertyAccess.Type)
                    propertyExpression = Expression.Convert(propertyExpression, contextPropertyAccess.Type);

                // ctx.Property == null || ctx.Property == e.Property
                var filterBody = (Expression) Expression.OrElse(
                    Expression.Equal(contextPropertyAccess, Expression.Default(contextPropertyAccess.Type)),
                    Expression.Equal(contextPropertyAccess, propertyExpression));

                // 添加：实体属性为null时也纳入结果
                filterBody = (Expression) Expression.Or(
                    filterBody,
                    Expression.Equal(propertyExpression, Expression.Default(contextPropertyAccess.Type)));

                var filterLambda = entityType.GetQueryFilter();

                // 合并已有过滤器
                if (filterLambda != null)
                {
                    filterBody = ReplacingExpressionVisitor.Replace(entityParam, filterLambda.Parameters[0], filterBody);
                    filterBody = Expression.AndAlso(filterLambda.Body, filterBody);
                    filterLambda = Expression.Lambda(filterBody, filterLambda.Parameters);
                }
                else
                {
                    filterLambda = Expression.Lambda(filterBody, entityParam);
                }

                entityType.SetQueryFilter(filterLambda);
            }
        }
    }

    private static Expression GetPropertyExpression(Expression objExpression, IProperty property)
    {
        Expression propExpression;
        if (property.PropertyInfo == null)
        {
            // 影子属性，通过EF.Property访问
            propExpression = Expression.Call(typeof(EF), nameof(EF.Property), new[] {property.ClrType},
                objExpression, Expression.Constant(property.Name));
        }
        else
        {
            // 常规属性，直接访问成员
            propExpression = Expression.MakeMemberAccess(objExpression, property.PropertyInfo);
        }

        return propExpression;
    }
}

当前问题 #

上述代码逻辑能满足需求，但生成的SQL语句极为复杂，采用了多个CASE语句结合按位或的写法，可能存在性能隐患：

WHERE ((CASE
          WHEN (@__ef_filter__p_0 = CAST(1 AS bit)) OR ((@__ef_filter__TenantId_1 = [b].[TenantId]) AND [b].[TenantId] IS NOT NULL) THEN CAST(1 AS bit)
          ELSE CAST(0 AS bit)
      END | CASE
          WHEN [b].[TenantId] IS NULL THEN CAST(1 AS bit)
          ELSE CAST(0 AS bit)
      END) = CAST(1 AS bit))

优化咨询 #

当前实现是否为最优方案？有没有更高效的方式来实现这个需求？

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