Modeling Employee-Assistant(s) relationship with EF Code First

asp.net-mvc c# ef-code-first entity-framework-6 self-referencing-table

Question

Here are the business requirements, in short:

  • All employees need to be stored in a database
  • Some employees have assistants, some do not
  • Some employees have more than one assistant
  • Assistants are employees, as well

There is obviously a bit of a self-referencing situation. But the difference from a typical "Employee-Manager" situation is that here one Employee can have 0 or multiple assistants. So, the combination of Employee and employee's Assistants needs to be stored in a separate table in a one-to-many relationship between Employee and EmployeeAssistant. But I'm getting confused how to model this in Entity Framework 6 Code First.

I started with this:

public class Employee
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

public class EmployeeAssistant
{
   [ForeignKey("Employee")]
   public int EmployeeId { get; set; }
   public virtual Employee Employee { get; set; }

   [ForeignKey("Assistant")]
   public int AssistantId { get; set; }
   public virtual Employee Assistant { get; set; }
}

But I get an error during Update-Database command:

Introducing FOREIGN KEY constraint 'FK_dbo.EmployeeAssistant_dbo.Employee_EmployeeId' on table 'EmployeeAssistant' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

What am I missing? Should I approach this differently?

1
4
8/29/2016 4:15:13 AM

Popular Answer

since each employee may have one or more assistants (and each assistant will have one or more employees) and all are employees, the simplest solution is one class with two collections for assistants and employees, and the relations will be managed by the framework:

public class Employee
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public ICollection<Employee> Assistants { get; set; }
    public ICollection<Employee> Employees { get; set; }
}

when you use package manager console to add migration it will automatically create two tables, one for employees, and another for many to many relations.

then all you have to do is to find related assistants and/or employees by using Include extension method.

db.Employees.Where(x=>x.Id==id).Include(x=>x.Assistants).FirstOrDefault()

and/or

db.Employees.Where(x=>x.Id==id).Include(x=>x.Employees).FirstOrDefault()

2
8/29/2016 7:03:51 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow