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.
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:
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>();
HelperClasses.HandleChildren.Children GetChildren =
tempRegionIds = GetChildren.GetChildrenIds(immediateChildrenIds);
foreach (int tempRegionId in tempRegionIds)
if (regionsToDeleteIds == null)
foreach (int regionId in regionsToDeleteIds)
Region deleteRegion = db.Regions.Find(regionId);
And finally, here's the
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)
foreach (var i in immediateChildrenIds)
_childrenIds = (from child in _db.Regions
where child.ParentId == i select child.Id).ToList();