Hi all,

I'm a PHP programmer and I'm just now getting into C#. Here's the problem I'm having:

I have a table, 'categories', with 'catID', 'parentID' and 'name' fields. CatID is the primary key, and parentID is either 0 (no parent, top level) or an existing CatID.

With this setup, Categories can be nested infinitely deep, as many levels down as the user wishes.

In PHP, I would have just set up parentID as a foreign key referencing the CatID on delete cascade; however I've found that in C#, I'm not allowed to set up a foreign key constraint that self-references a column in the same table (lame!).

Does anyone know of a good way I could loop down through my table, as many levels and needed, in order to remove all children? For example if I delete a top-level category, I want to make sure all it's subcats are fully deleted, but if I'm already down 4 levels, I only want to delete the children of that current level. Make sense?

Oh and I'm using a local database (.sdf) not service-based db...

Any help appreciated!

In PHP, I would have just set up parentID as a foreign key referencing the CatID on delete cascade; however I've found that in C#, I'm not allowed to set up a foreign key constraint that self-references a column in the same table (lame!).

This doesn't make any sense. Constraints aren't specified in C#. What database are you using?

Edit: oh, I see. More reply pending.

Edit 2: One option: write a function that does what you say, deleting the children first.

void DeleteCategoryTree(int catID) {
    int[] children = GetChildIDs(catID);
    foreach(int child in children)
        DeleteCategoryTree(child);
    DeleteCategoryRow(catID);
}

Edit 3: And if you think there can be children so deep as to cause a stack overflow, use a Stack.

void DeleteCategoryTree(int catID) {
    var stack = new Stack<int>();
    var deletionStack = new Stack<int>();
    stack.Push(catID);
    while (stack.Count != 0) {
        int id = stack.Pop();
        deletionStack.Push(id);
        foreach (int child in GetChildIDs(id));
            stack.Push(child);
    }
    while (deletionStack.Count != 0) {
        DeleteCategoryRow(deletionStack.Pop());
    }
}

But what if that child has children, and it's children have children? See my problem?

But what if that child has children, and it's children have children? See my problem?

Read my function (now functions) again, you'll see that it deletes the children.

This question has already been answered. Start a new discussion instead.