How to disable cascade delete for link tables in EF code-first?

.net c# database ef-code-first entity-framework

Question

I want to disable cascade deletes for a link table with entity framework code-first. For example, if many users have many roles, and I try to delete a role, I want that delete to be blocked unless there are no users currently associated with that role. I already remove the cascade delete convention in my OnModelCreating:

protected override void OnModelCreating(DbModelBuilder modelBuilder) {
    ...
    modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();

And then I set up the user-role link table:

modelBuilder.Entity<User>()
    .HasMany(usr => usr.Roles)
    .WithMany(role => role.Users)
    .Map(m => {
        m.ToTable("UsersRoles");
        m.MapLeftKey("UserId");
        m.MapRightKey("RoleId");
    });

Yet when EF creates the database, it creates a delete cascade for the foreign key relationships, eg.

ALTER TABLE [dbo].[UsersRoles]  WITH CHECK ADD  CONSTRAINT [FK_dbo.UsersRoles_dbo.User_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[User] ([UserId])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[UsersRoles]  WITH CHECK ADD  CONSTRAINT [FK_dbo.UsersRoles_dbo.Role_RoleId] FOREIGN KEY([RoleId])
REFERENCES [dbo].[Role] ([RoleId])
ON DELETE CASCADE
GO

How can I stop EF generating this delete cascade?

1
65
12/4/2012 2:44:17 PM

Accepted Answer

I got the answer. :-) Those cascade deletes were being created because of ManyToManyCascadeDeleteConvention. You need to remove this convention to prevent it from creating cascade deletes for link tables:

modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();
107
10/1/2015 6:16:50 AM

Popular Answer

I agree with Ebram Khalil that turning it off for a single table is a good option. I like to stick as close to the automatically built migrations as I can, however, so I would set it up in OnModelCreating:

modelBuilder.Entity<User>()
    .HasMany(usr => usr.Roles)
    .WithMany(role => role.Users)
    .Map(m => {
        m.ToTable("UsersRoles");
        m.MapLeftKey("UserId");
        m.MapRightKey("RoleId");
    })
    .WillCascadeOnDelete(false);

I believe this preserves the delete going the other direction, so if both needed to be blocked (makes sense in this example) a similar call would need to be made starting with Entity<User>(Role)

Of course, this comes ages after the question was asked. So it may not have been valid in 2012.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow