C#实体框架核心条件投影

Question

我目前使用 Entity Framework Core 技术，其效果非常好。然而，我正在尝试优化我的应用程序，以便在查询时从数据库返回计算数据。我正在使用 Code First 技术，其中每个模型直接映射到单个表。

以下是我持久化模型的简化示例：

public class User
{
    public int Id { get; set; }

    public string Name { get; set; }

    public ICollection<UserRole> Roles { get; set; }
}

public class UserRole
{
    public int Id { get; set; }

    public int UserId { get; set; }

    public User User { get; set; }

    public string Role { get; set; }
}

我现在使用的是规范模式的变体，使我能够在执行查询之前运行可变数量的.Include / .ThenInclude。然而，我想要做的是有条件地启用投影的特定部分。

例如，这是我的领域模型的样子：

public class UserImpl
{
    public User User { get; set; }

    public int? RoleCount { get; set; }

    public static Expression<Func<User, UserImpl>> Projection(UserImplParams opts) {
        return u => new UserImpl
        {
            User = u,
            RoleCount = opts != null && opts.IncludeRoleCount ? u.Roles.Count() : default(int?)
        };
    }
}

public class UserImplParams
{
    public bool IncludeRoleCount { get; set; }
}

我希望实现的是类似于以下方式的操作：

var opts= new UserImplParams
{
    IncludeUserRole = true
};

await _databaseContext.Users.Select(UserImpl.Projection(opts)).ToListAsync();

我希望EF Core能够看到以下两种情况之一：

u => new UserImpl
{
    User = u,
    RoleCount = u.Roles.Count()
};

或者

u => new UserImpl
{
    User = u,
    RoleCount = default(int?)
};

这是可能的吗？主要是因为此表达式可能包含多个投影属性，甚至包含嵌套的属性。每次仅针对少量数据将整个表达式发送到数据库似乎效率低下。

- Eliott Robson

---

这里有很多细节，但我不是很明白。我知道你在有条件地决定是否包含某些内容，但逻辑完全混乱（正在进行中）。首先，你如何获得UserImpl的实例，以及UserImplParams是什么。基本上，我会说“不”，你在一次混合了很多东西。只需为应该包含和不应该包含的内容分别调用逻辑即可。 - Seabizkit

我更新了问题，UserImplParams 是什么并不重要，只是为了完整起见而添加。我们的思路是如何构建动态表达式？我需要实现一个表达式访问器吗？ - Eliott Robson

你应该研究一下GraphQL。 - Seabizkit

Answer 1

[编辑] 我在我的网站上发布了一个更加更新的版本的代码 https://eliottrobson.me/entity-framework-core-projection-performance/ 它基本上是相同的，但增加了对更多场景的支持。

---

我想这样做的原因部分是由于过早优化。我相信，在90%的情况下，发送一个带有CASE WHEN 1=1或1=0（用于真和假）的大型SQL将被正确优化。然而，事实是CASE语句并不总是短路https://dba.stackexchange.com/questions/12941/does-sql-server-read-all-of-a-coalesce-function-even-if-the-first-argument-is-no/12945#12945。

没有多说，这是我的解决方案。

主要功能在这个新类中：

public class ProjectionExpressionVisitor : ExpressionVisitor
{
    internal Expression<Func<TSource, TDest>> Optimise<TSource, TDest>(Expression<Func<TSource, TDest>> expression)
    {
        return Visit(expression) as Expression<Func<TSource, TDest>>;
    }

    protected override Expression VisitConditional(ConditionalExpression node)
    {
        var test = ReduceExpression(node.Test);

        // The conditional is now a constant, we can replace the branch
        if (test is ConstantExpression testNode)
        {
            var value = (dynamic) testNode.Value;
            return value ? ReduceExpression(node.IfTrue) : ReduceExpression(node.IfFalse);
        }

        // If it is not a conditional, we follow the default behaviour
        return base.VisitConditional(node);
    }

    public Expression ReduceExpression(Expression node)
    {
        if (node is ConstantExpression)
        {
            // Constants represent the smallest item, so we can just return it
            return node;
        }
        else if (node is MemberExpression memberNode)
        {
            return ReduceMemberExpression(memberNode);
        }
        else if (node is BinaryExpression binaryNode)
        {
            return ReduceBinaryExpression(binaryNode);
        }

        // This is not a supported expression type to reduce, fallback to default
        return node;
    }

