How to remove child one to many related records in EF code first database?

Question

Well, I have one-to-many related model:

public class Parent
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Child> Children { get; set; }
}

public class Child
{
    public int Id { get; set; }
    public string ChildName { get; set; }
}

What I want to do is clear Parent.Children and remove related child entities from database. I've already tried:

Database context class:

modelBuilder.Entity<Parent>()
            .HasMany(p => p.Children)
            .WithOptional()
            .WillCascadeOnDelete(true);

this works fine, but I still have redundant records in database with Parent_Id = null fields when I do

parent.Children.Clear();
repository.InsertOrUpdate(parent);

in my repository class. Also the same behavior is when I do:

modelBuilder.Entity<Parent>()
            .HasMany(pr => pr.Children)
            .WithOptional(ri => ri.Parent)
            .WillCascadeOnDelete(true);

with additional Parent property in Child class

public class Child
{
    ...
    public Parent Parent { get; set; }
    ...
}

or when I do

modelBuilder.Entity<Child>()
            .HasOptional(p => p.Parent)
            .WithMany(p => p.Children)
            .HasForeignKey(p => p.Parent_Id)
            .WillCascadeOnDelete(true);

with additional Parent_Id property in Child class

public class Child
{
     ...
     public int Parent_Id { get; set; }
     ...
}

So, how can I configure cascade deleting correctly? Or how should I supposed to remove those child entities? I assume this is casual task but I'm just missing something.

Can you post some more code? Maybe the full contents of your `OnModelCreating()`? When I copy-and-paste your entities and your first mapping attempt (and set `Id` as the key property for both entities), deletes are cascading correctly for me. — Jeremy Todd, May 20 '13 at 17:44

score 117 · Answer 1 · answered May 29 '14 at 23:53

117

In EF6 a faster way to do the operation is...

 context.Children.RemoveRange(parent.Children)

answered May 29 '14 at 23:53

Sam Sippe

3,160
3
27
40

1

It is a faster way to code but in terms of performance, is it really faster? If I want to delete all children under parent with `parent.Id == 10`, and my `Child` class has 100 fields and haven't loaded to memory yet, it seems inefficient to use `RemoveRange` because the program will have to load `parent.Children` with all 100 fields by default. – VCD Sep 19 '17 at 09:46
2

@VCD it's faster than calling context.Children.Remove(child) for each child but not as fast as running sql "DELETE FROM Children where parentId=@parentId" – Sam Sippe Sep 19 '17 at 11:14
How would you go about finding the parent, if the only info you have is the child Id? – guyfromfargo Feb 26 '18 at 17:24
@guyfromfargo in EF something like `var child = context.Children.Single(f=>f.Id==childId); var parent = context.Parents.Single(f=>f.Id==child.ParentId);` – Sam Sippe Feb 28 '18 at 01:40

score 72 · Accepted Answer · answered May 20 '13 at 18:28

72

Cascading delete has no effect here because you don't delete the parent but just call InsertOrUpdate. The correct procedure is to delete the children one-by-one, like so for example:

using (var context = new MyContext())
{
    var parent = context.Parents.Include(p => p.Children)
        .SingleOrDefault(p => p.Id == parentId);

    foreach (var child in parent.Children.ToList())
        context.Children.Remove(child);

    context.SaveChanges();
}

answered May 20 '13 at 18:28

Slauma

175,098
59
401
420

Surely with cascade delete on you dont need to delete the children individually? – Kirsten May 24 '13 at 23:37
9

@kirsteng: Cascading delete means that the children are deleted **if** you delete the parent. But in this question no parent is deleted. So, cascading delete does not apply to this scenario. – Slauma May 25 '13 at 00:47
23

instead of foreaching all of the "child" objects, just say `context.Children.RemoveRange(parent.Children.ToArray())` that way the DbContext doesn't have to do as much work checking each time you call Remove. This may not be a big performance problem for deletes, but I've noticed a huge difference when adding items one at a time when I tested adding 100k records using `context.Children.Add(child)` in a for loop. – C. Tewalt May 05 '15 at 18:27
Of course, you want to make sure that sucking all of the children into memory doesn't blow up the memory in your process space. – C. Tewalt May 05 '15 at 18:28
1

cascade delete works in this situation, at least when using EF Core :) – Konrad Aug 05 '18 at 19:20
1

@Konrad Thanks for the hint. In my case, I had to alter DB-schema to have `ON DELETE CASCADE` and generate DB-models again to have `DeleteBehavior.Cascade` in them. – Jari Turkia Jan 16 '19 at 11:39
oh this sucks so much. You mean you suggest to pull all children into the app first and then delete them? Is this what we have come to? – Toolkit Mar 27 '20 at 08:34

Konrad · Answer 3 · 2018-08-05T21:10:58.293

10

This is called "deleting orphans".

Can EF automatically delete data that is orphaned, where the parent is not deleted?

I don't know how it works in EF6 but in EF Core it works fine https://learn.microsoft.com/en-us/ef/core/saving/cascade-delete so you don't necessarily need to delete the parent for cascades to work.

Delete orphans examples

edited Aug 05 '18 at 21:10

answered Aug 05 '18 at 21:05

Konrad

6,385
12
53
96

This should be the accepted answer now as the first link exactly describes how to solve the issue. The OP needs to add the parent Id to the child as a FK and then set up the Identifying Relation using the approach in that link. – tomRedox Aug 02 '21 at 08:48

score 3 · Answer 4 · edited May 23 '17 at 12:18

3

Try changing to

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

because virtual is needed to get lazy loading. as explained here

I think your parent.Children.clear isnt working because the Children have not been loaded

edited May 23 '17 at 12:18

Community

1
1

answered May 20 '13 at 18:20

Kirsten

15,730
41
179
318

1

In my case (ASP.NET core 2 / EF Core), I've tested and this was not the solution – hamid reza Feb 08 '19 at 13:48

Matthew Hudson · Answer 5 · 2016-08-15T13:54:36.760

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

How to remove child one to many related records in EF code first database?

5 Answers5

Linked