How to Cascade Delete an Adjacency List Model in SQL in C#

Introduction

Out of the box, it's impossible to cascade delete an item hierarchy that's maintained via an adjacency list model in SQL. For example, when you have a single table that maintains item hierarchy by way of a ParentId that points to another record in that same table, SQL will not allow cascade deletions. I wrote some server side code in C# that can be used as a helper class that will perform the cascade deletes.

Background

Adjacency List Model: When item hierarchy is maintained by a single table that has a form of a ParentId column that points back to the primary key of a record in the same table.

Using the Code

In this example, I will be using an MVC4 project as the front end using Web API. The method that actually does the cascading can be extended to really any project you want, provided you stay within the bounds of the method signature.

For starters, here is the Delete method of my Web API. The method to note here is "GetChildrenIds()". This is the method that's important - this takes in a list of ints that represent the immediate children of the selected node. You get the immediate children by simply doing a LINQ call against the database, as shown below:

// DELETE api/region/5
public HttpResponseMessage Delete(int id)
{
     Region region = db.Regions.Find(id);

     List<int> tempRegionIds = new List<int>();
     List<int> immediateChildrenIds = (from i in db.Regions 
     	where i.ParentId == id select i.Id).ToList();
     List<int> regionsToDeleteIds = new List<int>();

     // the below is needed because we need to add the Id of the 
     // clicked on node to make sure it gets deleted as well
     regionsToDeleteIds.Add(region.Id);

     // we can't make this a static method because that would require static member
     // variables, and static member variables persist throughout each recursion
     HelperClasses.HandleChildren.Children GetChildren = 
     			new HelperClasses.HandleChildren.Children();

     // see below this code block for the GetChildrenIds(...) method
     tempRegionIds = GetChildren.GetChildrenIds(immediateChildrenIds);

     // here, we're just adding to regionsToDeleteIds the children of the traversed parent
     foreach (int tempRegionId in tempRegionIds)
     {
         regionsToDeleteIds.Add(tempRegionId);
     }

     // reverse the order so it goes youngest to oldest (child to grandparent)
     // is it necessary?  I don't know honestly.  I just wanted to make sure that
     // the lowest level child got deleted first (the one that didn't have any children)
     regionsToDeleteIds.Reverse(0, regionsToDeleteIds.Count);

     if (regionsToDeleteIds == null)
     {
         return Request.CreateResponse(HttpStatusCode.NotFound);
     }

     foreach (int regionId in regionsToDeleteIds)
     {
         // here we're finding the object based on the passed in Id and deleting it
         Region deleteRegion = db.Regions.Find(regionId);
         db.Regions.Remove(deleteRegion);
     }

     ... 

And finally, here's the GetChildrenIds method: GetChildrenIds method. An important thing to note is you don't want to return a list of objects (the model) to delete - instead, you want to return a list of ints that represent the IDs of the objects to be deleted. This is because if you return a list of objects, that means you would have had to instantiate a DbContext in this helper class. Then, when you tried to take these objects and delete them in your calling class, you wouldn't be able to because these objects were retrieved under a different context.

public class Children
{
    private Entities _db = new Entities(HelperClasses.DBHelper.GetConnectionString());
    private List<int> _childrenIds = new List<int>();
    private List<int> _childRegionIds = new List<int>();

    public List<int> GetChildrenIds(List<int> immediateChildrenIds)
    {
        // traverse the immediate children
        foreach (var i in immediateChildrenIds)
        {
            _childRegionIds.Add(i);
            _childrenIds = (from child in _db.Regions 
            	where child.ParentId == i select child.Id).ToList();
            if (_childrenIds.Any())
                GetChildrenIds(_childrenIds);
            else
                continue;
        }

        return _childRegionIds;
    }
}