Linq-To-Sql: Recursively Get Children

Recursive linq to get infinite children

can you try this..

 public IEnumerable<Location> GetChild(int id)
        {
            DBEntities db = new DBEntities();
        var locations = db.Locations.Where(x => x.ParentLocationID == id || x.LocationID == id).ToList();

    var child = locations.AsEnumerable().Union(
                                db.Locations.AsEnumerable().Where(x => x.ParentLocationID == id).SelectMany(y => GetChild(y.LocationId))).ToList();
            return child;
        }

Linq recursive parent child

For linq-to-objects you can define your own extension method on IEnumerable<T> that recursively gets all children.

public static class EnumerableExtensions
{
    public static IEnumerable<T> SelectRecursive<T>(this IEnumerable<T> source, Func<T, IEnumerable<T>> selector)
    {
        foreach (var parent in source)
        {
            yield return parent;

            var children = selector(parent);
            foreach (var child in SelectRecursive(children, selector))
                yield return child;
        }
    }
}

Usage:

var lookup = col.ToLookup(x => x.Parent_Id);
var res = lookup[null].SelectRecursive(x => lookup[x.Id]).ToList();

Recursive LINQ query: select item and all children with subchildren

   public class Comment
    {
        public int Id { get; set; }
        public int ParentId { get; set; }
        public string Text { get; set; }        
        public List<Comment> Children { get; set; }
    }

    class Program
    {
        static void Main()
        {
        List<Comment> categories = new List<Comment>()
            {
                new Comment () { Id = 1, Text = "Item 1", ParentId = 0},
                new Comment() { Id = 2, Text = "Item 2", ParentId = 0 },
                new Comment() { Id = 3, Text = "Item 3", ParentId = 0 },
                new Comment() { Id = 4, Text = "Item 1.1", ParentId = 1 },
                new Comment() { Id = 5, Text = "Item 3.1", ParentId = 3 },
                new Comment() { Id = 6, Text = "Item 1.1.1", ParentId = 4 },
                new Comment() { Id = 7, Text = "Item 2.1", ParentId = 2 }
            };

            List<Comment> hierarchy = new List<Comment>();
            hierarchy = categories
                            .Where(c => c.ParentId == 0)
                            .Select(c => new Comment() { 
                                  Id = c.Id, 
                                  Text = c.Text, 
                                  ParentId = c.ParentId, 
                                  Children = GetChildren(categories, c.Id) })
                            .ToList();

            HieararchyWalk(hierarchy);

            Console.ReadLine();
        }

        public static List<Comment> GetChildren(List<Comment> comments, int parentId)
        {
            return comments
                    .Where(c => c.ParentId == parentId)
                    .Select(c => new Comment { 
                        Id = c.Id, 
                        Text = c.Text, 
                        ParentId = c.ParentId, 
                        Children = GetChildren(comments, c.Id) })
                    .ToList();
        }

        public static void HieararchyWalk(List<Comment> hierarchy)
        {
            if (hierarchy != null)
            {
                foreach (var item in hierarchy)
                {
                    Console.WriteLine(string.Format("{0} {1}", item.Id, item.Text));
                    HieararchyWalk(item.Children);
                }
            }
        }

Get All Children to One List - Recursive C#

You can do SelectMany

List<Location> result = myLocationList.SelectMany(x => x.Children).ToList();

You can use where condition for some selective results like

List<Location> result = myLocationList.Where(y => y.ParentID == someValue)
                                      .SelectMany(x => x.Children).ToList();

If you only required Id's of Children you can do

List<long> idResult = myLocationList.SelectMany(x => x.Children)
                                    .SelectMany(x => x.ID).ToList();

How do I select recursive nested entities using LINQ to Entity

In his blog post Traverse a hierarchical structure with LINQ-to-Hierarchical
, Arjan Einbu describes a method of flattening hierarchies for ease of querying:

Can I make a generic extension method that will flatten any hierarchy? [...]

To do that, we need to analyze which parts of the method needs to be swapped out. That would be the TreeNode’s Nodes property. Can we access that in an other way? Yes, I think a delegate can help us, so lets give it a try:

public static IEnumerable<T> FlattenHierarchy<T>(this T node, 
                             Func<T, IEnumerable<T>> getChildEnumerator)
{
    yield return node;
    if(getChildEnumerator(node) != null)
    {
        foreach(var child in getChildEnumerator(node))
        {
            foreach(var childOrDescendant 
                      in child.FlattenHierarchy(getChildEnumerator))
            {
                yield return childOrDescendant;
            }
        }
    }
}

casperOne describes this in his answer as well, along with the problems inherent in trying to traverse the hierarchy directly using LINQ.

Retrieve all Children and their Children, recursive SQL

In SQL, you could query using a CTE. For example, to retrieve a list of nodes with their parent and the highest parent in their tree:

declare @t table (id int, parent int)
insert @t (id, parent) values (1, null), (2,1), (3,2), (4,3), (5,null), (6,5)

; with cte as (
    select  id, parent, id as head
    from    @t
    where   parent is null
    union all
    select  child.id, child.parent, parent.head
    from    @t child
    join    cte parent
    on      parent.id = child.parent
)
select  *
from    cte

This gives:

id  parent  head
1   NULL    1
2   1       1
3   2       1
4   3       1
5   NULL    5
6   5       5

Note that I changed your example data so row 2 is no longer a child of itself, but a child of row 1.

Get unknown amount of parents using LINQ to SQL

You'll need a loop (or other form of recursion).

var personId = 3 as int?;
var result = new List<Person>();
do
{
    var person = context.Persons.Single(p => p.PersonId == personId.Value);
    result.Add(person);
    personId = person.ParentId;
} while (personId != null);
// all ancestors are in `result`

Recursive query to retrieve all child ids below

Edit: As you commented below, it turned out your problem on the level was what I could not understand.

As you have probably understood, a recursive CTE is divided into 2 parts.

WITH RECURSIVE MyCTE AS (
    <Start of the recursion/loop>
    UNION
    <Step from level N to level N+1/N-1>
)

We will:

1. Change the starting point of the recursion to start from the correct manager, not the guy on top of the hierarchy.
2. Browse into the hierarchy the same way you did
3. Change the loop so that a branch is extended back to the top boss, for the purpose of calculating the level correctly.

Postgresql only allows 1 recursive CTE (not sure for other DBMS), so we will need to do 2 and 3 together. We only need a little extra caution to allow the query to start with several starting point (id_manager IN (...))

WITH RECURSIVE tree(id_root_manager, id_direct_manager, id_employee, level) AS (
    SELECT id_manager,
           id_manager,
           id_employee,
           UNNEST(ARRAY[0,1]) /* level > 0: go to the bottom, level <= 0: go to the top of the hierarchy */
    FROM   people
    WHERE  id_manager IN ('00555')
    UNION ALL
    SELECT id_root_manager,
           id_manager,
           p.id_employee,
           CASE WHEN level <= 0 THEN level-1 ELSE level+1 END
    FROM   people p
    JOIN    tree   t ON (level > 0 AND p.id_manager = t.id_employee) OR (level <= 0 AND t.id_direct_manager = p.id_employee)
)
SELECT id_root_manager, id_direct_manager, id_employee, level - (SELECT MIN(level) FROM tree WHERE id_root_manager = h.id_root_manager) AS level
FROM tree h
WHERE level > 0
ORDER BY id_root_manager, level

If you are not interested in the "root" manager, you may want to avoid duplicates by changing the final select to:

SELECT DISTINCT id_direct_manager, id_employee, level - (SELECT MIN(level) FROM tree WHERE id_root_manager = h.id_root_manager) AS level
FROM tree h
WHERE level > 0
ORDER BY level

---

Related Topics