Click here to Skip to main content
Click here to Skip to main content
Go to top

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

, 14 Oct 2013
Rate this:
Please Sign up or sign in to vote.
Demonstrates how to cascade delete on a single table that maintains hierarchy by a ParentId that points to the primary key of the same table

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;
    }
}

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

marks-mike

United States United States
Mike is a web developer for a leading oil and gas company in Oklahoma. He's been writing .NET apps for 4 years and has expertise in ASP.NET with MVC.

Comments and Discussions

 
GeneralMy vote of 4 PinmemberJasonMacD15-Oct-13 11:04 
interesting

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 14 Oct 2013
Article Copyright 2013 by marks-mike
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid