Recursive Relationships

Hi - here's the scenario I'd like to model:

Our Employees can manage any number of Sub-Employees, and each has a Rank of seniority within that relationship. So, we have an Entity 'Employee' and an Entity 'EmployeeRel' with properties ParentId, ChildId and Rank.

(We need the identity key of EmployeeRel to be ParentId + ChildId + Rank, rather than relying on an arbitrarily created id.)

So, following the instructions about composite keys, I can create the EmployeeRel table. Where I'm coming unstuck is trying to work out what the associations I should create between EmployeeRel and the Employee table, and how to access the 'rank' property of the relationship.

Any guidance please? Thanks.

Because your relationships are going to back on parts of the composite key you will need to handle the associations manually by loading the associated records which match the Employee's Id, e.g. something like this assuming you want to abstract away the use of EmployeeRel:

public partial class Employee
{
  public Employee Manager
  {
    get
    {
      var parents = UnitOfWork.Find<EmployeeRel>(Entity.Attribute("ChildId") == Id);

      if (parents.Count == 0) return null;

      return UnitOfWork.FindById<Employee>(parents[0].Id.ParentId);
    }
  }

  public List<Employee> Reports
  {
    get
    {
      var employeeRels = new Query(typeof(EmployeeRel), Entity.Attribute("ParentId") == Id);
      employeeRels.Projection.Add("ChildId");

      return UnitOfWork.Find<Employee>(Entity.Attribute("Id").In(employeeRels)).ToList();
    }
  }
}

There are a number of potholes with this type of approach though so be wary, here are some top of head ones:

• You are going to be generating new queries anytime you start traversing the hierarchy so be wary of that
• Handling Inserts, Updates and Removals into the Reports collection would need to be handled external to this as we are returning a new list each time
• Similarly updating the manager would need to be handled manually

To access the Rank property you can find this as a property underneath .Id on your EmployeeRel entity, e.g. employeeRel.Id.Rank

Jeremy

Okay, thanks. Looks like it's probably going to be simplest to use the built-in identity, rather than try and work with a composite key.

Thanks.