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

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?

Solution 1

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

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

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.

Comments

[kmehta]

Thanks this helped me a ton. Curious, did you end up removing BOTH conventions or just the Many to Many convention?

[Jez]

Actually, I just ended up removing the one-to-many convention and selectively re-enabling it for one or two entities. My notes about it say that, because (unlike one-to-many) you can't use the Fluent API to re-enable cascade delete for a many-to-many by using .WillCascadeOnDelete(true), all the many-to-many tables either have to cascade, or not cascade. I considered having them all cascade as the lesser of the evils, because most of the time, if I delete something linked using a many-to-many link table, I want the things it's linked to to be deleted too.

[Rogala]

This didn't seem to work. My problem is when I have two properties of an object of the same type (created by this user and last edited by this user.) I was hoping this would remove the "may cause cycle or multiple cascade paths" error I am getting, but it didn't work. Suggestions?

[Spongman]

I cannot believe that anyone in their right mind would think that cacading deletes were a good default.

[Jim Wolff]

@spongman cascading deletes make sure you dont have orphaned artifacts lying around in your database, in that sense there is nothing wrong with cascade deletes.

[Spongman]

@frozen the 'no action' (failing with error) alternative is a much better alternative than unexpectedly removing data.

[Dave Van den Eynde]

Ah thank you. Much better than adding a WillCascadeOnDelete(false) on every relation.

[SAR]

@Jez is it possible to set only in tow tables allow cascade remove, in model or dbcontext

[Storm Muller]

"I believe that turning off ManyToManyCascadeDeleteConvention globally is not a wise option" why?

[ebram khalil]

@StormMuller imho, many-to-many relationship probably means that both entities need to be existed to have a meaningful relationship; hence, if you deleted one, you probably want the other to be deleted. Also, this would make you DB cleaner /less when you want to delete items literally or hard delete. This is not a must case, sometimes you want to just delete the relationship between both entities, but without deleting any of them

[Storm Muller]

One-to-one would mean both need to exist and One-to-many means that the "one" data set would have to exist. If it's a many-to-many relationship, that means that if a record is deleted in the one data set, it's children might still be used by other records in the data set. So I have to disagree with you.

[Flater]

While I agree with your general advice that it is not wise; there are databases where soft deletes are used (= no actual deleted from the database) and then cascade deletes become pointless and thus it can be a wise decision to not implement them (e.g. to avoid issues with multiple cascade paths).

[Gert Arnold]

Not tested before posting. .WillCascadeOnDelete is not available here.

[TwainJ]

@GertArnold, pretty sure it existed at the time, though it is hard to know, as this is two years old. This was almost certainly intended to be used with EF6. Are you sure you aren't trying it on EF Core? I wouldn't be at all surprised if it didn't work there.

[Gert Arnold]

Not in EF4.3.1, 5.0.0, 6+. Never been there.

[TwainJ]

Thanks for the research.

[Gert Arnold]

Which means: the answer is wrong. You should remove it.

[TwainJ]

Feel free to vote so, and if others agree, I understand they can help you get that done. My memory of this answer is that I only answered because I was doing this very thing in some code that I was working on at the time. I don't have time to verify your assertions, or go through the reams of code that may have been part of my world at the time - much of which is unavailable to me anyway. You may very well be right that this answer is wrong, but for me to take action on that, I would need to verify, and I don't have bandwidth for that.

[TwainJ]

On the other hand, even if I were able to show that this was valid in some very small or limited fashion, two years ago, when I made the answer, it is clear that it will be a pain point for someone, as it appears it was for you, if they were trying to take this advice. I'm sorry if that was the case for you, but I am very glad you were able to add to the conversation so that others will know that this answer may not have as much merit today, if it had any to begin with. I hope it will ease someone else's frustration if they come upon it. I'm happy to let that history stand.

[Gert Arnold]

Your own words "I believe..." and the wrong assertion following show that you weren't sure at the time of writing and didn't take the effort to test your answer. Nothing to do with failing memory or "limited fashion". If you're not wiling to delete the answer because of your precious rep points, then at least correct it. Why deliberately keep pointing people in the wrong direction?