locked
Best way to delete records from Child and Parent tables RRS feed

  • Question

  • User-273049309 posted

    Hi,

    I am purging table data which is some 'n' years old. So currently I have parent child tables like below. 

    Table 4 --> Table 3 --> Table 2 --> Table 1

    Here Table 1 has Primary Key and I need to get this for 'n' years old and this Primary Key needs to be deleted from Table 4 --> Table 3 --> Table 2 --> Table 1 in this order.

    Since it has foreign key constraints. What would be best way to delete the records in the above order? Can we maintain some transaction blocks in c#.net? In case if any delete fails then it can rollback it? I know we can achieve this commit and rollback through Stored Procs but can we write such in c#.net. Please help.

    Tuesday, March 8, 2016 4:42 PM

Answers

  • User-821857111 posted

    Let's say you have customers who have orders that have items, and you want to delete some customers. You also need to delete their orders and therefore the order items. The code you would use would look something like this:

    var customers = db.Customers.Where(c => c.NeedsToBeDeleted);
    foreach(var orderItem in customers.Orders.Select(o => o.Items))
    {
        db.Entry(orderItem).State = EntityState.Deleted;
    }
    foreach(var order in customers.Orders)
    {
        db.Entry(order).State = EntityState.Deleted;
    }
    foreach(var customer in customers)
    {
        db.Entry(customer).State = EntityState.Deleted;
    }
    db.SaveChanges();



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 10, 2016 7:58 AM

All replies

  • User-273049309 posted

    Also can we capture the number of rows deleted from the each table? Like we use @@RowCount in Stored Proc.

    Tuesday, March 8, 2016 4:55 PM
  • User-273049309 posted

    Can someone please help on this on how to achieve this using Entity Framework?

    Tuesday, March 8, 2016 9:30 PM
  • User-986267747 posted

    Hi praveen251985,

    Can someone please help on this on how to achieve this using Entity Framework?

    According to your description, it seems that it’s a cascade delete. You could refer to following links to get more detailed information about cascade delete in entity framework and try using it to achieve your requirement.

    https://msdn.microsoft.com/en-us/data/jj591620.aspx#CascadeDelete

    http://stackoverflow.com/questions/17487577/entity-framework-ef-code-first-cascade-delete-for-one-to-zero-or-one-relations

    http://stackoverflow.com/questions/21314113/entity-framework-6-code-first-cascade-delete

    Best regards,

    Klein Zhang

    Wednesday, March 9, 2016 6:52 AM
  • User-821857111 posted

    If you haven't can't/set up cascade delete in your database, you can just go through the child collections and get counts of them before removing them, then count and remove the parents.  

    Wednesday, March 9, 2016 7:39 AM
  • User-273049309 posted

    Hi Mike,

    Could you please provide some pointers on Deleting without Cascade Delete. I am using Entity Framework. Thanks for your help.

    Wednesday, March 9, 2016 7:55 PM
  • User-821857111 posted

    Let's say you have customers who have orders that have items, and you want to delete some customers. You also need to delete their orders and therefore the order items. The code you would use would look something like this:

    var customers = db.Customers.Where(c => c.NeedsToBeDeleted);
    foreach(var orderItem in customers.Orders.Select(o => o.Items))
    {
        db.Entry(orderItem).State = EntityState.Deleted;
    }
    foreach(var order in customers.Orders)
    {
        db.Entry(order).State = EntityState.Deleted;
    }
    foreach(var customer in customers)
    {
        db.Entry(customer).State = EntityState.Deleted;
    }
    db.SaveChanges();



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 10, 2016 7:58 AM
  • User438705957 posted

    Hi Pravee,

    Please see my post on this topic at https://forums.asp.net/t/2090608.aspx?Code+First+and+Foreign+Keys

    By default, Code First will set cascadeDelete:True  on the foreign key when creating the migration.
    This will create the foreign key in the database with 'On DELETE CASCADE' set, which will delete all child records when a parent record is deleted.

    Please see the post for further musings on the topic.

    Thursday, April 7, 2016 1:31 AM