如何解决 EF 生成嵌套太深的 SQL 语句

本文关键字：嵌套 语句 SQL 何解决 解决 EF | 更新日期: 2023-09-27 18:31:59

我在实体框架 6 中使用自引用模型，我需要根据用户的选择从中选择实例。对于更大的选择，我得到一个例外："你的SQL语句的某些部分嵌套得太深了。重写查询或将其分解为较小的查询。

这是我的模型的简化版本：

public class Hierarchy
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? ParentId { get; set; }
    public virtual Hierarchy Parent { get; set; }
    public virtual ICollection<Hierarchy> Children { get; set; }
}

用户的选择包含许多 id。

我的数据库已设定种子

Id  Name        ParentId
1   Root        NULL
2   parent-1    1
3   item-1-1    2
4   item-1-2    2
5   parent-2    1
6   item-2-1    2
7   item-2-2    2
8   child-1-1-1 3
9   child-1-1-2 3
10  child-1-2-1 4
11  child-1-2-2 4
12  child-2-1-1 3
13  child-2-1-2 3
14  child-2-2-1 4
15  child-2-2-2 4

我需要检索所选实例本身，以及所选实例的父项和子项。问题在于选择父母。这是一个 Linq 查询，它执行以下操作：

public static List<Hierarchy> Select(List<int> selection)
{
        var result = context.Hierarchy
            .Where(sub => sub.Children.Select(csub => csub.Id).Intersect(selection).Any());
}

不幸的是，它被转换为丑陋的SQL语句。呼叫时 

var test = Hierarchy.Select(new List<int>() { 2, 6, 11 });

这将转换为：

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[ParentId] AS [ParentId]
    FROM [dbo].[Hierarchy] AS [Extent1]
    WHERE  EXISTS (SELECT 
        1 AS [C1]
        FROM  (SELECT 
            [Extent2].[Id] AS [Id]
            FROM [dbo].[Hierarchy] AS [Extent2]
            WHERE [Extent1].[Id] = [Extent2].[ParentId]
        INTERSECT
            SELECT 
            [UnionAll2].[C1] AS [C1]
            FROM  (SELECT 
                [UnionAll1].[C1] AS [C1]
                FROM  (SELECT 
                    2 AS [C1]
                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
                UNION ALL
                    SELECT 
                    6 AS [C1]
                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1]
            UNION ALL
                SELECT 
                11 AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2]) AS [Intersect1]
    )

将为所选内容中的每个 id 添加UnionAll<n>子查询级别。实际上，用户可以选择许多id。当有超过 40 个 id 时，我遇到了一个泄漏的抽象并得到异常。无论如何，它看起来像一个次优查询。

从本质上讲，我的查询需要查找具有任何选定项目作为子项的所有实例。这涉及为每个实例确定两个列表的交集：所选 id 的本地列表和数据库中每个实例的子实例列表。

任何人都可以想到一种方法来使用 Linq 对实体执行此操作，而无需为每个选择项发出查询？

如何解决 EF 生成嵌套太深的 SQL 语句

可能是

public static List<Hierarchy> Select(List<int> selection)
{
    var result = context.Hierarchy
        .Where(sub => sub.Children.Any(csub => selection.Contains(csub.Id)));
}
相关文章：