How to delete Many - Many Relationship (Only Joining Table)

Question

I'm using Entity Framework Core in an ASP.NET Core application. I want to be able to delete the joining linking row in a many to many relationship table (where you only have table1Id & table2Id columns linking the many to many relationship) but the only options I have are

1) SetNull (which I'm assuming tries to set table2Id column ON TABLE2 to null because I can't not perform a update-database and it tells me the reason is because it can't set the foreign key id column to null)

2) Restrict (this is a band-aid fix because it allows me to push but now my users can't delete until they've undone every relationship before deleting)

3) Cascade (this tries to delete EVERYTHING and is definitely not what I want)

there doesn't seem to be just a "DeleteRelationship" option

I have tried to set the id properties in the linking class to nullable (see code)

public class PregenProfessionArchetypeModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public ICollection<PregenProfessionArchetype_ParentSkill_LinkModel> TrainedParentSkills { get; set; } = new List<PregenProfessionArchetype_ParentSkill_LinkModel>();
    public ICollection<PregenProfessionArchetype_ChildSkill_LinkModel> ChildSkills { get; set; } = new List<PregenProfessionArchetype_ChildSkill_LinkModel>();
    public ICollection<PregenProfessionArchetype_Technique_LinkModel> Techniques { get; set; } = new List<PregenProfessionArchetype_Technique_LinkModel>();
    public ICollection<PregenProfessionArchetype_Talent_LinkModel> Talents { get; set; } = new List<PregenProfessionArchetype_Talent_LinkModel>();
}


public class PregenProfessionArchetype_ChildSkill_LinkModel
{
    public int PregenProfessionArchetypeId { get; set; }
    public PregenProfessionArchetypeModel PregenProfessionArchetype { get; set; }
    public int ChildSkillId { get; set; }
    public ChildSkillModel ChildSkill { get; set; }
}

public class PregenProfessionArchetype_ParentSkill_LinkModel
{
    public int? PregenProfessionArchetypeId { get; set; }
    public PregenProfessionArchetypeModel PregenProfessionArchetype { get; set; }
    public int? ParentSkillId { get; set; }
    public ParentSkillModel ParentSkill { get; set; }
}

public class ChildSkillModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public int ParentSkillId { get; set; }
    [Display(Name = "Parent Skill")]
    public ParentSkillModel ParentSkill { get; set; }
    public int Cost { get; set; } = 1;


    public ICollection<PregenProfessionArchetype_ChildSkill_LinkModel> PregenProfessionArchetypeLink { get; set; } = new List<PregenProfessionArchetype_ChildSkill_LinkModel>();
}


        // Application Context File Overwritting (Child is same and left out talent)
        builder.Entity<PregenProfessionArchetype_ParentSkill_LinkModel>()
            .HasKey(ttp => new { ttp.ParentSkillId, ttp.PregenProfessionArchetypeId });

        builder.Entity<PregenProfessionArchetype_ParentSkill_LinkModel>()
            .HasOne<PregenProfessionArchetypeModel>(tm => tm.PregenProfessionArchetype)
            .WithMany(tpp => tpp.TrainedParentSkills)
            .HasForeignKey(tm => tm.PregenProfessionArchetypeId)
            .OnDelete(DeleteBehavior.Restrict);

        builder.Entity<PregenProfessionArchetype_ParentSkill_LinkModel>()
            .HasOne<ParentSkillModel>(tm => tm.ParentSkill)
            .WithMany(tmm => tmm.PregenProfessionArchetypeLink)
            .HasForeignKey(tm => tm.PregenProfessionArchetypeId)
            .OnDelete(DeleteBehavior.Restrict);

The only time I'm able to push is when it is set to restricted. The code shows an example of the classes and their relationship

Error Message: SetNull

Failed executing DbCommand (81ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE [PregenProfessionArchetype_ParentSkill_LinkModel] ADD CONSTRAINT [FK_PregenProfessionArchetype_ParentSkill_LinkModel_ParentSkillModel_PregenProfessionArchetypeId] FOREIGN KEY ([PregenProfessionArchetypeId]) REFERENCES [ParentSkillModel] ([Id]) ON DELETE SET NULL;

Answer 1

If you want to only delete the links of the join table and the row of parent table when deleting a parent skill , you could use Cascade delete which is the default behavior of required relationships

Delete behaviors are defined in the DeleteBehavior enumerator type and can be passed to the OnDelete fluent API to control whether the deletion of a principal/parent entity or the severing of the relationship to dependent/child entities should have a side effect on the dependent/child entities.

In your scenario , PregenProfessionArchetype_ParentSkill_LinkModel is the dependent/child entity of PregenProfessionArchetypeModel and ParentSkillModel. So when deleting Parent its links from PregenProfessionArchetype_ParentSkill_LinkModel Table will automatically be deleted because of Cascade, but the Pregens will not be deleted. And if you want also to delete Pregens that were connected only to that Parent you will have to do it manually.

Reference ：Entity Framework Core Code-First: Cascade delete on a many-to-many relationship