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

Question

我在 Entity Framework 6 中使用自引用模型，我需要根据用户的选择从中选择实例。对于更大的选择，我得到一个异常(exception):“您的 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

我需要检索所选实例本身，以及所选实例的父项和子项。问题在于选择 parent 。这是一个 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]
    )

级别UnionAll<n>为选择中的每个 id 添加子查询。实际上，用户可能会选择多个 ID。当有超过 40 个 id 时，我会遇到一个有漏洞的抽象并得到异常。在任何情况下，它看起来都像是一个次优查询。

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

有人能想出一种方法来使用 Linq to entities 执行此操作，而无需为每个选择项发出查询吗？

Answer 1

可能是

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