Entity Framework与SQL Server博客API循环数据库架构错误求助

问题背景 #

刚接触SQL Server数据库设计、C#及.NET框架，在实现基于SQL Server的简易博客API时遇到数据库约束创建失败的问题。

模型类代码 #

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; }
    }
}

ApplicationDBContext代码 #

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; }
    }
}

错误信息 #

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.

解决方案 #

1. 修正模型类的语法错误 #

C#中属性名和DbSet名称不能用双引号包裹，这是语法错误，需全部移除引号。示例修正后的Blog类：

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; }
    public IEnumerable<Comment> Comments { get; set; }
    public User User { get; set; }
}

同时DbSet名称建议使用复数形式（符合EF Core惯例），修正后的DbSet定义：

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

2. 解决级联删除循环问题 #

错误核心是SQL Server不允许多个级联删除路径指向同一个表：删除User时，会同时触发两条级联路径：

• User → Comment（直接关联）
• User → Blog → Comment（间接关联）

需在OnModelCreating中显式配置其中一个关联的删除行为为NoAction或Restrict，阻断循环路径。修改后的OnModelCreating代码：

protected override void OnModelCreating(ModelBuilder builder)
{
    // 配置User与Blog的关联，级联删除Blog
    builder.Entity<Blog>()
        .HasOne(blog => blog.User)
        .WithMany(user => user.Blogs)
        .OnDelete(DeleteBehavior.Cascade);

    // 配置Blog与Comment的关联，级联删除Comment
    builder.Entity<Comment>()
        .HasOne(comment => comment.Blog)
        .WithMany(blog => blog.Comments)
        .OnDelete(DeleteBehavior.Cascade);

    // 配置User与Comment的关联，禁止级联删除，避免循环
    builder.Entity<Comment>()
        .HasOne(comment => comment.User)
        .WithMany(user => user.Comments)
        .OnDelete(DeleteBehavior.Restrict);
}

或者根据业务需求选择DeleteBehavior.NoAction，区别在于：

• Restrict：删除User时，若存在关联的Comment则直接报错
• NoAction：删除User时不主动处理关联的Comment，但数据库会检查外键约束，若Comment仍引用该User则报错

内容的提问来源于stack exchange，提问作者William Pattison