如何在EF中删除级联->非级联删除链底部的实体?

Sorry for the entirely unhelpful title... I couldn't put this problem into any better words.

很抱歉，我的标题毫无用处……我不能把这个问题说得更好。

I have the following entities, each of which are identified by an Id attribute:

我有以下实体，每个实体都由Id属性标识:

• Chassis
• 底盘
• Slots
• 槽
• Cards
• 卡片

As I'm using POCO, the entities are nothing ground breaking. For instance, the Chassis class is defined like so:

当我使用POCO时，实体并不是什么突破性的东西。例如，底盘类的定义如下:

public class Chassis
{
    public int Id { get; set; }

    // Other properties omitted for brevity.

    public ICollection<Slot> Slots { get; set; }

    public Chassis() 
    {
        Slots = new Collection<Slot>();
    }
}

The relationships are as follows:

关系如下:

• A chassis has many slots (CASCADE).
• 底盘有许多槽(级联)。
• A card has many slots, as a card can be a multi-slot card (SET NULL).
• 卡有许多槽，因为卡可以是多槽卡(设置为NULL)。
• A slot must belong to a Chassis (pubic int ChassisId { get; set; })
• 槽必须属于机箱(耻骨内ChassisId {get;设置;})
• A slot does not have to have a Card (public int? CardId { get; set; }).
• 插槽不必有卡(公共int?CardId {得到;设置;})。

So, when deleting a chassis, all slots are deleted. Fine. However, I'd also like to delete all of the cards that were installed in those slots. Not fine. I'm trying to hook some code into an OnSavingChanges event (which I fire just before SaveChanges()), such that when a chassis is marked deleted, I delete the cards for it as well.

因此，在删除机架时，将删除所有槽。很好。但是，我也想删除所有安装在那些槽中的卡片。不是很好。我试图将一些代码连接到OnSavingChanges事件(我在SaveChanges()之前触发它)中，这样当一个底盘被标记为删除时，我也会删除它的卡片。

First, I tried:

首先,我试着:

OnSavingChanges += (x, y) =>
{
    var ctx = x as DbContext;
    var chassis = ctx.ChangeTracker.Entries<Chassis>().Where(e => e.State == EntityState.Deleted);

    // Delete all cards on a deleted chassis.
    foreach (var c in chassis)
    {
        // Cannot just do c.Slots, as EF seems to empty the nav. property now the
        // chassis is deleted.
        var slots = ctx.Slots.Where(s => s.ChassisId == c.Entity.Id).ToList();

        foreach (var s in slots)
        {
            if (s.Card != null)
            {
                ctx.Cards.Remove(s.Card);
            }
        }
    }
};

... but this throws the exception:

…但这就抛出了一个例外:

An exception of type 'System.Data.Entity.Infrastructure.DbUpdateException' occurred in EntityFramework.dll but was not handled in user code

类型'System.Data.Entity.Infrastructure的一个例外。发生在EntityFramework DbUpdateException”。但是在用户代码中没有处理dll

Additional information: Unable to insert or update an entity because the principal end of the 'Chassis_Slots' relationship is deleted.

附加信息:无法插入或更新实体，因为“chassis_slot”关系的主体端被删除。

I then tried to add ctx.Detach(s); within my inner foreach loop, to stop EF trying to save the already-deleted-via-cascade Slots entity:

然后我尝试添加ctx.Detach(s);在我的内部foreach循环中，停止EF试图保存已经删除的-via-cascade slot实体:

foreach (var s in slots)
{
    if (s.Card != null)
    {
        ctx.Cards.Remove(s.Card);
    }

    // Otherwise EF attempts to save the slot, which results in a exception saying the principle
    // end of the relatioship Chassis_Slots has already been deleted.
    ctx.Detach(s);
}

... however EF then cries with the following exception:

…但是EF却有以下例外:

Additional information: The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.Slots_dbo.Cards_CardId". The conflict occurred in database "...", table "dbo.Slots", column 'CardId'.

附加信息:DELETE语句与引用约束“FK_dbo.Slots_dbo.Cards_CardId”冲突。冲突发生在数据库“…”dbo、表”。槽”,列“CardId”。

The statement has been terminated.

