Deleting principal and dependents for independent association (one-to-many)

Question

Using Entity Framework 6 with the Designer, all our entities have an independent association. So no foreign keys are defined in our model's properties.

Having the following entities with a one-to-many relationship:

public class Parent
{
    public Guid Id { get; set; }
    public virtual ICollection<Child> Children { get; set; }
}
public class Child
{
    public Guid Id { get; set; }
    public virtual Parent Parent { get; set; }
}

Mapping to the following SQL tables:

CREATE TABLE Parent (
    Id uniqueidentifier NOT NULL,
    CONSTRAINT PK_Parent PRIMARY KEY CLUSTERED (Id)
)

CREATE TABLE Child (
    Id uniqueidentifier NOT NULL,
    FkParent uniqueidentifier NOT NULL,
    CONSTRAINT PK_Child PRIMARY KEY CLUSTERED (Id)
)

ALTER TABLE Child WITH CHECK
ADD CONSTRAINT FK_Child_Parent FOREIGN KEY(FkParent) REFERENCES Parent ([Id])

ALTER TABLE Child CHECK CONSTRAINT FK_Child_Parent

As you can see we are not using CASCADE ON DELETE, because my team is against the usage of it.

So the question is, what's the correct way to delete a parent and it's dependent children given only the parent's Id?
With delete, I mean that the SQL records in both tables should be deleted (resulting in a DELETE SQL statement) after calling .SaveChanges().

It sounds like you are looking for a database [trigger](http://www.tutorialspoint.com/plsql/plsql_triggers.htm) on the delete command on the but I dislike 'hiding' functionality in triggers and that would be just like the Cascade on delete functionality. Maybe just build it into the business logic of your (repository?) delete handler. — Jared Stroebele, Sep 07 '16 at 15:41
@JaredStroeb, exactly. Me and my team also don't like these kinds of database functionalities which ultimately hides them from business logic in the application. However, this discussion has a tendency towards design choices. — QuantumHive, Sep 07 '16 at 15:47

score 0 · Accepted Answer · edited May 23 '17 at 10:30

So the only way I figured out how to achieve this, is by explicitly loading the children into memory and deleting them one by one before actually deleting the parent.

using (var context = new MyDbContext())
{
    var parent = context.Parents.Find(parentId);
    //or use LINQ if you prefer:
    var alternative = context.Parents.Single(p => p.Id == parentId)

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

    context.Parents.Remove(parent);

    context.SaveChanges();
}

Note the .ToList() (or .ToArray() if you prefer) on the Children which is important, as explained in this answer.

Performance-wise this is not ideal, because you have to query every child record from the database and load this into memory (I suppose for a small application, this wouldn't matter though). But I couldn't find a better approach without using CASCADE ON DELETE, even though I'm not sure if that would really outperform this case.

Deleting principal and dependents for independent association (one-to-many)

1 Answers1