Delete records from multiple tables with foreign key constraint

Question

I'm using Asp.Net MVC and building an E-commerce application.

I've 4 tables in my DB

Product which have ProductId(PK), Name, Price, StoreId(FK)

Store which have StoreId(PK), Name, CreatedOn

Order which have OrderId(PK), StoreId(FK), OrderDate, OrderTotal

OrderDetail which have OrderDetailId(PK), OrderId(FK), Quantity, UnitPrice, Weight

When I Try to delete a single product, it doesn't give any error and deleted the product, If I delete Order record it gives an error of OrderDetails foreign key, I know I have to delete OrderDetail record first then Order record.

My scenario is, Store has multiple products, Each store has orders then order details.

Now, I don't want to delete single record every time because it takes alot of time. I want to delete a STORE and all the record from other tables (Product, Order, OrderDetails) will also be deleted. How can I do this ?

I've tried this This code is in my 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();
        }
    }

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" />
}

This question has nothing to do with MVC. This is about Entity Framework. — Panagiotis Kanavos, Feb 10 '16 at 10:43
What didn't work? Where are the mappings for the Store class? — Panagiotis Kanavos, Feb 10 '16 at 10:46
Possible duplicate http://stackoverflow.com/questions/17932720/how-does-willcascadeondelete-in-entity-framework-work The problem is that the foreign key columns are nullable, *not* a bug with WillCascadeOnDelete — Panagiotis Kanavos, Feb 10 '16 at 10:49
Please refer this Link https://www.safaribooksonline.com/library/view/programming-entity-framework/9781449317867/ch04s04.html — Ajas Aju, Nov 29 '17 at 05:39

Imran Luhur · Accepted Answer · 2019-05-02T18:46:34.753

14

You can delete multiple records from table but in order like

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();

I hope it may works for you

edited May 02 '19 at 18:46

answered Feb 10 '16 at 09:50

Imran Luhur

447
3
15

1

Your answer really helped me and I did it but This approach is not quite good, What if I have 15 20 tables in future then I have to write alot of code for delete method. – user3223395667 Feb 10 '16 at 10:52

user853710 · Answer 2 · 2016-02-10T10:28:27.300

2

If you want the child entities gone as well, then change the relationship to be cascade on delete and don't forget to update the model

edited Feb 10 '16 at 10:28

answered Feb 10 '16 at 09:36

user853710

1,715
1
13
27

I've also tried with .WillCascadeOnDelete(true); but didn't work – user3223395667 Feb 10 '16 at 09:41
set it on the database level. set in the database the relations properties. leave C# on the sideline for now. – user853710 Feb 10 '16 at 10:12
how ? I tried to check the properties of table, there is no INSERT and UPDATE option – user3223395667 Feb 10 '16 at 10:19
Found It. Thanks (Y). I've used Database First and written Class Level Validation by DataAnnotations, I don't want to loose all that. Recently I was updating models by deleting all Models and Generating from DB again. Now that I have written code in my Models, is there any other way to update the model ? – user3223395667 Feb 10 '16 at 10:50
That's why I never do CodeFirst. You have much more control using DB first – user853710 Feb 10 '16 at 10:56
The custom Code you have written, You extract into partial classes and recreate it – user853710 Feb 10 '16 at 10:57

score 0 · Answer 3 · answered Sep 10 '18 at 12:15

0

This is also working.

db.OrderDetail.Where(c => c.OrderDetailId == OrderDetailId).ToList()
.ForEach(db.OrderDetail.DeleteOnSubmit);

db.SubmitChanges();`

answered Sep 10 '18 at 12:15

Pradip Rupareliya

545
1
6
18

Delete records from multiple tables with foreign key constraint

3 Answers3