78

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?

Improve this question

5 Answers 5

Reset to default
120

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>();
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Thanks this helped me a ton. Curious, did you end up removing BOTH conventions or just the Many to Many convention?
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.
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?
I cannot believe that anyone in their right mind would think that cacading deletes were a good default.
@frozen the 'no action' (failing with error) alternative is a much better alternative than unexpectedly removing data.
|
7

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

Improve this answer

4 Comments

"I believe that turning off ManyToManyCascadeDeleteConvention globally is not a wise option" why?
@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
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.
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).
5

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.

Improve this answer

8 Comments

Not tested before posting. .WillCascadeOnDelete is not available here.
@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.
Not in EF4.3.1, 5.0.0, 6+. Never been there.
Which means: the answer is wrong. You should remove it.
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?
|
5

To globally disable the cascading delete behavior in ef core 6 and later, you can insert this snippet in your DbContext file. This changes the behavior at runtime, so you dont have to modify the auto-generated migrations.

I believe the default should be Restrict in any case. If i want to delete the records i have to do it explicitly and i prefer so.

protected override void OnModelCreating(ModelBuilder modelBuilder) { var eTypes = modelBuilder.Model.GetEntityTypes(); foreach(var type in eTypes) { var foreignKeys = type.GetForeignKeys(); foreach(var foreignKey in foreignKeys) { foreignKey.DeleteBehavior = DeleteBehavior.Restrict; } } }
Improve this answer

Comments

1

This works for me in EFCore 6.0.1 and MySql, according to ms docs.

Note: Don't forget to regenerate your migration files after this.

// In your dbContext class protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder .Entity<User>() .HasMany(usr => usr.Roles) .WithMany(role => role.Users) .OnDelete(DeleteBehavior.Restrict); }
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.