删除相关表的多个记录 [英] Deleting multiple records for a related table

问题描述

我的数据库中有这两个表，名为Vendors和VendorPriceBreaks：

 供应商
 ---- ------------- 
 VendorID（PK）
名称
 
 VendorPriceBreaks 
 ----------- ------ 
 VendorPriceBreakID（PK）
 VendorID（FK）
价格
  

当我删除供应商时，我想让所有与其关联的VendorPriceBreaks也被删除。我正在使用实体框架。

我先尝试了这个：

  public RedirectToRouteResult Delete（int id）
 {
 MyEntities entities = new MyEntities（）; 
 
 var vendor = entities.Vendors.FirstOrDefault（v => v.VendorID == id）; 
 entities.Vendors.Context.DeleteObject（vendor）; 
 entities.Vendors.Context.SaveChanges（）; 
 
 return RedirectToAction（Index）; 
} 
  

哪些给了我错误消息： DELETE语句与参考约束FK_VendorPriceBreaks_Vendors

所以我在删除我的对象之前添加了这一行： vendor.VendorPriceBreaks.Clear（）;

但是我收到此错误消息：操作失败：无法更改关系，因为一个或多个外部-key属性不可为空。当对关系进行更改时，将相关的外键属性设置为空值。如果外键不支持空值，则必须定义新的关系，外键属性必须分配另一个非空值，否则不相关的对象必须被删除。

这是最好的方法？

解决方案

其他人已经提供了宝贵的答案你应该选择他们作为接受的答案。

我只是将其添加为回答，因为评论太长。通常有四种方法来实现这一点：

由@ p.campbell描述的Casade删除。重要的是在数据库中定义ON CASCADE DELETE以及EF设计器中的 Cascade 关系。 这里是选项必须的原因设置在双方。

由@Bilgehan描述的存储过程。为了防止存储过程在父进程之前删除子进程。您可以通过 ExecuteStoreCommand 直接执行SQL。

识别关系 - 这是EF中最奇怪的方法之一。它要求子实体具有从其唯一的Id和FK到父代的复合密钥。如果发生这种情况，您可以简单地调用 vendor.VendorPriceBreaks.Clear（），它不仅会删除关系，还会删除子代。缺点是相关对象必须从数据库加载。

手动删除由@Slauma描述的相关对象。如果没有级联删除，存储过程或识别关系，则无需其他方式加载相关对象并逐个删除。

I have these two tables in my database, named Vendors and VendorPriceBreaks:

Vendors
-----------------
VendorID (PK)
Name

VendorPriceBreaks
-----------------
VendorPriceBreakID (PK)
VendorID (FK)
Price

When I delete a Vendor, I'd like to have all the VendorPriceBreaks associated with it deleted as well. I'm using Entity Framework.

I tried this first:

public RedirectToRouteResult Delete(int id)
{
    MyEntities entities = new MyEntities();

    var vendor = entities.Vendors.FirstOrDefault(v => v.VendorID == id);
    entities.Vendors.Context.DeleteObject(vendor);
    entities.Vendors.Context.SaveChanges();

    return RedirectToAction("Index");
}

Which gave me the error message: The DELETE statement conflicted with the REFERENCE constraint "FK_VendorPriceBreaks_Vendors"

So then I added this line before I deleted my object: vendor.VendorPriceBreaks.Clear();

But then I got this error message: The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.

What's the best way to do this?

解决方案

Other guys have already provided valuable answers so you should select on of them as accepted answers.

I'm just adding this as answer because it is too long for comment. There are generally four ways to achieve this:

Casade delete as described by @p.campbell. The important is to define ON CASCADE DELETE on relation in database as well as Cascade on relation in EF designer. Here is a reason why the option must be set on both sides.

Stored procedure as described by @Bilgehan. Just in case of stored procedure delete childs before the parent. You can actually execute SQL directly by ExecuteStoreCommand.

Identifying relation - this is one of the strangest architecutre decissions in EF. It requires that child entity has composite key from its unique Id and FK to parent. If this happens you can simply call vendor.VendorPriceBreaks.Clear() and it will not only remove relations but also deletes childs. The disadvantage is that related objects must be loaded from database.

Manual deletion of related objects as described by @Slauma. If you don't have cascade delete, stored procedure or identifying relation there is no other way then load related objects and delete them one by one.

这篇关于删除相关表的多个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！