声明已被终止。

... which kinda leaves me between a rock and a hard place, and completely out of ideas.

…这让我进退两难，毫无头绪。

Can anyone suggest a more successful way/ approach to do this?

有人能提出一个更成功的方法吗?

3 个解决方案

#1

Here is the sequence.

这是序列。

• When Chassis is deleted, all Slots will also be deleted because of the delete cascade mechanism. Simple right?
• 当底盘被删除时，由于删除级联机制，所有的槽都将被删除。简单的对吧?
• Then you intercept the saving changes, you want to also delete Card manually on the same transaction single SaveChanges, which I'm not sure how EF will generate the sql query even though the cards deletion syntax is added later, but when I checked it using profiler, it deleted cards first (should be chassis first).
• 然后拦截保存更改,您还想要手动删除卡在同一事务单SaveChanges,这我不知道英孚将生成sql查询即使卡片添加删除语法后,但是当我检查使用分析器,它首先删除卡(应该首先底盘)。
• What happens is, the Unchanged Slot (that will be automatically deleted by delete cascade) changes into Modified Slot.
• 所发生的是，未更改的槽(将通过删除级联自动删除)将更改为已修改的槽。
• Why? Because when you also delete Card, the corresponding Slot's CardId has to be set as null.
• 为什么?因为当你同时删除Card时，对应槽的CardId必须设置为null。
• Now the final result is Slot becomes modified, but Chassis has been deleted.
• 现在最终的结果是槽被修改了，但是底盘被删除了。

To fix the problem, you need to introduce new transaction / context.

要解决这个问题，需要引入新的事务/上下文。

public override int SaveChanges()
{
    var deletedCardIds = new List<int>();
    var chassises = ChangeTracker.Entries<Chassis>().Where(e => e.State == EntityState.Deleted);
    foreach (var chassis in chassises)
    {
        var slots = Slots.Where(s => s.ChassisId == chassis.Entity.Id).ToArray();
        foreach (var slot in slots)
        {
            if (slot.CardId.HasValue && !deletedCardIds.Contains(slot.CardId.Value))
            {
                deletedCardIds.Add(slot.CardId.Value);
            }
        }
    }

    // Commits original transaction.
    var originalRowsAffected = base.SaveChanges();

    int additionalRowsAffected = 0;
    if (deletedCardIds.Count > 0)
    {
        // Opens new transaction.
        using (var newContext = new AppContext())
        {
            foreach (var cardId in deletedCardIds)
            {
                var deletedCard = newContext.Cards.Find(cardId);
                if (deletedCard != null)
                {
                    newContext.Cards.Remove(deletedCard);
                }
            }

            // Commits new transaction.
            additionalRowsAffected = newContext.SaveChanges();
        }
    }

    return originalRowsAffected + additionalRowsAffected;
}

PS

• You have two separated transactions, it might cause unexpected behavior (atomicity is not guaranteed).
• 您有两个独立的事务，它可能导致意外的行为(原子性没有保证)。
• You might want to redesign the database for ideal solution.
• 您可能需要重新设计数据库以获得理想的解决方案。

UPDATE

Today I just realized that we can just simply use a TransactionScope to commit several operations in a single transaction. Just like doing this code in database.

今天我才意识到，我们可以简单地使用TransactionScope在一个事务中提交多个操作。就像在数据库中做这个代码一样。

begin tran
delete from dbo.Chassis
delete from dbo.Cards
commit tran

If you use EF6 onwards, you can just use Database.BeginTransaction otherwise use TransactionScope.

如果您使用EF6，您可以使用数据库。BeginTransaction否则TransactionScope使用。

public override int SaveChanges()
{
    var deletedCardIds = new List<int>();
    var chassises = ChangeTracker.Entries<Chassis>().Where(e => e.State == EntityState.Deleted);
    foreach (var chassis in chassises)
    {
        var cardIds = Slots.Where(s => s.ChassisId == chassis.Entity.Id)
            .Where(s => s.CardId.HasValue)
            .Select(s => s.CardId.Value)
            .ToArray();
        deletedCardIds.AddRange(cardIds);
    }

    int originalRowsAffected;
    int additionalRowsAffected;
    using (var transaction = new TransactionScope())
    {
        originalRowsAffected = base.SaveChanges();

        deletedCardIds.Distinct().ToList()
            .ForEach(id => Entry(new Card { Id = id }).State = EntityState.Deleted);
        additionalRowsAffected = base.SaveChanges();

        transaction.Complete();
    }

    return originalRowsAffected + additionalRowsAffected;
}

