Deleting Entity in Many to Many Relationship Leaves Orphans in Relationship Table

Question

When deleting records that are in a many to many relationship, the relationship table has orphan records. I have the following many to many relationship set up in my DbContext.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  modelBuilder.Entity<Car>()
     .HasMany(u => u.Owners)
     .WithMany(l => l.Cars)
     .Map(ul =>
     {
       ul.MapLeftKey("CarId");
       ul.MapRightKey("OwnerId");
       ul.ToTable("CarOwners");
     });
}

My Owner model has virtual property Cars:

public virtual ICollection<Car> Cars { get; set; } 

My Car model has virtual property Owners:

public virtual ICollection<Owner> Owners { get; set; } 

I delete a Car as follows (db is my DbContext, car is a Car model).

db.Cars.Remove(car);
db.SaveChanges()

When I delete a Car, I was expecting all records in the table CarOwners with that CarId to be deleted as well but this is not the case. Any advice?

Answer 1

The solution was:

ALTER TABLE [dbo].[CarOwners]  WITH CHECK ADD  CONSTRAINT [FK_Car_Owners] FOREIGN KEY([CarId])
REFERENCES [dbo].[Car] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[CarOwners]  WITH CHECK ADD  CONSTRAINT [FK_Owner_Cars] FOREIGN KEY([OwnerId])
REFERENCES [dbo].[Owner] ([Id])
ON DELETE CASCADE
GO

NOTE: If you are adding constraints to an existing table with data, you will have to make sure that orphan records are removed first... or else the ADD CONSTRAINT will fail.