I am new to SQL Server database design let alone C# and the .NET framework. I was wondering if I could get some help regarding the implementation of my SQL Server database with a simple blogging API.

Here is the diagram that I drew up:

Blog Database Schema

Here are the model classes that I have created for this blog:

namespace Dotnet_blog.Models.Domain
{
    public class Blog
    {
        public Guid Id { get; set; }
        public string Title { get; set; }

        public string Content { get; set; }

        public int Likes { get; set; }
        public int Dislikes { get; set; }

        // Reference
        public IEnumerable<Comment> Comments { get; set; }

        // Reference
        public User User { get; set; }
    }
}

namespace Dotnet_blog.Models.Domain
{
    public class User
    {
        public Guid Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public string Password { get; set; }

        // Reference here
        public IEnumerable<Comment> Comments { get; set; }
        public IEnumerable<Blog> Blogs { get; set; }
    }
}

namespace Dotnet_blog.Models.Domain
{
    public class Comment
    {
        public Guid Id { get; set; }
        public int Likes { get; set; }
        public int Dislikes { get; set; }
        public string Content { get; set; }

        // Reference here
        public Blog Blog { get; set; }

        // Reference here
        public User User { get; set; }
    }
}

And finally here is my ApplicationDBContext for creating the SQL objects

using Dotnet_blog.Models.Domain;
using Microsoft.EntityFrameworkCore;

namespace Dotnet_blog.Data
{
    public class ApplicationDBContext:DbContext
    {
        public ApplicationDBContext(DbContextOptions<ApplicationDBContext> options):base(options) //pass options to parent class.
        {
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            // This commented out section seems to not work.
            //builder.Entity<Comment>()
            //    .HasOne(comment => comment.User)
            //    .WithMany(user => user.Comments);

            //builder.Entity<Blog>()
            //    .HasOne(blog => blog.User)
            //    .WithMany(user => user.Blogs);

            //builder.Entity<Comment>()
            //    .HasOne(comment => comment.Blog)
            //    .WithMany(blog => blog.Comments);
        }

        public DbSet<User> User { get; set; }
        public DbSet<Blog> Blog { get; set; }
        public DbSet<Comment> Comment { get; set; }
    }
}

I get this error:

Microsoft.Data.SqlClient.SqlException (0x80131904): Introducing FOREIGN KEY constraint 'FK_Comment_User_UserId' on table 'Comment' 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.

Can anyone help me figuring out this error? That would be greatly appreciated.

CodePudding user response：

Please try this:

builder.Entity("Comment", b =>
    {
        b.HasOne("User", null)
            .WithMany()
            .HasForeignKey("UserId")
            .OnDelete(DeleteBehavior.Cascade)
            .IsRequired();
    });
builder.Entity("Comment", b =>
    {
        b.HasOne("Blog", null)
            .WithMany()
            .HasForeignKey("BlogId")
            .OnDelete(DeleteBehavior.Cascade)
            .IsRequired();
    });
builder.Entity("Blog", b =>
    {
        b.HasOne("User", null)
            .WithMany()
            .HasForeignKey("UserId")
            .OnDelete(DeleteBehavior.Cascade)
            .IsRequired();
    });

CodePudding user response：

The error that I was getting had to do with the fact that I had Comments cascade deleting from the user AND the blog. If I just set the cascade delete on the blog, then the comments will delete even if the user is deleted. This ultimately fixed my problem. I appreciate all the help from you guys!