By introducing transaction at above updated code, both SaveChanges either all occur, or nothing occurs, atomicity is guaranteed now.

通过在上面更新的代码中引入事务，两个保存更改要么全部发生，要么什么都没有发生，现在可以保证原子性。

#2

There are a few elements about your current solution that is making the cascading delete troublesome.

关于当前解决方案，有一些元素使级联删除变得麻烦。

1. The Card entity is self-referential. I.e. a card can either be a leaf-level card (with no child cards), or a branch-level card which aggregates child cards. From a domain perspective, I imagine that either a card has no children or exactly one set of children. I will use this assumed domain knowledge as part of my solution. Given free reign, I would remodel this as two entities: Card and CardWithChildren and make this explicit.

   卡片实体是自我参照的。例如，一张卡片可以是一张单张卡片(没有子卡片)，也可以是一张收集子卡片的卡片。从领域的角度来看，我认为一张卡片要么没有孩子，要么就是一组孩子。我将使用这个假定的领域知识作为解决方案的一部分。如果有了*支配，我将把它重新建模为两个实体:Card和CardWithChildren，并使其显式。

2. Without knowing more about your domain, it appears that the Slot entity is purely an object orientated device to allow Card entities to belong to either a Chassis or another Card. However, I would look at the relationship the other way around to how stated in the question. Rather than a slot can belong to a chassis or a card, I would model this as a card belongs to a slot, and then express the relationship polymorphically on the card. This can then be further reduced to just saying that a card is owned by either a chassis or a further card.

   在不了解域的情况下，Slot实体看起来纯粹是一个面向对象的设备，允许Card实体属于底盘或其他Card。然而，我会从另一个角度来看待这个问题。不是一个槽可以属于底盘或卡，我将它建模为一个卡属于一个槽，然后在卡上以多形态表示关系。然后，这可以进一步简化为只说一张卡片属于一个机架或另一张卡片。

Here's an example that takes into consideration the above. It has the following features:

这里有一个考虑上述因素的例子。它具有以下特点:

1. To allow the relationship between cards and containing chassis and parent cards, I have modelled the chassis and card types as extending a base type called CardContainer that aggregates cards through a card collection.

   为了允许卡片与包含底盘和父卡片之间的关系，我将底盘和卡片类型建模为扩展一个名为CardContainer的基类型，该基类型通过卡片集合聚集卡片。

2. The CardContainer entity model will then be expressed as a TPT (table per type) strategy by Entity Framework, leading to three tables: CardContainers, Cards and Chassis. I.e. I have made a slight change to your data model to abstract the collection aspects of cards and chassis into a separate table. The Card and Chassis entities then have a shared primary key back to their respective CardContainer entity.

   然后，通过实体框架将CardContainer实体模型表示为TPT(每种类型的表)策略，这将导致三个表:CardContainers、Cards和底盘。例如，我对您的数据模型做了一点修改，将卡片和机箱的收集方面抽象为一个单独的表。然后，Card和底盘实体有一个共享主键返回到各自的CardContainer实体。

3. The self-referential nature of the Cards table will remain. Theoretically this means that we can have an infinite depth of card-->card relationships. However, to allow the Entity Framework cascading delete to work with the TPT strategy, we need to explicitly tell Entity Framework about the maximum depth when retrieving cards. This is done by Including the correct depth of cards as part of the retrieval of a chassis entity.

   卡牌表的自我参照性质将保持不变。理论上这意味着我们可以有无限的卡片深度——>卡片关系。但是，为了允许实体框架级联删除使用TPT策略，我们需要显式地告诉实体框架检索卡片时的最大深度。这是通过将正确的卡片深度作为检索底盘实体的一部分来实现的。

This approach requires a single call to SaveChanges on a single Context and is hence atomic.

