How to get a tree structured table data by linq?

问题:

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?

回答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,")

回答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);

回答3:

You need to Perform Inner Join In Linq with the Table Itself as follows :

from root in TableName 
join subnodes in TableName on root.Id equals subnodes.ParentId
select new { Name }

This will retrieve all those records which parent id matches with Id with same table renamed as subnodes

Thanks