Delete cascade using intermediate table

Question

I have these entities Course and Student and an intermediate table CourseStudent.

public class Course
{
    public decimal Id { get; set; }
    public decimal IdState { get; set; }
    public decimal IdCity { get; set; }
    public string Name { get; set; }
    public List<CourseStudent> CourseStudents { get; set; } = new List<CourseStudent>();
}   
public class Student
{
    public decimal Id { get; set; }
    public decimal IdState { get; set; }
    public decimal IdCity { get; set; }
    public string Name { get; set; }
    public List<CourseStudent> CourseStudents { get; set; } = new List<CourseStudent>();
}

public class CourseStudent
{
    public decimal Id { get; set; }
    public decimal IdState { get; set; }
    public decimal IdCity { get; set; }
    public decimal IdCourse { get; set; }
    public decimal IdStudent { get; set; }
    public Course Course { get; set; }
    public Student Student { get; set; }
}

My Configuration files:

public class CourseStudentConfiguration : IEntityTypeConfiguration<CourseStudent>
{
    public void Configure(EntityTypeBuilder<CourseStudent> builder)
    {
        builder.ToTable("Courses_Students");
        builder.Property(x => x.IdState).HasColumnType("numeric(18,0)").IsRequired();
        builder.Property(x => x.IdCity).HasColumnType("numeric(18,0)").IsRequired();
        builder.Property(x => x.IdCourse).HasColumnType("numeric(18,0)").IsRequired();
        builder.Property(x => x.IdStudent).HasColumnType("numeric(18,0)").IsRequired();

        builder
            .HasOne(x => x.Course)
            .WithMany(x => x.CourseStudents)
            .HasForeignKey(x => x.IdCourse)
            .OnDelete(DeleteBehavior.Cascade);

        builder
            .HasOne(x => x.Student)
            .WithMany(x => x.CourseStudents)
            .HasForeignKey(x => x.IdStudent)
            .OnDelete(DeleteBehavior.Cascade);
    }
}

As you can see, my relations are only in my intermediate table.

public class CourseConfiguration : IEntityTypeConfiguration<Course>
{
    public void Configure(EntityTypeBuilder<Course> builder)
    {
        builder.ToTable("Courses");
        builder.Property(x => x.IdState).HasColumnType("numeric(18,0)").IsRequired();
        builder.Property(x => x.IdCity).HasColumnType("numeric(18,0)").IsRequired();
        builder.Property(x => x.Name).HasColumnType("varchar(64)").IsRequired();
    }
}

public class StudentConfiguration : IEntityTypeConfiguration<Student>
{
    public void Configure(EntityTypeBuilder<Student> builder)
    {
        builder.ToTable("Students");
        builder.Property(x => x.IdState).HasColumnType("numeric(18,0)").IsRequired();
        builder.Property(x => x.IdCity).HasColumnType("numeric(18,0)").IsRequired();
        builder.Property(x => x.Name).HasColumnType("varchar(64)").IsRequired();
    }
}

When I was deleting some Course the CourseStudent was deleted as was expected, but the Student was not deleted and I need the Student to be deleted too.

What changes do I need to do on CourseStudentConfiguration (or StudentConfiguration) in order to be deleted Student too when the Course is deleted.

Answer 1

Cascade does not work the way you think it works, you cannot Cascade from an intermediate table to parent tables.

You define Cascade on an intermediate table for the purpose that you might want to delete something in the parent table which has no/different constraints and then it will look at the constraint (CASCADE) on the intermediate table and will delete the contents in the intermediate table and not vice-versa.

Rules: (this explains it better, look below)

• Delete product from Product table (parent table with no constraints)?

  |> The product you deleted in Product table will also be deleted in: Order table (intermediate table with CASCADE constraint).

• Delete product from Order table (intermediate table with CASCADE constraint)?

  |> The product you deleted in Order table will not be deleted in the Product table, because Cascade will not work in this way.

Answer 2

Ask yourself a question whether it's possible to code it using plain SQL, because if it can't be achieved that way, it can't be configured in EF as well.

ON DELETE CASCADE is a property set on Foreign Key relationship

[ CONSTRAINT constraint_name ]   
{   
        FOREIGN KEY   
        ( column [ ,...n ] )  
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]   
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]   
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]   
        [ NOT FOR REPLICATION ]   
}  

You set two ON DELETE CASCADE for CourseStudent:

1. IdCourse REFERENCES Courses (Id) ON DELETE CASCADE
2. IdStudent REFERENECS Students (Id) ON DELETE CASCADE

It means that CourseStudent will be deleted if either of Student or Course will be deleted. There is no Foreign Key relationship defined in Student table, thats why it won't be deleted - and it is expected behaviour. Would you also expect the Course to be deleted if Student is deleted? I don't think so :)

Solution:

1. Delete Student object in the code 'manually' every time you delete CourseStudent. This logic can be encapsulated in some kind of repository/service.

2. You can either define a database trigger

   CREATE TRIGGER sampleTrigger ON CourseStudent FOR DELETE AS DELETE FROM Student WHERE Id IN (SELECT deleted.IdStudent FROM deleted) GO