    public Expression ReduceMemberExpression(MemberExpression node)
    {
        if (
            node.Expression.NodeType == ExpressionType.Constant ||
            node.Expression.NodeType == ExpressionType.MemberAccess
        )
        {
            var objectMember = Expression.Convert(node, typeof(object));
            var getterLambda = Expression.Lambda<Func<object>>(objectMember);
            var getter = getterLambda.Compile();
            var value = getter();

            return Expression.Constant(value);
        }

        return node;
    }

    public Expression ReduceBinaryExpression(BinaryExpression node)
    {
        var left = ReduceExpression(node.Left);
        var right = ReduceExpression(node.Right);

        var leftConst = left as ConstantExpression;
        var rightConst = right as ConstantExpression;

        // Special optimisations
        var optimised = OptimiseBooleanBinaryExpression(node.NodeType, leftConst, rightConst);
        if (optimised != null) return Expression.Constant(optimised);

        if (leftConst != null && rightConst != null)
        {
            var leftValue = (dynamic)leftConst.Value;
            var rightValue = (dynamic)rightConst.Value;

            switch (node.NodeType)
            {
                case ExpressionType.Add:
                    return Expression.Constant(leftValue + rightValue);
                case ExpressionType.Divide:
                    return Expression.Constant(leftValue / rightValue);
                case ExpressionType.Modulo:
                    return Expression.Constant(leftValue % rightValue);
                case ExpressionType.Multiply:
                    return Expression.Constant(leftValue * rightValue);
                case ExpressionType.Power:
                    return Expression.Constant(leftValue ^ rightValue);
                case ExpressionType.Subtract:
                    return Expression.Constant(leftValue - rightValue);
                case ExpressionType.And:
                    return Expression.Constant(leftValue & rightValue);
                case ExpressionType.AndAlso:
                    return Expression.Constant(leftValue && rightValue);
                case ExpressionType.Or:
                    return Expression.Constant(leftValue | rightValue);
                case ExpressionType.OrElse:
                    return Expression.Constant(leftValue || rightValue);
                case ExpressionType.Equal:
                    return Expression.Constant(leftValue == rightValue);
                case ExpressionType.NotEqual:
                    return Expression.Constant(leftValue != rightValue);
                case ExpressionType.GreaterThan:
                    return Expression.Constant(leftValue > rightValue);
                case ExpressionType.GreaterThanOrEqual:
                    return Expression.Constant(leftValue >= rightValue);
                case ExpressionType.LessThan:
                    return Expression.Constant(leftValue < rightValue);
                case ExpressionType.LessThanOrEqual:
                    return Expression.Constant(leftValue <= rightValue);
            }
        }

        return node;
    }

    private bool? OptimiseBooleanBinaryExpression(ExpressionType type, ConstantExpression leftConst, ConstantExpression rightConst)
    {
        // This is only a necessary optimisation when only part of the binary expression is constant
        if (leftConst != null && rightConst != null)
            return null;

        var leftValue = (dynamic)leftConst?.Value;
        var rightValue = (dynamic)rightConst?.Value;

        // We can check for constants on each side to simplify the reduction process
        if (
            (type == ExpressionType.And || type == ExpressionType.AndAlso) &&
            (leftValue == false || rightValue == false))
        {
            return false;
        }
        else if (
            (type == ExpressionType.Or || type == ExpressionType.OrElse) &&
            (leftValue == true || rightValue == true))
        {
            return true;
        }

        return null;
    }
}

基本上，我们的想法是通过尽可能减少条件表达式来优化它们，然后在混合参数lambda时应用一些特殊情况逻辑。

使用方法如下：

var opts = new UserImplParams
{
    IncludeUserRole = true
};

var projection = UserImpl.Projection(opts);

var expression = new ProjectionExpressionVisitor().Optimise(projection);

await _databaseContext.Users.Select(expression).ToListAsync();

希望这能帮助其他遇到类似问题的人。

- Eliott Robson

---

链接已失效。 - Edward Olamisan

Answer 2

你可以先有条件地更改组名（在第一个选择中），然后再次分组，
现在你有了两种类型的组

            .GroupBy(x => new {x.Brand})
            .Select(x => new DisputeReportListModel
            {
                Amount = x.Sum(y => y.Amount),
                Scheme = _isMastercard(x.Key.Brand) ? "MASTERCARD" : "VISA",
            }).AsEnumerable()
            .GroupBy(x => new {x.Scheme})
            .Select(x => new DisputeReportListModel
            {
                Amount = x.Sum(y => y.Amount),
                Scheme = x.Key.Scheme
            })
            .ToList();

- aris