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

C#.NetDatabaseEntity FrameworkEf Code-First

C# Problem Overview


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?

C# Solutions


Solution 1 - C#

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>();

Solution 2 - C#

I believe that turning off ManyToManyCascadeDeleteConvention globally is not a wise option. Instead, it's better to turn it off only for the concerned table.

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

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

Solution 3 - C#

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.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionJezView Question on Stackoverflow
Solution 1 - C#JezView Answer on Stackoverflow
Solution 2 - C#ebram khalilView Answer on Stackoverflow
Solution 3 - C#TwainJView Answer on Stackoverflow