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:
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.
2
Answers
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!
Please try this: