How to query for 2 depths?

Question

Example: A is a manager, B is A's subordinate, and C is B's subordinate.

What I need to do is to display a list of subordinates that shows on A's screen which are B and C's info. This is what I have now that will display whoever has A as their manager which are B and etc.

 displayList = db.LeaveApplicationDbSet
      .Where(l => db.EmployeeDbSet
                .FirstOrDefault(e => e.user_id.Equals(l.UserId))
                .manager_id.Equals(userDetails.EmployeeId)
      );

Any idea on how I can get the grand child portion of the hierarchy?? e.g.: A -> B -> C so from A's view he/she should be able to see B and C's info

Update:

public class MysqlDbContext: DbContext
{   
    public MysqlDbContext() : base(dbName)
    { }

    public DbSet<Employee> EmployeeDbSet { get; set; }
}

public class Employee : IValidatableObject
{
    [Column("id")]
    public int id { get; set; }

    [Required]
    [Column("user_id")]
    public string user_id { get; set; }

    [Column("full_name")]
    public string full_name { get; set; }

    [Required]
    public string manager_id { get; set; }
}

I do not have experience with asp, entity and linq so ignore this command if it doesn't fit in your program. I would think of a tree datastructure to complete this, this can also be done via a database query ofcourse. — wes, Dec 06 '19 at 08:49
can you show your entity? so we can have a clearer picture on the properties. — Gabriel Llorico, Dec 06 '19 at 08:51
What you are looking for is Linq Recursion, but it is much simpler to solve this with a normal approach using loops instead. — Eric Herlitz, Dec 06 '19 at 10:21
Hi thanks for the suggestion! @IraklisGkougkousis no its not even primary key — DatB, Dec 10 '19 at 06:03
the manager_id contains the id for your manager, the manager_id is not for the user itself. — DatB, Dec 11 '19 at 05:25

LordSilvermort · Answer 1 · 2019-12-06T10:41:09.130

0

An easy solutions would be to separate it in different queries. First you query the employees that has person A as manager. Then you query for employees that has any of B as manager. Something like this:

//All employees of person A 
var B = db.LeaveApplicationDbSet.Where(e => e.manager_id == "A");
var C = new List<Employee>();

//All employees from B
foreach(var i in B){
var D = db.LeaveApplicationDbSet.Where(e => e.manager_id == i.id).ToList();
C.AddRange(D);

}

edited Dec 06 '19 at 10:41

answered Dec 06 '19 at 10:13

LordSilvermort

237
2
8

This is not correct, C will be overriden every time in the foreach loop. – igg Dec 06 '19 at 10:24
Yes, sorry. Missed it – LordSilvermort Dec 06 '19 at 10:41

How to query for 2 depths?

1 Answers1