Forcing a cascade delete with Entity Framework

Question

I have a table which I need to delete from, but it has many foreign keys referencing it. I've never before used ON DELETE CASCADE - we have a kind of rule to soft delete records, or if a hard delete is absolutely required, to manually clean up any linked tables before/after deletion.

Is there a way, or a helper function somewhere, that will take a particular table (and record(s) in that table), iterate through every table/record in the database that is linked to it, and perform deletes. I want to believe for now that this isn't TOO bad a practice.

So, e.g. schema:

Organisations (OrganisationID, Name)

Users (UserID, FirstName, LastName, OrganisationID)
Projects (ProjectID, Name, OrganisationID)
Organisations_Markets (OrganisationID, MarketID)

Tasks (TaskID, Description, UserID)

If I wanted to delete where OrganisationID=3...

I know there's a way to list all the FKs in SQL - EXEC sp_fkeys 'Organisations'. How could I do this in EF, and do it for any nested FKs. So in the example above, the first deletes will be on the Tasks table (any records that are linked to Users where OrganisationID=3), and so on and so on.

edit: performance is not an issue - there'll never be more than 100 rows in total - so I'm happy to use EF for delete

Answer 1

In your model, it seems there are standard one-to-many relationships between Organization and Users and between Organization and Projects.

So every Organization has zero or more Users; every User belongs to exactly one Organization

Similarly every Organization has zero or more 'Projects, everyProjectbelongs to exactly oneOrganization`.

You also mention Organisation_Markets. You don't specify this, but it looks like a junction table for a many-to-many relation between Organization and Market: Every Organization refers to zero or more Markets; every Market refers to zero or more Organizations.

If you'd set-up the one-to-many and many-to-many relationships following the entity framework coding conventions you could safely remove an Organization.

Entity framework will automatically remove all Users and Projects from this Organization. All Markets that refer to this Organization won't refer to the deleted Organization anymore. But as it is allowed for a Market to refer to no Organization at all, Markets without Organizations won't be removed.

class Organization
{
    public int Id {get; set;}    // Primary key
    // an Organization has zero or more Users (one-to-many):
    public virtual ICollection<User> Users {get; set;}
    // an Organization has zero or more Projects (one-to-many):
    public virtual ICollection<Project> Projects {get; set;

    // an Organization refers to zero or more Markets (many-to-many)
    public virtual ICollection<Market> Markets {get; set;}
}

class User
{
    public int Id {get; set;}    // Primary key
    // every User belongs to one Organization using foreign key
    public int OrganizationId {get; set;}
    public Organization Organization {get; set;}
}

class Project
{
    public int Id {get; set;}    // Primary key
    // every Project belongs to one Organization using foreign key
    public int OrganizationId {get; set;}
    public Organization Organization {get; set;}
}

class Market
{
    public int Id {get; set;}    // Primary key
    // every Market refers to zero or more Organizations (many-to-many)
    public virtual ICollection<Organization> Organizations {get; set;}
}

class MyDbContext : DbContext
{
    public DbSet<Organization> Organizations {get; set;}
    public DbSet<User> Users{get; set;}
    public DbSet<Project> Projects{get; set;}
    public DbSet<Market> Markets{get; set;}
}

That's all. Because I followed the entity framework coding conventions, entity framework will recognize the one-to-many and many-to-many relationships and create the proper foreign keys. It will even create the junction table needed for the many-to-many relation, even though I didn't mention it.

By the way, I don't need the junction table. If I want all Markets belonging to an Organization I use property Organization.Markets. Entity framework will know that a join with the junction table is needed and will create the proper SQL code for it.

If you don't want the defaults, for instance different table names, consider using fluent-API or data annotations for this.

Now to remove an Organization do something like this:

using (var dbContext = new MyDbContext())
{
    Organization organizationToRemove = dbContext.Organizations
        .Where(organization => ...)
        ... etc.
    dbContext.Organizations.Remove(organizationToRemove);
    dbContext.SaveChanges();
}

Entity framework will automatically update other tables

• All Users of the Organization are removed
• All Projects of the Organization are removed
• All references to the Organization in all Markets are removed.

So you don't need a special function to neatly clean up all references to the Organization you removed. Entity Framework will do this correctly for you.