Hierarchical layering in Linq

Question

If I have a set of employee data similar to:

        var users = new[] 
        {
            new {SupervisorId = "CEO", UserId = "CEO", UserName = "Joe"},
            new {SupervisorId = "CEO", UserId = "CIO", UserName = "Mary"},
            new {SupervisorId = "CIO", UserId = "XDIR", UserName = "Ed"},
            new {SupervisorId = "CIO", UserId = "YDIR", UserName = "Lisa"},
            new {SupervisorId = "XDIR", UserId = "AMNGR", UserName = "Steve"},
            new {SupervisorId = "AMNGR", UserId = "ASUP", UserName = "Lesley"}
        };

Would it be possible to use Linq to add hierarchical layers, in the sense that:

CEO = 1 (top)

CIO = 2 (2nd level)

XDIR and YDIR = 3 (3rd level)

AMNGR = 4 (etc)

ASUP = 5 (etc)

I've been able to group the employees according to SupervisorId, but not sure how to make the "level" happen.

        var userGroups = from user in users
                         group user by user.SupervisorId into userGroup
                         select new
        {
            SupervisorId = userGroup.Key,
            Level = ??????
            Users = userGroup.ToList()
        };

        foreach (var group in userGroups)
        {
            Console.WriteLine("{0} - {1} - {2}", group.SupervisorId, group.Level, group.Users.Count);
        }

Many thanks.

Answer 1

I would add a ranking to your linq "user object"

public class User{

  public string SupervisorId  {get;set;}
  public string UserId {get;set;}
  public string UserName   {get;set;}
  public int Level {get {  return GetRank(SupervisorId  ) ; } } 

  private int GetRank(string userId){
     if(string.IsNullOrEmpty(userId)){
         //Bad case, probably want to use a very large number
         return -1;
     }
     int level = 0;
     switch(userId){
        case "CEO":
           level  = 0;
           break;

         //insert others here

     }

  }
}

Then your Linq you would add a join.

     var userGroups = from user in users
                     join super in users on user.SupervisorId  equals super.UserId
                     group user by  user.SupervisorId into userGroup 
                     select new
    {
        SupervisorId = userGroup.Key,
        Level =  super.Level, 
        Users = userGroup.ToList()
    };

Answer 2

Update

Heres one way to create a lookup table for each level. Its fairly and I dont know how it will scale. Obviously, you'll need to adapt it to pull the rows from your database.

Define a class to hold our lookup table

public class user{
    public string SupervisorId;
    public string UserId;   
    public int Level;
}

Then we get a unique list of UserId/SupervisorId combinations and loop through the list calculating the level for each combination by 'walking' up the tree.

    var uniqueusers = (new user[] 
        {
            new user {SupervisorId = "CEO", UserId = "CEO"},
            new user {SupervisorId = "CEO", UserId = "CIO"},
            new user {SupervisorId = "CIO", UserId = "XDIR"},
            new user {SupervisorId = "CIO", UserId = "YDIR"},
            new user {SupervisorId = "XDIR", UserId = "AMNGR"},
            new user {SupervisorId = "AMNGR", UserId = "ASUP"}
        }).Distinct();


        foreach (var item in uniqueusers)
        {           
            int level = 0;
            user CurrentUser = item;
            while (CurrentUser.UserId != CurrentUser.SupervisorId){
                CurrentUser = uniqueusers.Where(c => c.UserId == CurrentUser.SupervisorId).FirstOrDefault();
                level++;
            } 
            item.Level = level;             
        }

Now you can use the uniqueusers as a lookup table to determine the level for your query. eg

private int GetLevel(string userId){         
     return uniqueusers.Where(c => c.UserId == userId).FirstOrDefault().Level;    
  }

You could probably even combine this into a single step with a little effort.

Answer 3

ILookup<string, User> subordLookup = users
  .ToLookup(u => u.SupervisorId);

foreach(User user in users)
{
  user.Subordinates = subordLookup[user.UserId].ToList();
}

User userHierarchy = user.Single(u => u.UserId == "CEO");

Disclaimers:

• Does not handle multiple CEOs.
• Preserves circular relationships.
• Leaves orphans behind.

Answer 4

Is this what you are looking for?

         var levels = new[]
        {
            new { Level = 1, LevelName = "CEO" },
            new { Level = 2, LevelName = "CIO" },
            new { Level = 3, LevelName = "XDIR" },
            new { Level = 3, LevelName = "YDIR" },
            new { Level = 4, LevelName = "AMNGR" },
            new { Level = 5, LevelName = "ASUP" }
        };                        

        var userGroups = from user in users
                         join level in levels on
                         user.UserId equals level.LevelName                             
                         group new{ User = user, Level = level.Level } by new { SuperId = user.SupervisorId, Level = level.Level } into userGroup
                         select new
                            {
                                SupervisorId = userGroup.Key.SuperId,
                                Level = userGroup.Key.Level,
                                Users = userGroup.ToList()
                            };