How to work around EF generating SQL statement nested too deeply

Question

I am using a self-referencing model in Entity Framework 6, from which I need to select instances, based on a selection by the user. For larger selections, I get an exception: "Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries."

This is a simplified version of my model:

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; }
}

The selection by the user contains a number of id's.

My database is seeded with

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

I need to retrieve the selected instances themselves, as well as, the parents and the children of selected instances. The problem is in selecting the parents. This is a Linq query, which does that:

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

Unfortunately, it gets converted to an ugly SQL statement. When calling

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

this gets converted to:

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]
    )

A level of UnionAll<n> subqueries is added for each id in the selection. In practice, the user may select many id's. When there are more than some 40 id's, I bump into a leaky abstraction and get the exception. In any case, it looks like a sub-optimal query.

Essentially, my query needs to find all instances that have any of the selected items as a child. This involves determining, for each instance, the intersection of two lists: a local list of selected id's and the list of children of each instance in the database.

Can anybody think of a way to do this with Linq to entities, without emitting a query for each select item?

Answer 1

may be

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