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

Question

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?

Jiveman

View more on Stack Overflow

Laz Ziya · Accepted Answer · 8/29/2016 7:03:51 AM

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

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

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related