Ternary relationship of entities in a relational schema would be counter-normative, and how to do this mapping in entity framework 6?
Taking as an example:
We can abstract the relationship in several ways, one of them would be putting n p/ N relationship between project and necessary skills, while another for employee and possessing skills, as well as one for employee and project in which it is allocated. Would this be the correct way to realize this relationship or is a ternary really the most appropriate?
I ask this because I'm messing with this kind of relationship in a legacy system that I am having to migrate, I take the opportunity to ask, How do I map a ternary relationship with Entity Framework 6?
I tried to perform the mapping like this, 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 across all three classes, the way it was needed 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 a 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.
1 answers
An example for this to work is to create an explicit intermediate entity and do the update operations on that intermediate 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 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 template 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 usage for example of adding:
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();
}
In this way has a practical model, easy to understand and that can grow with other types of relations, and it is in fact good to ratify that from the moment your model leaves the pattern of ORM in the many to many, in the case cited it is good to make the entity that controls these relations.