Ef core many 2 many mapping-CodePudding

I have a 3 way many to many relation like this:

table 1 skill
table 2 instructions
table 3 person

Now I need to have a way to set for a combination of skill and person a list of instructions. I was thinking of having 1 table that has this mapping SkillId, InstructionId and PersonId. I have tried to set it up with HasOne.Withmany.HasForeignKey but it' not working. I am getting an error

The property or navigation 'Instruction' cannot be added to the entity type 'PersonSkillInstruction' because a property or navigation with the same name already exists on entity type 'PersonSkillInstruction'

If I set up only the primary keys and let .netcore 5.0 do the trick for me I am getting another error

The property 'PersonSkillInstruction.Instruction' is of type 'InstructionEntity' which is not supported by the current database provider. Either change the property CLR type, or ignore the property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'

I know I could do something like create a many to many for person and skill and set an id then use that in a combination with instruction, but it feels very fishy.

How can I make this 3 way many to many work?

I am using .netcore 5.0, ef core, code first and sqlserver

CodePudding user response：

I think the right thing to do is what you said:

I know I could do something like create a many to many for person and skill and set an id then use that in a combination with instruction

Nevertheless, here you have an example if you want to create PersonSkillInstruction table.

public class PersonSkillInstruction
{ 
  [ForeignKey("person")]    
  public int PersonId { get; set; }        
  [ForeignKey("skill")]    
  public int SkillId { get; set; }    
  [ForeignKey("instructions")]    
  public int InstructionId { get; set; }    
  public virtual Person Person { get; set; }
  public virtual Skill Skill { get; set; }
  public virtual Instruction Instruction { get; set; }
}

Then you need to set the PK in DBContext as follows:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// ...
  modelBuilder.Entity<PersonSkillInstruction>()
    .HasKey(t => new { t.PersonId, t.SkillId, t.InstructionId });
// ...

After adding a new migration, you have to reference two actions like this to avoid cycles:

// ...
  onDelete: ReferentialAction.Restrict
// ...

If this table will have heavy querying I suggest to use a surrogate key with a unique index like this:

modelBuilder.Entity<PersonSkillInstruction>()
  .HasIndex(t => new { t.PersonId, t.SkillId, t.InstructionId })
  .IsUnique();

Link about surrogate key