Entity Framework multiple cascading deletes
I am developing an application using Entity-Framework code first and migrations.
I have the following class representing communication between two users when one user backs up another for some product. User and Product classes do not have UserBackup references. This relationship should appear in the dbo.UserBackup table. A user can have multiple backup users for multiple products. Thus, only the combination of ProductId, BackupUserId, and UserId is unique in the table.
public class UserBackup
{
public int Id { get; set; }
public User User { get; set; }
public User BackupUser { get; set; }
public Product Product { get; set; }
}
I am overriding the OnModelCreating method inside the context class like this:
private static void CreateUserBackupTable(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<UserBackup>().ToTable("UserBackup");
modelBuilder.Entity<UserBackup>().HasKey(e => e.Id);
modelBuilder.Entity<UserBackup>().HasRequired<User>(e => e.User).WithMany().Map(x => x.MapKey("UserId"));
modelBuilder.Entity<UserBackup>().HasRequired<User>(e => e.BackupUser).WithMany().Map(x => x.MapKey("BackupUserId"));
modelBuilder.Entity<UserBackup>().HasRequired<Product>(e => e.Product).WithMany().Map(x => x.MapKey("ProductId"));
}
The migration file that was generated after using the Add-Migration command contains the following code.
CreateTable(
"dbo.UserBackup",
c => new
{
Id = c.Int(nullable: false, identity: true),
UserId = c.Int(nullable: false),
BackupUserId = c.Int(nullable: false),
ProductId = c.Int(nullable: false)
})
.PrimaryKey(t => t.Id)
.ForeignKey("dbo.User", t => t.UserId, cascadeDelete: true)
.ForeignKey("dbo.User", t => t.BackupUserId, cascadeDelete: true)
.ForeignKey("dbo.Product", t => t.ProductId, cascadeDelete: true)
.Index(t => t.UserId)
.Index(t => t.BackupUserId)
.Index(t => t.ProductId);
However, when I run the application, I get the following exception:
The FOREIGN KEY constraint view "FK_dbo.UserBackup_dbo.User_BackupUserId" in the UsersBackup table can cause loops or multiple cascading paths.
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or change other FOREIGN KEY constraints.
Failed to create constraint. See previous errors.
If I remove the foreign key constraint, I don't get the error. Or at least specify "cascadeDelete: false". But I need the entry to be deleted from the UserBackup table in case the Backup user is deleted.
source to share
This is a SQL Server limitation, not Entity Framework. In SQL Server, you cannot have 2 foreign keys with cascading delete to the same table (UserId and BackupUserId point to the same table).
I asked people at MS about this in 2009 and the answer was that this limitation would not be lifted. Now, in 2014, the situation has not changed, so I think they took it seriously.
The only option is to manually manage the deletion and remove the cascading option.
Edit:
To remove the cascade just add .WillCascadeOnDelete(false);
for each relationship or turn off the cascade for the entire model:
modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
source to share