SQL Error: Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths. Entity Framework Core

asp.net-core c# entity-framework-core foreign-keys sql-server

Question

I am using SQL and attempting to Add-Migration using Entity Framework Core. I am unsure how to resolve this. It is for associating a review system with the user and the product. This worked in SQLite. Now using SQL server. I have tried to provide everything while being brief. I can provide more if needed. Below is my code, can anyone please help?

An error occurred while accessing the IWebHost on class 'Program'. Continuing without the application service provider. Error: Introducing FOREIGN KEY constraint 'FK_ProductReviews_AspNetUsers_ReviewerId' on table 'ProductReviews' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint or index. See previous errors.

I have tried the commented out code.

            protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);

    builder.Entity<MemberReview>()
                .HasKey(k => new { k.RevieweeId, k.ReviewerId });

            builder.Entity<MemberReview>().
                HasOne(u => u.Reviewer)
                .WithMany(u => u.ReviewedMembers);
                // .HasForeignKey(u => u.ReviewerId)
                // .OnDelete(DeleteBehavior.Restrict);

            builder.Entity<MemberReview>().
                HasOne(u => u.Reviewee)
                .WithMany(u => u.MemberReviews);
            // .HasForeignKey(u => u.RevieweeId)
            // .OnDelete(DeleteBehavior.Restrict);

            builder.Entity<ProductReview>()
                .HasKey(k => new { k.ReviewerId, k.ReviewedProductId });

            builder.Entity<ProductReview>().
                HasOne(u => u.ReviewedProduct)
                .WithMany(u => u.ProductReviews);
                //.HasForeignKey(u => u.ReviewedProductId)
                //.OnDelete(DeleteBehavior.Restrict);

            builder.Entity<ProductReview>().
                HasOne(u => u.Reviewer)
                .WithMany(u => u.ReviewedProducts);
                //.HasForeignKey(u => u.ReviewerId)
                //.OnDelete(DeleteBehavior.Restrict);
        }

        public class ProductReview
    {
        public Product ReviewedProduct { get; set; }
        public User Reviewer { get; set; }
        [Required]
        public int ReviewerId { get; set; }
        [Required]
        [MaxLength(30)]
        public string ReviewerUserName { get; set; }
        [Required]
        public int ReviewedProductId { get; set; }
        [Required]
        [MaxLength(35)]
        public string Title { get; set; }
        [Required]
        [MaxLength(420)]
        public string Review { get; set; }
        [Required]
        [MaxLength(2)]
        public int Rating { get; set; }
    }

        public class User : IdentityUser<int>
    {
        [Required]
        [MaxLength(12)]
        public string UserType { get; set; }
        [Required]
        public DateTime DateOfEstablishment { get; set; }
        [Required]
        [MaxLength(75)]
        public string KnownAs { get; set; }
        public DateTime Created { get; set; }
        public DateTime LastActive { get; set; }
        [MaxLength(420)]
        public string Description { get; set; }
        public ICollection<Photo> Photos { get; set; }
        public ICollection<Product> Products { get; set; }
        // REVIEW THING
        public ICollection<MemberReview> MemberReviews { get; set; }
        public ICollection<MemberReview> ReviewedMembers { get; set; }
        public ICollection<ProductReview> ReviewedProducts { get; set; }
        // *****
    }

        public class Product
    {
        public int Id { get; set; }
        [Required]
        [MaxLength(75)]
        public string Name { get; set; }
        [Required]
        [MaxLength(420)]
        public string Description { get; set; }
        public DateTime DateAdded { get; set; }
        public User User { get; set; }
        [Required]
        public int UserId { get; set; }
        // REVIEW THINGS
        public ICollection<ProductReview> ProductReviews { get; set; }
        // *****
    }
1
2
1/21/2019 6:15:35 AM

Accepted Answer

I just tried this:

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);

    builder.Entity<MemberReview>()
        .HasKey(e => new { e.RevieweeId, e.ReviewerId });

    builder.Entity<ProductReview>()
        .HasKey(e => new { e.ReviewerId, e.ReviewedProductId });

    builder.Entity<MemberReview>()
        .HasOne<User>(e => e.Reviewer)
        .WithMany(e => e.MemberReviews)
        .HasForeignKey(e => e.ReviewerId)
        .OnDelete(DeleteBehavior.Restrict); ////////
                                                  //
    builder.Entity<MemberReview>()                //
        .HasOne<User>(e => e.Reviewee)            /// => only one of these two can be cascade
        .WithMany(e => e.ReviewedMembers)         //
        .HasForeignKey(e => e.RevieweeId)         //
        .OnDelete(DeleteBehavior.Restrict); ////////

    builder.Entity<ProductReview>()
        .HasOne<User>(e => e.Reviewer)
        .WithMany(e => e.ReviewedProducts)
        .HasForeignKey(e => e.ReviewerId)
        .OnDelete(DeleteBehavior.Restrict);
}

You had not provided the MemberReview class so I created this:

public class MemberReview
{
    public User Reviewer { get; set; }
    public int ReviewerId { get; set; }

    public User Reviewee { get; set; }
    public int RevieweeId { get; set; }
}

And this is the result:

DB Diagram

2
1/21/2019 10:57:26 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow