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

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?

May this will help you: http://stackoverflow.com/questions/14489676/entity-framework-how-to-solve-foreign-key-constraint-may-cause-cycles-or-multi — Divyang Desai, Aug 29 '16 at 04:48

score 3 · Answer 1 · answered Aug 29 '16 at 07:03

3

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

answered Aug 29 '16 at 07:03

LazZiya

5,286
2
24
37

Hmm, didn't think this would work, but it does! The only issue I have is that the generated many-to-many table has the name `EmployeeEmployee` with fields `Employee_Id` and `Employee_Id1`. Is there a way to control the names of these elements with data annotations (preferably) or with Fluent mappings? – Jiveman Aug 30 '16 at 03:39
you don't have to change those fields, because you will not use it, the framework will do the dirty job for you :) btw, before you delete an entity from the employees table you have to clear its relations with other entites. – LazZiya Aug 30 '16 at 09:28
Yes, I know I don't have to, but I need the database itself to be meaningful, because it will be used for reporting and other potential direct querying. I can't use it just as a "black box" data persistence. I've found how to do this with fluent mappings, just wasn't sure how to do it with data annotations, if it's even possible. Will post an answer with more details when I get a chance. – Jiveman Sep 01 '16 at 15:11

score 0 · Answer 2 · edited May 23 '17 at 12:32

Based on this link Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths - why?

It seems if you delete an EmployeeAssistant with your code, it causes two cascading delete paths.

I would suggest a structure such as this:

After edit

public class Employee
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public List<EmployeeAssistant> Assistants { get; set; } //if an employee has no assistants this List can easily just be empty
    OR
    public ICollection<EmployeeAssistant> Assistants { get; set; } // depending on your architecture, choose the one that would suit you better
}
public class EmployeeAssistant
{
   [ForeignKey("Employee")]
   public int EmployeeId { get; set; } //this is the employee who 'has' this assistant
   public virtual Employee Employee { get; set; }

   public int Id { get; set; }  //this is the assistant's own information - identical to employee's basic info
   public string FirstName { get; set; }
   public string LastName { get; set; }
}

After a while of brain-boggling, I even came up with a possibility where you just need 1 class, Employee, but it includes a bool IsAssistant denoting if this employee is an assistant, and AssistantEmployeeId which is an employee ID of the employee who 'has' this assistant. For example:

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

I know this second method is very messy and probably doesn't suit your needs, but personally I always create database tables first then do 'Generate Model from Database' so I'm inexperienced at code-first approach.

Hmm, but how do you then know which assistant goes with which employee? If the EmployeeAssistant entity only has `Employee` reference, then this will create a table in the database `EmployeeAssistant` with fields `Id` and `EmployeeId`. So, it's missing data to tie an Employee to an Assistant. Right? — Jiveman, Aug 29 '16 at 05:03
I reread your answer after your edits. I appreciate the brain-boggling! however, the first method means I have to replicate all the Employee properties in the EmployeeAssistant class, and that doesn't make sense to do, as an EmployeeAssistant is an Employee himself/herself. The second approach doesn't work as it only takes care of one assistant case, but not multiple assistants. — Jiveman, Aug 30 '16 at 03:47
Then adding a list of EmployeeAssistantIDs to the second approach would do the trick? e.g. `public List AssistantIDs { get; set; }` — Keyur PATEL, Aug 30 '16 at 04:44

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

2 Answers2