Entity Framework Multiple Cascading Deletes

Question

I'm developing an application using Entity-Framework code first and migrations.

I have the following class, representing relation between two users when one user makes backup of another one for some product. User and Product classes don't have any references to UserBackup. This relation should map to dbo.UserBackup table. User can have multiple Backup Users for multiple products. So basically 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 override OnModelCreating method within 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, which was generated after "Add-Migration" command is applied, 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:

Introducing FOREIGN KEY constraint 'FK_dbo.UserBackup_dbo.User_BackupUserId' on table 'UsersBackup' may cause cycles or multiple cascade paths.

Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Could not 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 record to be deleted from UserBackup table in case when Backup user is deleted.

Answer 1

This is a limitation of SQL Server not Entity Framework. In SQL Server you can't have 2 foreign keys with cascade delete to the same table (UserId and BackupUserId point to the same table).

I asked people at MS about it in 2009 and the answer was this limitation won't be removed. Now in 2014 it did not change so I guess they were serious about it.

Your only option is to manually manage deletion and remove cascade option.

Edit:

To remove cascade just add .WillCascadeOnDelete(false); for each relation or turn cascading off for a whole model:

modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();