How to iterate over all child by id in entity framework core?

Question

This is my entity:

public class User {
        public int Id { get; set; }

        public string Name { get; set; }

        public int? ManagerId { get; set; }

        public User Manager { get; set; }
}

I have relation on same table with ManagerId.

builder.HasOne(t => t.Manager).WithMany();

I'm using entity framework core and i want to iterate over all child by id.

Example:

Id | Name   | ManagerId 
---------------------
1  | Boss   | null
---------------------
2  | Child1 |  1
---------------------
3  | Child2 |  2

Input: 1

Output: 2, 3

Input: 2

Output: 3

P.S. There can be more than one "Bosses" so i want to filter by id

That's not iterating by ID, that's retrieving hierarchical data. The easiest option would be to use a `hierarchyid` field to store the hierarchy path of each entry, not a ParentID. With a parent ID the only way you can retrieve the hierarchy without iterating is to use a recursive CTE — Panagiotis Kanavos, Sep 13 '19 at 15:12

Pedro Brito · Answer 1 · 2019-09-13T09:49:12.580

0

Try this code pls

 public async Task<List<User>> GetChilds(int userId)
    {
     List<User> newChilds = new List<User>();
        var childs = await _dbContext.Users.Where(x => x.ManagerId == userId).ToList();

        if (childs != null)
        {
            foreach (var child in childs)
            {
                var childChildreen = await GetChilds(child.Id);
                if(childChildreen != null)
                {
                    newChilds.AddRange(childChildreen);
                }

            }
            childs.AddRange(newChilds);
        }

        return childs;
    }

edited Sep 13 '19 at 09:49

answered Sep 13 '19 at 09:30

Pedro Brito

263
1
9

Are you sure that it is optimal solution ? – Mate Gvenetadze Sep 13 '19 at 09:59
You are sending request in database every function call – Mate Gvenetadze Sep 13 '19 at 09:59
Foreach child he runs the same function, using recursivity! Is normal to use recursive functions they have to be well tested and optimized, this one is simple so i think is good solution. – Pedro Brito Sep 13 '19 at 10:17
If my answer helped you, please mark my answer as useful. Thank you, best regards – Pedro Brito Sep 13 '19 at 10:29
@MateGvenetadze as long as you don't use a `hierarchyid` any solution is suboptimal. Solutions that use iteration are the worst possible case – Panagiotis Kanavos Sep 13 '19 at 15:14
@Panagiotis Kanavos a recursive solution is used in special scenarios only! ofc if he want to have better performance he will have to redo is entities and think in a way that would be easier to retrieve the data he wants. – Pedro Brito Sep 13 '19 at 15:45
@PedroBrito this isn't a special case. [Recursive CTEs](http://www.sqlservertutorial.net/sql-server-basics/sql-server-recursive-cte/) are used to retrieve hierarchical data when a parent/child model is used. That's *not* the same as calling `GetChild` recursively. A recursive CTE is still a *single* query that will return all the data at once. The use of [hierarchyid](https://learn.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference?view=sql-server-2017) instead of parent/child is a far faster option – Panagiotis Kanavos Sep 13 '19 at 15:53

score 0 · Answer 2 · answered Sep 13 '19 at 15:11

if you really want to iterate over all emlements you could do something like this:

add a collection of children to your entity

public virtual ICollection<User> Users { get; set; }

let the model builder know

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>().HasOne(t => t.Manager).WithMany(u => u.Users);
}

finally load the tree into memory and iterate:

using (var ctx = new DataContext())
{
    // load the whole hierarchy to memory
    var q = ctx.Users.Include(u => u.Users).ToList();
    var user = q.Single(u => u.Id == 1);
    Console.WriteLine(string.Join(",", GetIds(user)));
}

private static IEnumerable<int> GetIds(User user)
{
    foreach (var child in user.Users)
        foreach (int id in GetIds(child))
            yield return id;

    yield return user.Id;
}

the key is EF cores relationship fixup as mentioned here https://stackoverflow.com/a/41837737/1859022

Be advised: perfromance might become an issue pretty quickly for a larger table since your data structure has no way of filtering the tree before loading it into memory. If this is the case you might need to move to Most efficient method of self referencing tree using Entity Framework

How to iterate over all child by id in entity framework core?

2 Answers2