CODEWITHSUNDEEP
c#linq-to-sql
San
San

Reputation: 1837

Recursive self join

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.

Upvotes: 2

Views: 3348

Answers (3)

alzaimar
alzaimar

Reputation: 4622

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;
}

Upvotes: 1

AgentFire
AgentFire

Reputation: 9780

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();

Upvotes: 0

Jacco
Jacco

Reputation: 3271

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:

Upvotes: 2

Related Questions