Question

使用 ObjectContext 。我想通过执行系统传递 SQL 查询, 因为我不想只为了在事后删除而检索所有相关实体。

类别表如下:

CatID | CatName | ParentID

CatID 是父式ID FK 的主密钥

I am wishing to delete a category and also all those that are under it. Can be 2+ levels deep of sub cats, so different ParentID s

Thought I could do it as below and just set "cascade" on delete option for the foreign key in the database, but it won t let me and it does not appear to want to cascade delete down by using the CatID - ParentID relationship and the query gets stopped by this very FK constraint.

public RedirectToRouteResult DelCat(int CatID)
{
    if (CatID != 0)
    {
        _db.ExecuteStoreCommand("DELETE FROM Categories WHERE CatID={0}", CatID);
        _db.SaveChanges();
    }

    return RedirectToAction("CatManage");
}

Answer 1

递归的 CTE 全部目录生成等级中所有类别列表。显然, 删除部分, 全部删除。

; with allCategories as (
 select CatID
   from Categories
  where CatID = @CatID_to_delete
  union all
 select Categories.CatID
   from allCategories
  inner join Categories 
     on allCategories.CatID = Categories.ParentID
)
delete Categories
  from Categories 
 inner join allCategories
    on Categories.CatID = allCategories.CatID

尝试从所有目录 < / code> 中 < code> 选择 * * 来先检查。

有" "http://sqlfiddle.com/#! 3/f883f/ 1, rel="no follow" >TEST@ Sql Fiddle 。

Answer 2

为什么不在你的批量里发两份声明呢?

DELETE Categories WHERE ParentID = {0};
DELETE Categories WHERE CatID = {0};

如果您使用“ 不愿允许” 的框架, 那么就做对了: 将逻辑输入存储程序, 并调用存储程序。

友情链接