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.
ParentId
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.
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:
GetChildrenIds()
int
// 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.
GetChildrenIds
DbContext
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 _childRegion.