sql-server - 向投影添加更多子查询时 SQL 语句嵌套太深

Question

复制项目在 github 上可用，使用AdventureWorks2016数据库。GitHub 复制

我们有自定义过滤机制来满足我们的需求。它的作用是 - 它获取输入数据，使用完整查询构建表达式树并将其传递EntityFramework给执行。我们有两个部分的查询——获取基本实体和获取一些额外的数据值，在最终投影中表示为子查询。

问题：
当获得超过 20 个子查询时，SqlServer会引发错误：

您的 SQL 语句的某些部分嵌套得太深。重写查询或将其分解为更小的查询。

经过仔细调查，结果发现与此类似的查询：

var products = db.Products.Where(p => productIds.Contains(p.ProductID))
                .Select(p => new
                {
                    Entity = p,
                    Extras = new
                    {
                        TotalTransactions = p.TransactionHistories.Count(),
                        TotalCostChanges = p.ProductCostHistories.Count(),
                        AverageTransactionCost = p.TransactionHistories.Average(t => t.Quantity * t.ActualCost),
                        MaxQuantity = (int?)p.TransactionHistories.Max(t => t.Quantity)
                    }
                });

导致生成如下 SQL 查询：

SELECT 
    [Project3].[ProductID] AS [ProductID], 
    [Project3].[Name] AS [Name], 
    [Project3].[C1] AS [C1], 
    [Project3].[C2] AS [C2], 
    [Project3].[C3] AS [C3], 
    (SELECT 
        MAX([Extent5].[Quantity]) AS [A1]
        FROM [Production].[TransactionHistory] AS [Extent5]
        WHERE [Project3].[ProductID] = [Extent5].[ProductID]) AS [C4]
    FROM ( SELECT 
        [Project2].[ProductID] AS [ProductID], 
        [Project2].[Name] AS [Name], 
        [Project2].[C1] AS [C1], 
        [Project2].[C2] AS [C2], 
        (SELECT 
            AVG([Filter4].[A1]) AS [A1]
            FROM ( SELECT 
                 CAST( [Extent4].[Quantity] AS decimal(19,0)) * [Extent4].[ActualCost] AS [A1]
                FROM [Production].[TransactionHistory] AS [Extent4]
                WHERE [Project2].[ProductID] = [Extent4].[ProductID]
            )  AS [Filter4]) AS [C3]
        FROM ( SELECT 
            [Project1].[ProductID] AS [ProductID], 
            [Project1].[Name] AS [Name], 
            [Project1].[C1] AS [C1], 
            (SELECT 
                COUNT(1) AS [A1]
                FROM [Production].[ProductCostHistory] AS [Extent3]
                WHERE [Project1].[ProductID] = [Extent3].[ProductID]) AS [C2]
            FROM ( SELECT 
                [Extent1].[ProductID] AS [ProductID], 
                [Extent1].[Name] AS [Name], 
                (SELECT 
                    COUNT(1) AS [A1]
                    FROM [Production].[TransactionHistory] AS [Extent2]
                    WHERE [Extent1].[ProductID] = [Extent2].[ProductID]) AS [C1]
                FROM [Production].[Product] AS [Extent1]
                WHERE [Extent1].[ProductID] IN (707, 708, 709, 711)
            )  AS [Project1]
        )  AS [Project2]
    )  AS [Project3]

将每个属性添加到 Extras 后，就会再创建一个嵌套查询。

有什么方法EntityFramework可以生成更好的查询（请参阅：所有那些对值 C1、C2... 的嵌套查询都可以表示为主选择中的简单子查询）或者应该以某种完全不同的方式创建这种查询?

标签： sql-serverentity-frameworkentity-framework-6