实体框架代码首先5级联删除多对多表错误 [英] Entity Framework Code First 5 Cascade Delete on many to many tables error

问题描述

我有这个模式

public class State
{
    public State()
    {
        this.Promotions = new List<Promotion>();
        this.Branches = new List<Branch>();
        this.Stores = new List<Store>();
    }

    public int Id { get; set; }
    public string Description { get; set; }

    public virtual ICollection<Promotion> Promotions { get; set; }
    public virtual ICollection<Store> Stores { get; set; }
    public virtual ICollection<Branch> Branches { get; set; }
}

public class Store
{
    public Store()
    {
        this.Promotions = new List<Promotion>();
        this.Branches = new List<Branch>();
    }

    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Promotion> Promotions { get; set; }
    public virtual ICollection<Branch> Branches { get; set; }

    public int StateId { get; set; } // Foreign key
    public virtual State State { get; set; } // Navigation Property
}

public class Branch
{
    public Branch()
    {
        this.Promotions = new List<Promotion>();
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public int StoreId { get; set; } // Foreign key
    public int StateId { get; set; } // Foreign key

    public virtual Store Store { get; set; } // Navigation Property
    public virtual State State { get; set; } // Navigation Property

    public virtual ICollection<Promotion> Promotions { get; set; }
}

    public class Promotion
{
    public Promotion()
    {
        this.Stores = new List<Store>();
        this.Branches = new List<Branch>();
        this.Productos = new List<Producto>();
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public int StateId { get; set; }

    public virtual ICollection<Store> Stores { get; set; }
    public virtual ICollection<Branch> Branches { get; set; }
    public virtual ICollection<Product> Products { get; set; }

    public virtual State State { get; set; }

}

这在我的上下文中：

// State
modelBuilder.Entity<State>()
    .HasMany(p => p.Promotions)
    .WithRequired(e => e.State)
    .WillCascadeOnDelete(false);

modelBuilder.Entity<State>()
    .HasMany(s => s.Branches)
    .WithRequired(e => e.State)
    .WillCascadeOnDelete(false);

modelBuilder.Entity<State>()
   .HasMany(e => e.Stores)
   .WithRequired(e => e.State)
   .WillCascadeOnDelete(true);

 // Store
modelBuilder.Entity<Store>()
    .HasMany(b => b.Branches)
    .WithRequired(s => s.Store)
    .WillCascadeOnDelete(true);

// Many to many
modelBuilder.Entity<Store>().
  HasMany(p => p.Promotions).
  WithMany(s => s.Stores).
  Map(
   m =>
   {
       m.MapLeftKey("StoreId");
       m.MapRightKey("PromotionId");
       m.ToTable("Store_Promotion");
   });

modelBuilder.Entity<Promotion>().
 HasMany(e => e.Products).
 WithMany(p => p.Promotiones).
 Map(
  m =>
  {
      m.MapLeftKey("PromotionId");
      m.MapRightKey("ProductoId");
      m.ToTable("Promotion_Producto");
  });

modelBuilder.Entity<Branch>().
 HasMany(p => p.Promotiones).
 WithMany(b => b.Branches).
 Map(
  m =>
  {
      m.MapLeftKey("BranchId");
      m.MapRightKey("PromotionId");
      m.ToTable("Branch_Promotion");
  });

现在如果我打开多个WillCascadeOnDelete的状态（前三个流畅的映射）得到错误

Now if I turn on more than one WillCascadeOnDelete of the State (first three in the fluent mapping) I get the error

测试方法Proj.Data.Tests.UnitTest1.TestPromotion抛出异常：

System.Data。 SqlClient.SqlException：FOREIGN KEY简介在分支上的约束FK_dbo.Branch_dbo.Store_StoreId可能会导致循环或多个级联路径。指定ON DELETE NO ACTION或ON UPDATE NO ACTION，或修改其他FOREIGN KEY约束。
无法创建约束。看到以前的错误。

Test method Proj.Data.Tests.UnitTest1.TestPromotion threw exception:
System.Data.SqlClient.SqlException: Introducing FOREIGN KEY constraint 'FK_dbo.Branch_dbo.Store_StoreId' on table 'Branch' 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.

我知道，我从Julie Lerman的书中读过：

I know that, and I've read from Julie Lerman's book:

某些数据库（包括SQL Server）不支持指定
级联删除的多个关系指向同一个表

Some databases (including SQL Server) don’t support multiple relationships that specify cascade delete pointing to the same table

正因为如此，因为许多关系表具有来自两个相关表的级联删除。

As it happens since the many to many relationship table has cascade delete coming from both related tables.

所以，我的问题是：这里唯一的选择是关闭父表上的级联删除，并手动处理关系表上的删除？实体框架5中没有任何解决方法吗？

So, my question is: The only choice here is to turn off cascade delete on parent tables and handle the deletion on relationship table manually? Isn't there any workaround from Entity Framework 5 for this?

推荐答案

好的，我明白了这个问题。没有多少关系，问题是这个

Ok, I understood the problem. It is not to have a many to many relationship, the problem is this

State -> Promotion -> PromotionStore
State -> Branch -> BranchPromotion
State -> Store -> StorePromotion

然后商店，分行和商店有FK状态。所以如果我删除一个国家PromotionStore可以达到第一和第三种可能性。

and then Store, Branch and Store have FK to State. So if I delete a State PromotionStore can be reached by 1st and 3rd possibilities.

我最后做的是关闭状态的级联删除和手动删除相关的记录，像这个：

What I ended up doing is turning off cascade delete for State and deleting the related records manually like this:

public override void Delete(State state)
{
   DbContext.Entry(state).Collection(x => x.Promotions).Load();
   DbContext.Entry(state).Collection(x => x.Stores).Load();
   DbContext.Entry(state).Collection(x => x.Branches).Load();

   var associatedPromotions = state.Promotions.Where(p => p.StateId == state.Id);
   associatedPromotions.ToList().ForEach(r => DbContext.Set<Promotion>().Remove(r));

   var associatedStores = state.Stores.Where(e => e.StateId == state.Id);
   associatedStores.ToList().ForEach(e => DbContext.Set<Store>().Remove(e));

   var associatedBranches = state.Branches.Where(s => s.StateId == state.Id);
   associatedBranches.ToList().ForEach(s => DbContext.Set<Branch>().Remove(s));

   base.Delete(state);
}

这篇关于实体框架代码首先5级联删除多对多表错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！