EF Core, configure modelBuilder to fix error "cycles or multiple cascade paths"

Question

I want to configure folowing relations:

Like may has a Reason (type Comment) and this Comment have to be cascade deleated if Like will be deleated
Like may have a Comment (type Comment) or User (type User), Like have to be cascade deleated if Comment or User will be deleated
Like must not have both Comment (type Comment) and User (type User)
Author (type User) must not be able to create Likes duplicates

I tried many combinations but did not have a success, please help me.

My current code:

public class Comment
{
    public int Id { get; set; }
    public string Text { get; set; }
    public Like ToLike { get; set; }
    public int? ToLikeId { get; set; }
    public ICollection<Like> Likes { get; set; }
}

public class User
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

    public ICollection<Like> CreatedLikes { get; set; }
    public ICollection<Like> Likes { get; set; }
}

public class Like
{
    public int Id { get; set; }

    public User Author { get; set; }
    public int AuthorId { get; set; }

    public DateTime CreatedAt { get; set; }

    public Comment Reason { get; set; }

    public Comment Comment { get; set; }
    public int? CommentId { get; set; }

    public User User { get; set; }
    public int? UserId { get; set; }
}

        modelBuilder.Entity<Like>(b =>
        {
            b.HasAlternateKey(l => new { l.AuthorId, l.CommentId, l.UserId });
            b.HasOne(l => l.Reason)
            .WithOne(c => c.ToLike)
            .HasForeignKey<Comment>(c => c.ToLikeId)
            .OnDelete(DeleteBehavior.SetNull);

            b.HasOne(l => l.Author).WithMany(a => a.CreatedLikes)
                .OnDelete(DeleteBehavior.Restrict);

            b.HasOne(l => l.User).WithMany(u => u.Likes);
            b.HasOne(l => l.Comment)
            .WithMany(c => c.Likes)
            .HasForeignKey(c => c.CommentId);
        });

Currently I have this error on first DB initialize (row context.Database.Migrate();):

System.Data.SqlClient.SqlException: 'Introducing FOREIGN KEY constraint 'FK_Likes_Comments_CommentId' on table 'Likes' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

The main question: how to fix this error?

Additional questions:

How to configure my all needs described in the top?
What is the best db schema for Likes to be able to have a history of user activities?

score 2 · Accepted Answer · answered Jul 17 '17 at 16:24

If I understand the issue correctly, you are experiencing the diamond pattern when you are creating primary key and foreign key relationships that have cascading updates and deletes. In the pattern with cascading relationships the database finds the cascading ambiguous.

One of the relationships in the diamond must have the Restrict attribute for the foreign key.

Microsoft Documentation on Entity Framework Core Relationships

Cascade Delete

By convention, cascade delete will be set to Cascade for required relationships and Restrict for optional relationships (see the Required section for the difference between required and optional relationships). Cascade means dependent entities are also deleted. Restrict means that dependent entities that are not loaded into memory will remain unchanged and must be manually deleted, or updated to point to a valid principal entity. For entities that are loaded into memory, EF will attempt to set the foreign key properties to null.

It is a SqlServer Limitation. It cannot handle multiple cascade paths. In above example, deleting a Parent, can cause delete of a Child1 & Child2. Which in turn can case delete of same Grandchild. Hence there are multiple paths to same Grandchild hence SqlServer throws. — Smit, Jul 17 '17 at 17:55

EF Core, configure modelBuilder to fix error "cycles or multiple cascade paths"

1 Answers1

Linked