DELETE语句冲突与实体框架相同的表引用约束
我有一个自我参照，其中的ParentId是FK的ID（PK）的表。
使用EF（code-第一），我已经设置了我的关系如下：
I have a table with a self reference where the ParentId is an FK to the ID (PK).
Using EF (code-first), I've set up my relationship as follows:
this.HasOptional(t => t.ParentValue)
.WithMany(t => t.ChildValues)
.HasForeignKey(t => t.ParentId);
当我尝试删除该儿童及其父，该删除命令EF提交到数据库不在我期望他们去的顺序 - 它会尝试先删除父记录。
When I try to delete the children and its parent, the DELETE commands EF issues to the database are not in the order I expected them to go - it attempts to delete the parent record first.
我知道我有几个选择（两者都不喜欢）：
I realize that I have a couple of options here (neither of which I like):
- 删除子记录第一，做一个完整的保存/提交，然后删除父记录。随着我的模型，并且保持它的逻辑的复杂性，这是不是一种选择 - 不能发出多个commit命令时，我想 。
- 在溶解删除任何之前的关系。这似乎是一个更明智的解决方案，但是，我必须发出单独提交的DELETE操作之前UPDATE语句。我想避免多个保存/提交调用。
- 使用触发器来之前删除父记录删除儿童。但我想，以避免触发和它们的性质问题尽可能。
所以，问题是......是有办法强制删除儿童的父记录过吗？也许我缺少某种告诉EF的明确办法，它需要照顾这些孩子的父母过吗？也许有一个办法直接EF在ID的递减顺序删除吗？我不知道..想法？
So the question is.. is there a way to enforce the deletion of children before the parent record? Perhaps I'm missing some sort of an explicit way of telling EF that it needs to take care of these children before the parent? Maybe there's a way to direct EF to delete in a descending order of IDs? I don't know.. thoughts?
我知道答案是一岁，但我觉得不完整。在我的印象中，自引用表是用来重新present任意深度。
I realize the answer is a year old, but I find it incomplete. In my mind, a self-referencing table is used to represent an arbitrary depth.
例如，请考虑以下结构：
For example, consider the following structure:
/*
* earth
* europe
* germany
* ireland
* belfast
* dublin
* south america
* brazil
* rio de janeiro
* chile
* argentina
*
*/
答案没有解决如何删除土，还是欧洲，从上面的结构。
The answer does not solve how to delete earth, or europe, from the structure above.
我提出以下几点code作为一种替代（由Slauma提供的答案进行修改，谁做的很好的BTW）。
I submit the following code as an alternative (modification of answer provided by Slauma, who did a good job btw).
在MyContext类，添加以下方法：
In the MyContext class, add the following methods:
public void DeleteMyEntity(MyEntity entity)
{
var target = MyEntities
.Include(x => x.Children)
.FirstOrDefault(x => x.Id == entity.Id);
RecursiveDelete(target);
SaveChanges();
}
private void RecursiveDelete(MyEntity parent)
{
if (parent.Children != null)
{
var children = MyEntities
.Include(x => x.Children)
.Where(x => x.ParentId == parent.Id);
foreach (var child in children)
{
RecursiveDelete(child);
}
}
MyEntities.Remove(parent);
}
我填充用code-先用下面的类中的数据：
I populate the data using code-first with the following class:
public class TestObjectGraph
{
public MyEntity RootEntity()
{
var root = new MyEntity
{
Name = "Earth",
Children =
new List<MyEntity>
{
new MyEntity
{
Name = "Europe",
Children =
new List<MyEntity>
{
new MyEntity {Name = "Germany"},
new MyEntity
{
Name = "Ireland",
Children =
new List<MyEntity>
{
new MyEntity {Name = "Dublin"},
new MyEntity {Name = "Belfast"}
}
}
}
},
new MyEntity
{
Name = "South America",
Children =
new List<MyEntity>
{
new MyEntity
{
Name = "Brazil",
Children = new List<MyEntity>
{
new MyEntity {Name = "Rio de Janeiro"}
}
},
new MyEntity {Name = "Chile"},
new MyEntity {Name = "Argentina"}
}
}
}
};
return root;
}
}
这是我保存到我用下面的code数据库：
which I save to my database with the following code:
ctx.MyEntities.Add(new TestObjectGraph().RootEntity());
然后调用删除像这样：
then invoke the deletes like so:
using (var ctx = new MyContext())
{
var parent = ctx.MyEntities
.Include(e => e.Children)
.FirstOrDefault();
var deleteme = parent.Children.First();
ctx.DeleteMyEntity(deleteme);
}
这导致我的数据库现在有像这样的结构：
which results in my database now having a structure like so:
/*
* earth
* south america
* brazil
* rio de janeiro
* chile
* argentina
*
*/
，其中欧洲和所有的孩子都被删除。
where europe and all of its children are deleted.
在上面，我指定根节点的第一个孩子，证明，用我的code可以从递归层次结构中的任何位置删除一个节点及其所有儿童。
in the above, I am specifying the first child of the root node, to demonstrate that using my code you can recursively delete a node and all of its children from anywhere in the hierarchy.
如果你想测试删除everyting，你可以简单地修改这样的行：
if you want to test deleting everyting, you can simply modify the line like this:
ctx.DeleteMyEntity(parent);
或任何节点，要在树上。
or whichever node you want in the tree.
显然，我不会得到赏金，但我希望我的帖子会帮助别人寻找一个解决方案，适用于任意深度的自我参照实体。
obviously, I won't get the bounty, but hopefully my post will help someone looking for a solution that works for self-referencing entities of arbitrary depth.
下面是完整的源代码，这是Slauma的code从所选答案修改后的版本：
Here is the full source, which is a modified version of Slauma's code from the selected answer:
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
namespace EFSelfReference
{
public class MyEntity
{
public int Id { get; set; }
public string Name { get; set; }
public int? ParentId { get; set; }
public MyEntity Parent { get; set; }
public ICollection<MyEntity> Children { get; set; }
}
public class MyContext : DbContext
{
public DbSet<MyEntity> MyEntities { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<MyEntity>()
.HasOptional(e => e.Parent)
.WithMany(e => e.Children)
.HasForeignKey(e => e.ParentId);
}
public void DeleteMyEntity(MyEntity entity)
{
var target = MyEntities
.Include(x => x.Children)
.FirstOrDefault(x => x.Id == entity.Id);
RecursiveDelete(target);
SaveChanges();
}
private void RecursiveDelete(MyEntity parent)
{
if (parent.Children != null)
{
var children = MyEntities
.Include(x => x.Children)
.Where(x => x.ParentId == parent.Id);
foreach (var child in children)
{
RecursiveDelete(child);
}
}
MyEntities.Remove(parent);
}
}
public class TestObjectGraph
{
public MyEntity RootEntity()
{
var root = new MyEntity
{
Name = "Earth",
Children =
new List<MyEntity>
{
new MyEntity
{
Name = "Europe",
Children =
new List<MyEntity>
{
new MyEntity {Name = "Germany"},
new MyEntity
{
Name = "Ireland",
Children =
new List<MyEntity>
{
new MyEntity {Name = "Dublin"},
new MyEntity {Name = "Belfast"}
}
}
}
},
new MyEntity
{
Name = "South America",
Children =
new List<MyEntity>
{
new MyEntity
{
Name = "Brazil",
Children = new List<MyEntity>
{
new MyEntity {Name = "Rio de Janeiro"}
}
},
new MyEntity {Name = "Chile"},
new MyEntity {Name = "Argentina"}
}
}
}
};
return root;
}
}
class Program
{
static void Main(string[] args)
{
Database.SetInitializer<MyContext>(
new DropCreateDatabaseAlways<MyContext>());
using (var ctx = new MyContext())
{
ctx.Database.Initialize(false);
ctx.MyEntities.Add(new TestObjectGraph().RootEntity());
ctx.SaveChanges();
}
using (var ctx = new MyContext())
{
var parent = ctx.MyEntities
.Include(e => e.Children)
.FirstOrDefault();
var deleteme = parent.Children.First();
ctx.DeleteMyEntity(deleteme);
}
Console.WriteLine("Completed....");
Console.WriteLine("Press any key to exit");
Console.ReadKey();
}
}
}