How to disable cascading deletion for link tables in EF codes?

Question

I want to disable cascading deletes for the link table with the first entity code. For example, if many users have many roles, and I'm trying to remove a role, I want this removal to be blocked if there are no users who are currently associated with this role. I already delete the cascade exception convention in my OnModelCreating :

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

And then I set up a table of links to the user role:

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

However, when EF creates a database, it creates a delete cascade for foreign key relationships, for example.

 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 the EF creation of this delete cascade?

Answer 1

I got the answer. :-) These cascading deletes were created due to ManyToManyCascadeDeleteConvention . You need to delete this convention to prevent it from creating cascading deletes for link tables:

 modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>(); 

Answer 2

I agree with Ebram Khalil that disabling it for one table is a good option. However, I like to stick with auto-generated migrations, as I can configure it 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 that this saves the deletion going in the other direction, so if both need to be blocked (it makes sense in this example), a similar call will need to be made starting from Entity<User>(Role)

Of course, this comes after a question has been asked. Therefore, it may be invalid in 2012.

Answer 3

I believe that disabling ManyToManyCascadeDeleteConvention around the world is not a smart option. Instead, it is better to disable it only for the corresponding table.

This can be achieved by editing the generated migration file for the cascadeDelete property. For example:

AddForeignKey("dbo.UsersRoles", "UserId", "dbo.User", "UserId", cascadeDelete: false);