C# DELETE 语句与实体框架的 SAME TABLE REFERENCE 约束冲突
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16463773/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
The DELETE statement conflicted with the SAME TABLE REFERENCE constraint with Entity Framework
提问by Kon
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:
我有一个带有自我引用的表，其中 ParentId 是 ID (PK) 的 FK。
使用 EF（代码优先），我建立了如下关系：
this.HasOptional(t => t.ParentValue)
.WithMany(t => t.ChildValues)
.HasForeignKey(t => t.ParentId);
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.
当我尝试删除子项及其父项时，向数据库发送的 DELETE 命令 EF 问题与我预期的顺序不一致 - 它首先尝试删除父记录。
I realize that I have a couple of options here (neither of which I like):
我意识到我在这里有几个选择（我都不喜欢）：
- Delete child records first, do a full save/commit, and then delete parent record. With the complexity of my model and the logic that maintains it, this isn't an option - can't issue multiple commit commands whenever I want.
- Dissolve the relationship before deleting anything. This seems like a more sensible solution, but again, I have to issue separate commit with an UPDATE statement before the DELETEs. I want to avoid multiple save/commit calls.
- Use a trigger to delete children prior to deleting parent record. But I'd like to avoid triggers and their problematic nature as much as possible.
- 先删除子记录，做一个完整的保存/提交，然后删除父记录。鉴于我的模型和维护它的逻辑的复杂性，这不是一个选项 - 不能随时发出多个提交命令。
- 在删除任何内容之前先解除关系。这似乎是一个更明智的解决方案，但同样，我必须在 DELETE 之前使用 UPDATE 语句发出单独的提交。我想避免多次保存/提交调用。
- 在删除父记录之前使用触发器删除子记录。但我想尽可能避免触发因素及其有问题的性质。
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?
所以问题是..有没有办法在父记录之前强制删除子项？也许我缺少某种明确的方式告诉 EF 它需要在父母之前照顾这些孩子？也许有一种方法可以指示 EF 按 ID 的降序删除？我不知道.. 想法？
回答by Slauma
Deleting parent and child like the following does work for me. The children are deleted beforethe parent and it is a single database roundtrip (one call to SaveChanges) with of course three DELETE statements in a single transaction:
像下面这样删除父母和孩子确实对我有用。子项在父项之前被删除，它是单个数据库往返（一次调用SaveChanges），当然在单个事务中包含三个 DELETE 语句：
using (var ctx = new MyContext())
{
var parent = ctx.MyEntities.Include(e => e.Children).FirstOrDefault();
foreach (var child in parent.Children.ToList())
ctx.MyEntities.Remove(child);
ctx.MyEntities.Remove(parent);
ctx.SaveChanges();
}
(Using ToList()is necessary here because calling Removefor the children also removes from the parent's Childrencollection. Without using ToLista runtime exception would be thrown that the collection the foreachloop is iterating over has been modified.)
（ToList()在这里使用是必要的，因为Remove对子项的调用也会从父项的Children集合中删除。如果不使用ToList运行时异常，则会抛出foreach循环迭代的集合已被修改。）
The order in which Removeis called for children and parent doesn't matter. This works as well:
Remove调用 children 和 parent的顺序无关紧要。这也有效：
using (var ctx = new MyContext())
{
var parent = ctx.MyEntities.Include(e => e.Children).FirstOrDefault();
var children = parent.Children.ToList();
ctx.MyEntities.Remove(parent);
foreach (var child in children)
ctx.MyEntities.Remove(child);
ctx.SaveChanges();
}
EF sorts the DELETE statements in the correct order in both cases.
在这两种情况下，EF 都以正确的顺序对 DELETE 语句进行排序。
Full test program (EF 5 / .NET 4.5 / SQL Server):
完整测试程序（EF 5 / .NET 4.5 / SQL Server）：
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);
}
}
class Program
{
static void Main(string[] args)
{
Database.SetInitializer<MyContext>(
new DropCreateDatabaseAlways<MyContext>());
using (var ctx = new MyContext())
{
ctx.Database.Initialize(false);
var parent = new MyEntity { Name = "Parent",
Children = new List<MyEntity>() };
parent.Children.Add(new MyEntity { Name = "Child 1" });
parent.Children.Add(new MyEntity { Name = "Child 2" });
ctx.MyEntities.Add(parent);
ctx.SaveChanges();
}
using (var ctx = new MyContext())
{
var parent = ctx.MyEntities.Include(e => e.Children)
.FirstOrDefault();
foreach (var child in parent.Children.ToList())
ctx.MyEntities.Remove(child);
ctx.MyEntities.Remove(parent);
ctx.SaveChanges();
}
}
}
}
Screenshot after the first usingblock with current content in DB table before the entities are deleted:
在using删除实体之前，DB 表中包含当前内容的第一个块之后的屏幕截图：
Screenshot from SQL profiler after the last SaveChanges:
最后一个 SQL 探查器的屏幕截图SaveChanges：
I.e. Child 1(Id = 2) and Child 2(Id = 3) are deleted beforeParent(Id = 1).
即Child 1（Id = 2）和Child 2（Id = 3）在Parent（Id = 1）之前被删除。
回答by Aram
There is another way, (think about the draw backs before doing it ... ) you can set the relationship to be ON DELETE CASCADE, and try to delete just the parent row.
还有另一种方法，（在做之前考虑一下缺点......）您可以将关系设置为ON DELETE CASCADE，并尝试仅删除父行。
回答by geekzster
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.
答案并没有解决如何从上面的结构中删除地球或欧洲。
I submit the following code as an alternative (modification of answer provided by Slauma, who did a good job btw).
我提交了以下代码作为替代（修改了 Slauma 提供的答案，顺便说一句，他做得很好）。
In the MyContext class, add the following methods:
在 MyContext 类中，添加以下方法：
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);
}
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;
}
}
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.
其中欧洲及其所有子项都被删除。
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.
在上面，我指定了根节点的第一个子节点，以演示使用我的代码可以从层次结构中的任何位置递归删除节点及其所有子节点。
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.
显然，我不会得到赏金，但希望我的帖子能帮助那些寻找适用于任意深度的自引用实体的解决方案的人。
Here is the full source, which is a modified version of Slauma's code from the selected answer:
这是完整源代码，它是所选答案中 Slauma 代码的修改版本：
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();
}
}
}
