How do I model deletion in an EF Core many-to-many relationship?

Question

I've followed the docs for setting up my many-to-many relationship, using a join table that is exposed as an entity.

But the docs don't mention what I should do about deletion.

So for example, a Student has many teachers, and a Teacher has many students. The join entity/table is StudentTeacher.

The join table/entity:

public class StudentTeacher {
  public int StudentId { get; set; }
  public Student Student { get; set; }
  public int TeacherId { get; set; }
  public Teacher Teacher { get; set; }
}

The config for the join table/entity:

modelBuilder.Entity<StudentTeacher>()
  .HasOne(b => b.Teacher)
  .WithMany(b => b.StudentTeachers)
  .HasForeignKey(b => b.TeacherId)
  .IsRequired()
  .OnDelete(/* ... what goes here? ...*/);

modelBuilder.Entity<StudentTeacher>()
  .HasOne(b => b.Student)
  .WithMany(b => b.StudentTeachers)
  .HasForeignKey(b => b.StudentId)
  .IsRequired()
  .OnDelete(/* ... what goes here? ...*/);

What do I use in OnDelete()? And why?

score 3 · Answer 1 · answered Dec 02 '16 at 14:03

3

.OnDelete(/* ... what goes here? ...*/);

You should specify here what DB must do with child records (in StudentTeacher) when parent records (in Student or Teacher) is deleted: delete too (Cascade) or prohibit and throw error (Restrict) if corresponding child record exists. With Restrict you must manually delete child records before deleting parent one.

But only you can decide what action must be applied for each relationship - this is your application, we don't know all requirements to it.

Important: with Cascade, deleting, say, Teacher will affect (delete) only records in StudentTeacher (with corresponding TeacherId), but Students will be keep intact.

Important 2: In MS SQL Server (you didn't wrote what DB engine you are using), you can set only one to Cascade (other should be Restrict), or you will receive error when applying migration (Introducing FOREIGN KEY constraint _some_name_ on table _some_table_ may cause cycles or multiple cascade paths.)

answered Dec 02 '16 at 14:03

Dmitry

16,110
4
61
73

I already know the difference between the delete modes, but, this is a special case, because it's for a join table. Unlike – grokky Dec 02 '16 at 16:39
1

This is exactly one of my problems that should I use cascade or not? I did cascade, but cascade delete operation is running on db side, and if there is no trigger you wont get back your records. So, I did deletion process on when user update the enrollement table like removing a teacher to a student or vice verse. Thus, I can track the operations and save the records. May be you can also do like that @grokky – kizilsu Dec 02 '16 at 21:13
@kizilsu Dmitry's answer is correct and gives good background to the problem. But to decide which delete mode to use, see my answer as well. – grokky Dec 06 '16 at 07:20

score 3 · Accepted Answer · answered Dec 06 '16 at 07:18

3

It's seems confusing at first to model the join table, because <=EF6 didn't need it. But it's actually simple.

When deleting a Teacher entity, you need to delete its relationships to all Student entities. When deleting a Student entity, you need to delete its relationships to all Teacher entities.

So join entities must always be CASCADE deleted.

answered Dec 06 '16 at 07:18

grokky

8,537
20
62
96

Yes, you are right. This is what I did most of my relationship tables. What about that I don't want to delete relationships on db side? I need to log or archive some important relationships. In this teacher student example this is not necessary, but I have that kind of situation. What would you do or suggest anything? @grokky – kizilsu Dec 06 '16 at 13:04
@kizilsu I suppose the easiest is to set a trigger on the db and create an audit log that way. Or, map it in EF, and create an entry every time you delete. – grokky Dec 06 '16 at 13:39
Doesn't that contradict the *Important 2* part of the other answer? It's stated that both linked entities can't be cascading the deletion and at least one of them needs to be set to restricted behavior. I agree with your reasoning but when I tested, I got error updating database (just as the other answer suggests). – Konrad Viltersten Jul 19 '19 at 22:26

How do I model deletion in an EF Core many-to-many relationship?

2 Answers2