SQL query to delete a comment and all its children in MySQL and Entity Framework

advertisements

I have a comments table.

  • id
  • section_id
  • parent_comment_id
  • root_id
  • content

It supports multi-level commenting.

If the comment is at the top most position, it's root_id is NULL and it's parent_comment_id is NULL.

If the comment is underneath another comment: the parent_comment_id equals to the comment Id of the comment which is just one level underneath it. The root_id for all child comments will be the Id of the top most comment in which that comment is underneath it.

Example:

[id=1] [parent_comment_id=null] [root_id=null]
  [id=2] [parent_comment_id=1] [root_id=1]
  [id=3] [parent_comment_id=1] [root_id=1]
    [id=4] [parent_comment_id=3] [root_id=1]
      [id=5] [parent_comment_id=4] [root_id=1]
    [id=12] [parent_comment_id=3] [root_id=1]
  [id=6] [parent_comment_id=1] [root_id=1]

[id=2] [parent_comment_id=null] [root_id=null]

I have no control over the database Schema. So I can't make changes. There is also no foreign key constraint set up in the table for any of these columns.

What I am having problem with is to create a function, probably a recursive function, that gets a comment Id and deletes that comment and all its childs, childs in all levels. This can be a comment anywhere in the tree.

Here is something that I am trying now:

 protected void DeleteChildComments(comment c)
 {
      // get all comments with c.id as its parent_id
      List<comment> oneLevelDownSubComments = new List<comment>();

      using (phEntities db = new phEntities())
       {
           oneLevelDownSubComments = db.comments.Where(x => x.parent_comment_id == c.id).ToList<comment>();    

       }

        if (oneLevelDownSubComments.Count == 0)
        {
            // no children, just delete the comment
            using (phEntities db = new (phEntities())
            {
                db.comments.Remove(c);
                db.SaveChanges();
            }
        }
        else
        {
            // has children
            foreach(var item in oneLevelDownSubComments)
            {
                DeleteChildComments(item);
            }
        }

    }

I am developing in ASP.NET 4.5 C# and Entity Framework against a MySQL 5 Database.


OK, I finally made it, here is the code:

  protected void DeleteChildComments(comment c, phEntities db) {
   if (c != null) {
    // get all comments with c.id as its parent_id
    List <comment> oneLevelDownSubComments = new List <comment> ();

    oneLevelDownSubComments =
        db.comments.Where(x => x.parent_comment_id == c.id).ToList <comment> ();

    if (oneLevelDownSubComments.Count == 0) {
     // no children, just delete the comment

     db.comments.Remove(c);
     db.SaveChanges();
    } else {
     // has children, delete them
     foreach(var item in oneLevelDownSubComments) {
      DeleteChildComments(item, db);
     }

     // delete itself if has no children
     DeleteChildComments(c, db);
    }
   }
  }