LINQ Hierarchical query to return all parents

Question

I am stuck on doing a hierarchical query in LINQ - I am on my first ASP.NET project ever, so bear over with my lack of knowledge and experience. I am basically doing the project on EF6, C#, and MVC 5. So, I can't figure out how to get the following hierarchical data out.

I have an employee table, an employeeMap table, and a Goal table. EmployeeMap maps goals to employees. Goals are hierarchical so, a goal has a parent goal in an unary relationship, here my Goal class a little simplified:

public class Goal
{
    public int ID { get; set; }
    public string Name { get; set; }
    public int? ParentID { get; set; }
    public virtual Goal Parent { get; set; }
}

I need a list of goals mapped to an employee, and all the parent goals thereof. I can get the goals mapped to the employee, and the parent goal thereof, but can't get the parents parent and so on up the hierarchy to the top, where parentID would be null. Here's my query getting the goals and the direct parent.

viewModel.EmpGoals = (
         from g in db.Goals
         join m in db.EmployeeMaps on g.ID equals m.GoalID
         join e in db.Employees on m.EmployeeID equals e.ID
         where m.EmployeeID == id.Value
         select new EmployeeGoal
         {
             EmployeeID = e.ID,
             LastName = e.LastName,
             FirstName = e.FirstName,
             GoalID = g.ID,
             Name = g.Name,
             ParentID = g.ParentID,
             Parent = g.Parent,
             WeightPct = m.WeightPct,
             Locked = m.State.Equals(1),
             Activities = g.Activities
         }).ToList();
        }

So I guess I need a hierarchical query, recursively running up to all parents and return each parent ( or at least just the very top of the parent tree, or root maybe rather), but how can I do that using LINQ, or should I consider some raw SQL to give me this back?

Thanks :)

Answer 1

Does it have to be a single query? Maybe return your list after calling a method from your data layer service:

ExampleDataLayerService dlSvc = new ExampleDataLayerService();
viewModel.EmpGoals = dlSvc.GetEmpGoalList(id.Value);

Service layer method:

public List<EmployeeGoal> GetEmpGoalList(int empID)
{
    //Get employee info
    var empInfo = db.Employees.Where(x => x.ID == empID).Select(x => new { ID = x.ID, LastName = x.LastName, Firstname = x.FirstName }).FirstOrDefault();

    //Get initial bottom tier list of employee goals
    List<int> goalIdList = db.EmployeeMaps.Where(x => x.EmployeeID == empID).Select(x => x.GoalID).ToList();

    List<EmployeeGoal> empGoalList = new List<EmployeeGoal>();
    List<int> usedGoalList = new List<int>();

    foreach (var goal in goalIdList)
    {
        var tempID = goal;

        while (tempID != 0 && tempID != null)
        {
            var gmData = (from g in db.Goals
                      join m in db.EmployeeMaps.Where(m => m.EmployeeID == empInfo.ID) on g.ID equals m.GoalID into m_g
                      from mg in m_g.DefaultIfEmpty()
                      where g.Goals == tempID
                      select new EmployeeGoal
                      {
                          EmployeeID = empInfo.ID,
                          LastName = empInfo.LastName,
                          FirstName = empInfo.FirstName,
                          GoalID = g.ID,
                          Name = g.Name,
                          ParentID = g.ParentID,
                          Parent = g.Parent,
                          WeightPct = (mg == null) ? 0 : mg.WeightPct,
                          Locked = (mg == null) ? 0 : mg.State.Equals(1),
                          Activities = g.Activities
                      }).FirstOrDefault();

            if (!usedGoalList.Contains(gmData.GoalID))
            {
                empGoalList.Add(gmData);
                UsedGoalList.Add(gmData.GoalID);
            }

            tempID = gmData.ParentId;
        }
    }

    return empGoalList;
}

Code is off the top of my head an untested so may not run as is. If desired you could also add some meta data to determine what "tier" each goal is if you need to sort the goal list from root downwards or something like that. Also this solution may not work as it will be much less efficient than a single LINQ query as this one has multiple hits the DB. To save DB hits you can also just build the tables in memory first and query from those if that's preferred.

Hope it helps or at least gives some ideas for a workable solution.