Ternary relationship of entities in a relational scheme would be counter-normative, and how to do this mapping in Entity framework 6?

Question

Taking as an example:

We can abstract the relationship in several ways, one of them would be putting relation of N p/ N between project and skills needed, while another for employee and skills possessing, as well as one for employee and project in which it is allocated. That would be the right way to accomplish that relationship or a ternary really is the most appropriate?

I ask this because I’m dealing with this kind of relationship in a legacy system that I’m having to migrate, I ask, how do I map a ternary relationship with Entity Framework 6?

I tried to perform the mapping in this way, in example code:

             this.HasMany(e => e.Skill)
                 .WithMany(s => s.Employees)
                 .Map(eps =>
                     {
                         eps.MapLeftKey("ID_EMP");
                         eps.MapRightKey("ID_SKILL");
                         eps.ToTable("EMP_SKILL_PROJ");
                     });

             this.HasMany(e => e.Projects)
                 .WithMany(p => p.Emplyees)
                 .Map(eps =>
                     {
                         eps.MapLeftKey("ID_EMP");
                         eps.MapRightKey("ID_PROJECT");
                         eps.ToTable("EMP_SKILL_PROJ");
                     });

I repeated this mapping in all three classes, in the way that was necessary according to the context of each. But obviously I got the following exception:

One or more validation errors Were Detected During model Generation: Projectskill: Name: The Entityset 'Projectskill' with table 'EMP_SKILL_PROJ' was already defined. Each Entityset must refer to Unique schema and table.

Projectemployee: Name: The Entityset 'Projectemployee' with table 'EMP_SKILL_PROJ' was already defined. Each Entityset must refer to a Unique schema and table.

Answer 1

An example for this to work is to create an explicit intermediary entity and do the upgrade operations in this intermediary entity, example:

Models:

public partial class Employee
{
  public Employee()
  {
     EmployeeSkillProject = new HashSet<EmployeeSkillProject>();
  }
  public int Id { get; set; }
  public string Name { get; set; }
  public virtual ICollection<EmployeeSkillProject> EmployeeSkillProject { get; set; }
}

---

public partial class Project
{
  public Project()
  {
     EmployeeSkillProject = new HashSet<EmployeeSkillProject>();
  }
  public int Id { get; set; }
  public string Description { get; set; }
  public virtual ICollection<EmployeeSkillProject> EmployeeSkillProject { get; set; }
}

---

public partial class Skill
{
  public Skill()
  {
     EmployeeSkillProject = new HashSet<EmployeeSkillProject>();
  }
  public int Id { get; set; }
  public string Title { get; set; }
  public virtual ICollection<EmployeeSkillProject> EmployeeSkillProject { get; set; }
}

all these models have collection relationship with the model EmployeeSkillProject with the following model example:

public partial class EmployeeSkillProject
{
  public int EmployeeId { get; set; }
  public int SkillId { get; set; }
  public int ProjectId { get; set; }
  public string Description { get; set; }
  public virtual Employee Employee { get; set; }
  public virtual Project Project { get; set; }
  public virtual Skill Skill { get; set; }
}

and in this model there is the aggregation of the other three models. To configure a Context with the configuration of this model above:

public partial class Ex001Context : DbContext
{
  private const string ConnectionString = "String de conexão";
  public Ex001Context()
     :base(ConnectionString)
  {
     Database.SetInitializer<Ex001Context>(null);
     Configuration.LazyLoadingEnabled = false;         
     Configuration.ProxyCreationEnabled = false;         
  }
  public virtual DbSet<Employee> Employee { get; set; }
  public virtual DbSet<EmployeeSkillProject> EmployeeSkillProject { get; set; }
  public virtual DbSet<Project> Project { get; set; }
  public virtual DbSet<Skill> Skill { get; set; }
  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
     modelBuilder.Entity<Employee>().ToTable("Employee");
     modelBuilder.Entity<Project>().ToTable("Project");
     modelBuilder.Entity<Skill>().ToTable("Skill");
     modelBuilder.Entity<EmployeeSkillProject>().ToTable("EmployeeSkillProject");

     modelBuilder.Entity<Employee>()
        .Property(x => x.Name)
        .IsRequired()
        .HasMaxLength(50)
        .IsUnicode(false);

     modelBuilder.Entity<EmployeeSkillProject>()
        .HasKey(e => new { e.EmployeeId, e.SkillId, e.ProjectId });

     modelBuilder.Entity<EmployeeSkillProject>()
        .Property(x => x.Description)
        .IsRequired()
        .HasMaxLength(50)
        .IsUnicode(false);

     modelBuilder.Entity<EmployeeSkillProject>()
        .HasRequired(x => x.Employee)
        .WithMany(p => p.EmployeeSkillProject)
        .HasForeignKey(x => x.EmployeeId);

     modelBuilder.Entity<EmployeeSkillProject>()
        .HasRequired(x => x.Project)
        .WithMany(p => p.EmployeeSkillProject)
        .HasForeignKey(p => p.ProjectId);

     modelBuilder.Entity<EmployeeSkillProject>()
        .HasRequired(x => x.Skill)
        .WithMany(p => p.EmployeeSkillProject)
        .HasForeignKey(p => p.SkillId);

     modelBuilder.Entity<Project>()
        .Property(x => x.Description)
        .IsRequired()
        .HasMaxLength(50)
        .IsUnicode(false);

     modelBuilder.Entity<Skill>()
        .Property(x => x.Title)
        .IsRequired()
        .HasMaxLength(50)
        .IsUnicode(false);
  }
}

and in basic use for example add:

using (Ex001Context db = new Ex001Context())
{
   Employee employee = new Employee();
   employee.Name = "Employee 2";

   Skill skill = new Skill();
   skill.Title = "Skill 2";

   Project project = new Project();
   project.Description = "Project 2";

   EmployeeSkillProject employeeSkillProject = new EmployeeSkillProject();

   employeeSkillProject.Employee = employee;
   employeeSkillProject.Skill = skill;
   employeeSkillProject.Project = project;
   employeeSkillProject.Description = "Employee, Skill, Project 2";

   db.Employee.Add(employee);
   db.Skill.Add(skill);
   db.Project.Add(project);
   db.EmployeeSkillProject.Add(employeeSkillProject);

   db.SaveChanges();
} 

Thus has a practical model, easy to understand and that can grow with other types of relations, and it is indeed good to ratify that from the moment your model leaves the standard of ORM in many to many, in the cited case it is good to make the entity that controls those relations.