我使用实体框架核心,我有一个表:
public class BlogComment
{
public int Id { get; set; }
public BlogPost Post { get; set; }
[StringLength(100)]
public string AuthorName { get; set; }
[StringLength(254)]
public string AuthorEmail { get; set; }
public bool SendMailOnReply { get; set; }
[StringLength(2000)]
public string Content { get; set; }
public DateTime CreatedTime { get; set; }
public int? ReplyToId { get; set; }
public BlogComment ReplyTo { get; set; }
}
由此,EFC 生成下表:
CREATE TABLE [dbo].[BlogComment] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[AuthorEmail] NVARCHAR (254) NULL,
[AuthorName] NVARCHAR (100) NULL,
[Content] NVARCHAR (2000) NULL,
[CreatedTime] DATETIME2 (7) NOT NULL,
[PostId] INT NULL,
[ReplyToId] INT NULL,
[SendMailOnReply] BIT NOT NULL,
CONSTRAINT [PK_BlogComment] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_BlogComment_BlogPost_PostId] FOREIGN KEY ([PostId]) REFERENCES [dbo].[BlogPost] ([Id]),
CONSTRAINT [FK_BlogComment_BlogComment_ReplyToId] FOREIGN KEY ([ReplyToId]) REFERENCES [dbo].[BlogComment] ([Id])
);
GO
CREATE NONCLUSTERED INDEX [IX_BlogComment_PostId]
ON [dbo].[BlogComment]([PostId] ASC);
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_BlogComment_ReplyToId]
ON [dbo].[BlogComment]([ReplyToId] ASC) WHERE ([ReplyToId] IS NOT NULL);
有些评论会作为回复发送给另一个评论,但不是全部。删除原始评论后,回复将变为普通评论。因此,按照本教程,配置如下所示:
modelBuilder.Entity<BlogComment>()
.HasOne(p => p.ReplyTo)
.WithOne()
.HasForeignKey<BlogComment>(c => c.ReplyToId)
.IsRequired(false)
.OnDelete(DeleteBehavior.SetNull);
删除方法非常简单:
var comment = await context.BlogComment.Include(c => c.ReplyTo).SingleAsync(m => m.Id == id);
context.BlogComment.Remove(comment);
await context.SaveChangesAsync();
但是我无法运行它,出现错误:
System.Data.SqlClient.SqlException:DELETE 语句与 SAME TABLE REFERENCE 约束 "FK_BlogComment_BlogComment_ReplyToId" 冲突。
我该如何解决这个问题?
要在注释中结束对话:
首先,自我引用是 1:n 的关联:
modelBuilder.Entity<BlogComment>()
.HasOne(p => p.ReplyTo)
.WithMany(c => c.Replies)
.HasForeignKey(c => c.ReplyToId)
.IsRequired(false)
.OnDelete(<we'll get to that>);
所以,为了方便起见,BlogComment
现在也有房产
public ICollection<BlogComment> Replies { get; set; }
但是,我无法使用
.OnDelete(DeleteBehavior.SetNull);
它给了我
在表"博客注释"上引入外键约束"FK_BlogComments_BlogComments_ReplyToId"可能会导致循环或多个级联路径。
这是我们必须接受的 Sql Server 限制,没有办法逃避它。获得所需级联行为的唯一方法是
.OnDelete(DeleteBehavior.ClientSetNull);
这是:
对于 DbContext 跟踪的实体,依赖实体中的外键属性值设置为 null。这有助于在跟踪实体时使实体图保持一致状态,以便随后可以将完全一致的图写入数据库。(...)这是可选关系的默认值。
即:客户端执行 SQL 以取消外键值。不过,应跟踪子记录。要删除BlogComment
父级,删除操作应如下所示:
using (var db = new MyContext(connectionString))
{
var c1 = db.BlogComments
.Include(c => c.Replies) // Children should be included
.SingleOrDefault(c => c.Id == 1);
db.BlogComments.Remove(c1);
db.SaveChanges();
}
如您所见,您不必设置ReplyToId = null
,这是EF处理的事情。
对我来说,当我删除一个实体时,我必须Include()
我需要"处理"的实体。EF 无法管理当前未跟踪的内容。
var breedToDelete = context.Breed
.Include(x => x.Cats)
.Single(x => x.Id == testBreedId);
context.Breed.Remove(breedToDelete);
context.SaveChanges();
我可以通过手动将ReplyTo
设置为 null 来使其工作。我仍在寻找更好的解决方案,或者解释为什么需要它。这不是OnDelete(DeleteBehavior.SetNull)
应该做的吗?
var comment = await context.BlogComment.Include(c => c.ReplyTo).SingleAsync(m => m.Id == id);
var reply = await context.BlogComment.SingleOrDefaultAsync(m => m.ReplyToId == id);
if (reply != null)
{
reply.ReplyTo = null;
reply.ReplyToId = null;
context.Entry(reply).State = EntityState.Modified;
}
context.BlogComment.Remove(comment);