这种方法需要在单个上下文中调用SaveChanges，因此是原子性的。

NOTE: This solution doesn't contain a Slot entity for the reasons above as well as to keep the code sample small(ish). However, this solution can easily be extended to add it back by making Card and Chassis extend a SlotCollection, which in turn aggregates Slot entities which hold a single Card entity. The fundamental approach remains the same.

注意:由于上述原因，该解决方案不包含槽实体，并且使代码示例保持较小(ish)。但是，通过使Card和底盘扩展一个SlotCollection，这个解决方案可以很容易地扩展为将其添加回，而SlotCollection反过来又聚合了包含单个Card实体的槽实体。基本的方法仍然是一样的。

Entity Framework Code First POCO's

首先是POCO的实体框架代码

public abstract class CardContainer
{
    public int Id { get; set; }
    public ICollection<Card> Cards { get; set; }

    protected CardContainer()
    {
        Cards = new List<Card>();
    }
}

public class Chassis : CardContainer
{             
}

public class Card : CardContainer
{
    public CardContainer Container { get; set; }
    public int ContainerId { get; set; }
}    

POCO Configurations

少配置

public class CardContainerConfiguration : EntityTypeConfiguration<CardContainer>
{
    public CardContainerConfiguration()
    {
        ToTable("CardContainers");
        HasKey(k => k.Id);
        Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);   
        HasMany(f => f.Cards)
            .WithRequired(p => p.Container)
            .HasForeignKey(p => p.ContainerId)
            .WillCascadeOnDelete(true);
    }
}

public class ChassisConfiguration : EntityTypeConfiguration<Chassis>
{
    public ChassisConfiguration()
    {
        ToTable("Chassis");
    }
}

public class CardConfiguration : EntityTypeConfiguration<Card>
{
    public CardConfiguration()
    {
        ToTable("Cards");
        HasRequired(x => x.Container).WithMany(x => x.Cards).HasForeignKey(x => x.ContainerId);            
    }
}    

Example Context

例子上下文

public class EfContext : DbContext
{              
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {           
        modelBuilder.Configurations.Add(new CardContainerConfiguration());
        modelBuilder.Configurations.Add(new ChassisConfiguration());
        modelBuilder.Configurations.Add(new CardConfiguration());           

        base.OnModelCreating(modelBuilder);
    }
}

Example usage

示例使用

public void CanCascadeDeleteCards()
{
    using (var context = new EfContext())
    {
        // We have to tell Entity Framework of the maximum depth of the 
        // card-->card relationship using the Include method
        var chassis = context.CardContainers.OfType<Chassis>()
            .Include(x => x.Cards.Select(y => y.Cards))
            .First();
        context.CardContainers.Remove(chassis);             
        context.SaveChanges();
    }
}

#3

Why are you deleting a chassis and then only deleting the cards in an extra event? I believe that is causing all of your problems. In my projects, whenever I want to delete both parent and children without an explict cascade relationship in the database, I always start with the children and then delete the parents without any problem.

为什么要删除一个机箱，然后只删除额外事件中的卡片?我相信这是造成你们所有问题的原因。在我的项目中，每当我想要在数据库中删除没有说明级联关系的父母和孩子时，我总是先从孩子开始，然后删除他们的父母，没有任何问题。

In your code to delete a chassis, get all the slots and iterate through them and remov all your cards, just like you do currently in the event. If you mark the cards as deleted before you delete the chassis or slots, then when you delete the cards it won't mark the slots as modified. That should prevent your error and let you delete everything in one transaction.

在删除机架的代码中，获取所有的槽，遍历它们并删除所有的卡，就像您当前在事件中所做的那样。如果您在删除机架或插槽之前将这些卡片标记为已删除，那么当您删除这些卡片时，它将不会将这些插槽标记为已修改。这应该可以防止您的错误，并允许您在一个事务中删除所有内容。

It is also far cleaner and easier to read to mark all entites as deleted in one spot. Right now, it is very easy for anyone reading your chassis delete to not realize that also deletes any cards associated with it.

它也更干净，更容易阅读，以标记所有的实体被删除在一个地方。现在，任何阅读你的底盘删除的人都很容易没有意识到它也会删除任何与之相关的卡片。