CODEWITHSUNDEEP
c#entity-framework-4ef-code-firstentity-framework-4.3
Justin
Justin

Reputation: 1310

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?

Upvotes: 6

Views: 490

Answers (1)

Justin
Justin

Reputation: 1310

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.

Upvotes: 2

Related Questions