Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 476
Justin Avatar asked Aug 22 '12 14:08

Justin


1 Answers

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.

like image 169
Justin Avatar answered Oct 05 '22 01:10

Justin