How to delete row with 1:1 relation to the same table?

Question

How to delete row with 1:1 relation to the same table?

cascade entity-framework entity-framework-core one-to-one

Question

I using Entity Framework Core, and I have a table:

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; }
}

From this, EFC generates the following table:

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);

Some comments are send as a reply to another, but not all. When the original comment is deleted, the reply becomes a normal comment. So, following this tutorial, the configuration looks is this:

modelBuilder.Entity<BlogComment>()
      .HasOne(p => p.ReplyTo)
      .WithOne()
      .HasForeignKey<BlogComment>(c => c.ReplyToId)
      .IsRequired(false)
      .OnDelete(DeleteBehavior.SetNull);

The delete method is pretty simple:

var comment = await context.BlogComment.Include(c => c.ReplyTo).SingleAsync(m => m.Id == id);
context.BlogComment.Remove(comment);
await context.SaveChangesAsync();

But I can't run it, I get an error:

System.Data.SqlClient.SqlException: The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_BlogComment_BlogComment_ReplyToId".

How can I fix this?

klenium

Related Questions

View more on Stack Overflow

Gert Arnold · Accepted Answer · 5/13/2018 9:21:01 AM

To wrap up the conversation in the comments:

First, the self reference is a 1:n association:

modelBuilder.Entity<BlogComment>()
      .HasOne(p => p.ReplyTo)
      .WithMany(c => c.Replies)
      .HasForeignKey(c => c.ReplyToId)
      .IsRequired(false)
      .OnDelete(<we'll get to that>);

So, just for convenience, BlogComment now also has a property

public ICollection<BlogComment> Replies { get; set; }

However, I can't create the table using

.OnDelete(DeleteBehavior.SetNull);

It gives me

Introducing FOREIGN KEY constraint 'FK_BlogComments_BlogComments_ReplyToId' on table 'BlogComments' may cause cycles or multiple cascade paths.

This is a Sql Server restriction we just have to accept, no way to evade it. The only way to get the desired cascade behavior is

.OnDelete(DeleteBehavior.ClientSetNull);

Which is:

For entities being tracked by the DbContext, the values of foreign key properties in dependent entities are set to null. This helps keep the graph of entities in a consistent state while they are being tracked, such that a fully consistent graph can then be written to the database. (...) This is the default for optional relationships.

I.e.: the client executes SQL to nullify the foreign key values. The child records should be tracked though. To remove a BlogComment parent the delete action should look like:

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();
}

As you see, you don't have to set ReplyToId = null, that's something EF takes care of.

How to delete row with 1:1 relation to the same table?

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Related Questions

Fastest Entity Framework Extensions

Performance

Expression Evaluator

Related