How to delete child entities before parent with Entity Framework CF?

Question

I am trying to use EF code-first to delete a db record (deleteMe) and it's children (deleteMe.Prices).

foreach (var deleteMe in deleteThese)
{ 
   // Delete validation
   if(CanDeleteItem(deleteMe.ItemId))
   {
      db.Entry(deleteMe).State = EntityState.Deleted;

      foreach (var item in deleteMe.Prices)
      {
         db.Entry(item).State = EntityState.Deleted; // cascade delete
      }
   }
}
db.SaveChanges();

However, Entity Framework seems to be unable to track the fact that the child records should be deleted before the parent. I get the error:

The DELETE statement conflicted with the REFERENCE constraint "ItemPrice_Item".
The conflict occurred in database "DEVDB", table "dbo.ItemPrices", column 'Item_ItemId'.
The statement has been terminated.

How would I execute this delete in EF?

Answer 1

I ended up finding a quick line that'd do it for me:

foreach (var deleteMe in deleteThese)
{ 
   // Delete validation
   if(CanDeleteItem(deleteMe.ItemId))
   {
      ///
      deleteMe.Prices.ToList().ForEach(p => db.ItemPrices.Remove(p));
      ///

      db.Entry(deleteMe).State = EntityState.Deleted;
   }
}
db.SaveChanges();

Answer 2

EF6

context.Children.RemoveRange(parent.Children)

Answer 3

Cascade delete in EF is dependent on cascade delete configured in relation in the database so if you don't have cascade delete configured in the database you must first load all item prices to your application and mark them as deleted.

Answer 4

Well the most easiest solution would be to iterate through prices first and call save changes, then set the entry to delete for deleteMe and call save changes again, but have you checked out this: Entity framework code first delete with cascade? It seems to be what you want.

Curious though also why you just aren't removing the entities from the context to delete but instead setting the entry state?

Another option is to set cascade delete http://blogs.msdn.com/b/alexj/archive/2009/08/19/tip-33-how-cascade-delete-really-works-in-ef.aspx

Do something like this (not tested but hopefully you get the jist):

using (TransactionScope scope = new TransactionScope())
{    
    foreach (var deleteMe in deleteThese)
    { 
   // Delete validation
      if(CanDeleteItem(deleteMe.ItemId))
      {

         foreach (var item in deleteMe.Prices)
         {
            db.Entry(item).State = EntityState.Deleted; // cascade delete
         }
         db.SaveChanges();

         db.Entry(deleteMe).State = EntityState.Deleted;


     }
   }
   db.SaveChanges();
   scope.Complete();
}     

Additionally you could call:

db.Prices.Remove(item);

and

db.DeleteMes.Remove(deleteMe);

instead of setting the entry state. Not sure if there is a difference behind the scenes between the two though.

Answer 5

Cascade delete in Entity framework is tricky thing, as you need to be sure about deletion entity object graph.It is better to always write a integration test for these cascade deletes.

If you try to delete parent entity in EF, it will try to execute delete statements for any child entities in current dbcontext. As a result, it will not initialize any child entities which have not been loaded. This will lead to RDBMS runtime error which violate the foreign key constraint. To be in safe side ensure all dependent entities loaded to current dbcontext before deleting.

Answer 6

The following works quite efficiently. For each relational table in your database add the following (At your context file).

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder
            .Entity<TableA>()
            .HasMany(x => x.TableB)
            .WithRequired(x => x.TableA)
            .WillCascadeOnDelete();

        modelBuilder
            .Entity<TableC>()
            .HasMany(x => x.TableD)
            .WithRequired(x => x.TableC)
            .WillCascadeOnDelete();

        modelBuilder
            .Entity<TableE>()
            .HasMany(x => x.TableF)
            .WithRequired(x => x.TableE)
            .WillCascadeOnDelete(); }

Then in your code, don't forget to load these tables, before you delete

            context.TableA.Load();
            context.TableB.Load();
            context.TableC.Load();
            context.TableD.Load();
            context.TableE.Load();
            context.TableF.Load();

            var tableAEntity= TableA.Where(x => x.Condition == [yourcondition].FirstOrDefault(); 

            context.TableA.Remove(tableAEntity);
            context.SaveChanges();

This will delete the entity (record) from the main entry table and all the connected table records (related through FK) quite fast and efficiently (Even if the relationship cascades deeply at multiple levels).

Answer 7

If your object is self-referencing, you can delete both many-to-many and one-to-many children using the method below. Just remember to call db.SaveChanges() afterwards :)

[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(int id)
{
    Object obj = this.db.Objects.Find(id);
    this.DeleteObjectAndChildren(obj);
    this.db.Objects.Remove(obj);
    this.db.SaveChanges();
    return this.Json(new { success = true });
}

/// <summary>
/// This deletes an object and all children, but does not commit changes to the db.
///  - MH @ 2016/08/15 14:42
/// </summary>
/// <param name="parent">
/// The object.
/// </param>
private void DeleteObjectAndChildren(Object parent)
{
    // Deletes One-to-Many Children
    if (parent.Things != null && parent.Things.Count > 0)
    {
        this.db.Things.RemoveRange(parent.Things);
    }

    // Deletes Self Referenced Children
    if (parent.Children != null && parent.Children.Count > 0)
    {
        foreach (var child in parent.Children)
        {
            this.DeleteObjectAndChildren(child);
        }

        this.db.Objects.RemoveRange(parent.Children);
    }
}

Answer 8

I had a similar issue and for me, it looked like I hadn't correctly established the relationship between Parent and Child in their respective classes.

My fix was to add the attributes specified below to the Child class, for the property that represented its Parent's Id

    public class Child
    {
        [Key, Column(Order = 1)]
        public string Id { get; set; }

        [Key, ForeignKey("Parent"), Column(Order = 2)]  // adding this line fixed things for me
        public string ParentId {get; set;}
    }

    public class Parent
    {
        [Key, Column(Order = 1)]
        public string Id { get; set; }

        ...

        public virtual ICollection<Child> Children{ get; set; }
    }

Answer 9

_context.Remove(parent);
_context.RemoveRange(_context.Childrens
  .Where(p => parent.Childrens
  .Select(c => c.Id).Contains(p.Id)));