How to get a tree structured table data by linq?

Question

I have a table which has a tree structure in itself.

Id ParentId Name
----------------
1    null   x
2    null   y
3    null   z
4    null   t
5     1     xx
6     1     xy
7     1     xz
8     2     yx
9     2     yy
10    9     yyx
11    10    yyxx
12    11    yyxxx

I want to retrieve the whole sub-tree under a root node. When my root node is "x" I want to get the set of nodes {1, 5, 6, 7, 10, 11, 12}. How can I do that by linq?

Answer 1

If you're able the change the table structure to add extra fields, then one approach I have used in the past is to have a "Path" field, which holds a comma separated list of IDs.

ID    ParentID    Name      Path
--    --------    ----      ----
1     null        x         1
2     null        y         2
3     null        z         3
4     null        t         4
5     1           xx        1,5
6     1           xy        1,6
7     1           xz        1,7
8     2           yx        2,8
9     2           yy        2,9
10    9           yyx       2,9,10
11    10          yyxx      2,9,10,11
12    11          yyxxx     2,9,10,11,12

Then you can query based on the Path field using LIKE (or StartsWith in Linq)

In your question you say you want to get { 1, 5, 6, 7, 10, 11, 12 }, but those IDs are part of two different sub-trees, if I've read it right.

To get "x" and all it's children ...

where Path = "1" || Path.StartsWith("1,")

To just get x's children ...

where Path.StartsWith("1,")

Answer 2

    /// <summary>
    /// Allows to recursively select descendants as plain collection
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="source"></param>
    /// <param name="DescendBy"></param>
    /// <returns></returns>

    public static IEnumerable<T> Descendants<T>(
        this IEnumerable<T> source, Func<T, IEnumerable<T>> DescendBy)
    {
        foreach (T value in source)
        {
            yield return value;

            foreach (var child in DescendBy(value).Descendants(DescendBy))
            {
                yield return child;
            }
        }
    }

usage: node.children.Descendants(node=>node.children);