Post Repost May cause cycles or multiple cascade paths-CodePudding

Introducing FOREIGN KEY constraint 'FK_Posts_Posts_RepostId' on table 'Posts' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Attempt 1

public class Post
{
    public int Id { get; set; }
    public string Text { get; set; }
    public int? RepostId { get; set; }
    public Post Repost { get; set; }
}

Fluent API

builder.Entity<Post>()
       .HasOne(s => s.Repost)
       .WithOne()
       .OnDelete(DeleteBehavior.SetNull);

Attempt 2

https://stackoverflow.com/a/53678583/13434418

public class Repost
{
    public Repost()
    {
        Posts = new HashSet<Post>();
    }

    public int Id { get; set; }
    public int? PostId { get; set; }
    public virtual Post? Post { get; set; }
    public virtual ICollection<Post> Posts { get; set; }
}

public class Post
{
    public int Id { get; set; }
    public string Text { get; set; } = null!;
    public int? RepostId { get; set; }
    public virtual Repost? Repost { get; set; }
    public virtual Repost? RepostNavigation { get; set; }
}

Fluent API

builder.Entity<Post>(entity =>
        {
            entity.HasOne(d => d.Repost)
                .WithMany(p => p.Posts)
                .HasForeignKey(d => d.RepostId)
                .OnDelete(DeleteBehavior.SetNull)
                .HasConstraintName("FK_Posts_Posts");
        });

        builder.Entity<Repost>(entity =>
        {
            entity.ToTable("Repost");

            entity.HasIndex(e => e.PostId, "IX_Repost_PostId")
                .IsUnique();

            entity.HasOne(d => d.Post)
                .WithOne(p => p.RepostNavigation)
                .HasForeignKey<Repost>(d => d.PostId)
                .OnDelete(DeleteBehavior.Cascade);
        });

I need to set null in the repost when I delete a post. I can use DeleteBehavior.NoAction, but the question is, is there any way to set it automatically when the post is deleted?

UPD

Solution

public class Post
{
    public int Id { get; set; }
    public string Text { get; set; }
    public int? RepostId { get; set; }
    public Post Repost { get; set; }
    public ICollection<Post> Reposts { get; set; }
}

    builder.Entity<Post>()
            .HasOne(s => s.Repost)
            .WithMany(s => s.Reposts)
            .HasForeignKey(s => s.RepostId)
            .IsRequired(false)
            .OnDelete(DeleteBehavior.ClientSetNull);

CodePudding user response：

Your first attempt was close, it's just that with self-references, or any reference where your property name differs from the Type name you need to explicitly configure the FK:

This should work:

builder.Entity<Post>()
   .HasOne(s => s.Repost)
   .WithOne()
   .HasForeignKey<Post>(s => s.RepostId)
   .OnDelete(DeleteBehavior.ClientSetNull);

DeleteBehavior.SetNull isn't a valid option because SQL Server cannot resolve a that behaviour on a self-referencing FK. If you try and set up a self-referencing FK in SQL Server with a ON DELETE SET NULL you get the same error. By using ClientSetNull EF can issue UPDATE statements for any related entities before deleting the record.

SetNull can appear to work if the DbContext happens to have the related entity(ies) tracked when deleting the related one.