Recursive self join

Question

I have table as below. I need to get all the manager id's for the user id provided.

userid managerid
10     1
9      10
6      9
2      6
4      1

If i pass 2 to my method I need to get 1,10,9 and 6. I have written the below query which will return only first level parent. I.e it will return only 6 & 9.

public List<int?> mymethod (int userId){

return (from e in mycontext.EmployeeManagers
                     join e1 in m_context.EmployeeManagers
                        on e.UserId equals e1.ManagerId
                    where e1.UserId == userId
                     select e1.ManagerId).AsQueryable().ToList()
}

How can I modify the query to return all the manager hirerachy?

please help.

Duplicate of http://stackoverflow.com/questions/4072166/linq-to-sql-recursive-query — istepaniuk, Jan 17 '13 at 07:29

score 2 · Answer 1 · edited May 23 '17 at 10:29

2

You can not do this in a sinqle LINQ expression. You have to run this in a loop.

A better option is to do this in the database and then return the results to LINQ.

See:

edited May 23 '17 at 10:29

Community

1
1

answered Jan 17 '13 at 07:19

Jacco

3,251
1
19
29

1

There is no recursive join in LINQ simply because only few database engines support it. Consider [this elegant solution for hierarchical data handling in database](http://www.sqlteam.com/article/more-trees-hierarchies-in-sql). – ghord Jan 17 '13 at 07:25

score 1 · Accepted Answer · answered Jan 17 '13 at 07:49

I would simply run a short loop like this (sorry for invalid capitalization, coded from scratch):

public List<Int> GetAllManagers(int userID)
{
  var result = new List<int>();
  int index = 0;
  result.add(userID); // start with user (it will be removed later)
  while (index < result.count)
  {
    var moreData = from e in mycontext.EmployeeManagers
                    where e.UserId == result[index];
                     select e.ManagerId;
    foreach (int id in moreData)
      if (result.indexOf(id)==-1)
        result.add(id);
    index++;
  }
  result.delete(0);
  return result;
}

or recursevly

private void AddUniqueIds (List<int> elements, ref List<int> list)
{
  foreach (int id in elements)
    if (list.indexOf(id)==-1)
      list.add(id);
}
public List<int> GetAllManagers(int userID)
{
  var result = new List<int>();
  var moreData = from e in mycontext.EmployeeManagers
                 where e.UserId == result[index];
                 select e.ManagerId;

  foreach (int id in moreData)
    AddUniqueIds(result, GetAllManagers(id));

  return result;
}

score 0 · Answer 3 · answered Jan 17 '13 at 07:22

0

You need to use different pattern.

Lets see that you get your query.

var query = myContext.EmployeeManagers

Then, you could join it however you want to

for(int = 0; i < 5; i++)
{
    query = query.Join( ... ..., i, ... ); // Can't recall all
                                           // the parameters right now.
}

And then just execute it:

var result = query.ToList();

answered Jan 17 '13 at 07:22

AgentFire

8,944
8
43
90

Note it would require you to know max depth level, which is not always the case. – ghord Jan 17 '13 at 07:26
-1 Where does this magic 5 come from? How do you expect to return a list, from doing multiple joins? I'd expect this to return j1.managerid, j2.managerid, j3.managerid, ... in a single result. – Jacco Jan 17 '13 at 07:26
@Gregory not knowing the max depth will result an stackoverflow exception. – AgentFire Jan 17 '13 at 08:46
1

@Jacco it was the example of looped multiple joins. – AgentFire Jan 17 '13 at 08:46

Recursive self join

3 Answers3