Entity Framework self referencing entity deletion.
- by Viktor
Hello. I have a structure of folders like this:
Folder1
Folder1.1
Folder1.2
Folder2
Folder2.1
Folder2.1.1
and so on..
The question is how to cascade delete them(i.e. when remove folder2 all children are also deleted). I can't set an ON DELETE action because MSSQL does not allow it. Can you give some suggesions?
UPDATE: I wrote this stored proc, can I just leave it or it needs some modifications?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE sp_DeleteFoldersRecursive
@parent_folder_id int
AS
BEGIN
SET NOCOUNT ON;
IF @parent_folder_id = 0 RETURN;
CREATE TABLE #temp(fid INT );
DECLARE @Count INT;
INSERT INTO #temp(fid)
SELECT FolderId FROM Folders WHERE FolderId = @parent_folder_id;
SET @Count = @@ROWCOUNT;
WHILE @Count > 0
BEGIN
INSERT INTO #temp(fid)
SELECT FolderId FROM Folders WHERE EXISTS
(SELECT FolderId FROM #temp
WHERE Folders.ParentId = #temp.fid)
AND NOT EXISTS
(SELECT FolderId FROM #temp
WHERE Folders.FolderId = #temp.fid);
SET @Count = @@ROWCOUNT;
END
DELETE Folders FROM Folders INNER JOIN #temp ON Folders.FolderId = #temp.fid;
DROP TABLE #temp;
END
GO
