c# - 从具有外键约束的多个表中删除记录

Question

我正在使用 Asp.Net MVC 并构建一个电子商务应用程序。

我的数据库中有 4 个表

Product 有 ProductId(PK), Name, Price, StoreId(FK)

Store 有 StoreId(PK), Name, CreatedOn

Order 有 OrderId(PK), StoreId(FK), OrderDate, OrderTotal

OrderDetail 有 OrderDetailId(PK), OrderId(FK), Quantity, UnitPrice, Weight

当我尝试删除单个产品时，它没有给出任何错误并删除了产品，如果我删除 Order 记录它给出了 OrderDetails 外键错误，我知道我必须先删除OrderDetail记录，再删除Order记录。

我的场景是，商店有多种产品，每个商店都有订单，然后是订单详情。

现在，我不想每次都删除一条记录，因为这会花费很多时间。我想删除一个 STORE，其他表(Product、Order、OrderDetails)中的所有记录也将被删除。我该怎么做？

我试过了这段代码在我的 IdentityModel.cs 中

protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>()
            .HasRequired(s => s.Store)
            .WithMany()
            .HasForeignKey(d => d.StoreId)
            .WillCascadeOnDelete(false); // also tried with TRUE, didn't work

        modelBuilder.Entity<Order>()
            .HasRequired(s => s.Store)
            .WithMany()
            .HasForeignKey(d => d.StoreId)
            .WillCascadeOnDelete(false); // also tried with TRUE, didn't work

        modelBuilder.Entity<OrderDetail>()
            .HasRequired(s => s.Order)
            .WithMany()
            .HasForeignKey(d => d.OrderId)
            .WillCascadeOnDelete(false); // also tried with TRUE, didn't work
    }

Controller

[HttpPost]
    public ActionResult StoreDelete(int? id, Store store)
    {
        try
        {
            if (id == null)
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);

            store = db.Stores.Find(id);

            if (store == null)
            {
                return HttpNotFound();
            }
            else
            {
                db.Stores.Remove(store);
                db.SaveChanges();
                return RedirectToAction("ManageStores", "Store");  
            }

        }

        catch
        {
            return View();
        }
    }

查看

@using (Html.BeginForm("StoreDelete", "Store", new { id = ViewBag.StoreId }, FormMethod.Post, new { @class = "form-horizontal", role = "form" }))
{
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
<h4 class="text-danger">Are you sure you want to delete this store ?</h4>
<br />
@Html.TextBoxFor(m => m.Name, new { @class = "inputboxes texbboxstyle form-control", @readonly = "readonly" })
<br>
@Html.TextBoxFor(m => m.CreatedOn, new { @class = "inputboxes texbboxstyle form-control", @readonly = "readonly" })
<br>
<input type="submit" class="btn-lg btn-danger" style="border-radius:0px; width:200px;" value="Delete" />
}

Answer 1

您可以从表中删除多条记录，但要按顺序喜欢

var Order = db.Orders.where(x=>x.StoreId == store.StoreId) 
var OrderDetail = db.OrderDetails.where(x=>x.OrderId== Order.OrderId) 
var Product = db.Products.where(x=>x.StoreId == store.StoreId)

db.Orders.RemoveRange(Order);
db.OrderDetails.RemoveRange(OrderDetail);
db.Products.RemoveRange(Product);
db.Stores.Remove(store);
db.SaveChanges();

希望对你有用