代码优先配置错误

Question

我正在尝试使用代码优先模型来建立一个简单的QA应用程序，以便学习。用户应该能够提出问题，回答问题并为问题和答案编写评论。以下是我的模型类：

[Table("UserProfile")]
public class UserProfile
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int UserId { get; set; }

    public string UserName { get; set; }

    public DateTime? BirthDate { get; set; }

    public ICollection<Gym> Gyms { get; set; }
}

[Table("Question")]
public class Question
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int QuestionId { get; set; }

    public int UserProfileId { get; set; }

    [ForeignKey("UserProfileId")]
    public UserProfile UserProfile { get; set; }

    public string Header { get; set; }

    public string Content { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime CreateDate { get; set; }

    public ICollection<Answer> Answers { get; set; }

    public ICollection<QuestionComment> QuestionComments { get; set; }
}

[Table("QuestionComment")]
public class QuestionComment
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int QuestionCommentId { get; set; }

    public int UserProfileId { get; set; }

    [ForeignKey("UserProfileId")]
    public UserProfile UserProfile { get; set; }

    public int QuestionId { get; set; }

    [ForeignKey("QuestionId")]
    public Question Question { get; set; }

    [Column("Content", TypeName = "ntext")]
    public string Content { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime CreateDate { get; set; }
}

[Table("Answer")]
public class Answer
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int AnswerId { get; set; }

    public int UserProfileId { get; set; }

    [ForeignKey("UserProfileId")]
    public UserProfile UserProfile { get; set; }

    public int QuestionId { get; set; }

    [ForeignKey("QuestionId")]
    public Question Question { get; set; }

    [Column("Content", TypeName="ntext")]
    public string Content { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime CreateDate { get; set; }

    public IList<AnswerComment> AnswerComments { get; set; }
}

[Table("AnswerComment")]
public class AnswerComment
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int AnswerCommentId { get; set; }

    public int UserProfileId { get; set; }

    [ForeignKey("UserProfileId")]
    public UserProfile UserProfile { get; set; }

    public int AnswerId { get; set; }

    [ForeignKey("AnswerId")]
    public Answer Answer { get; set; }

    [Column("Content", TypeName = "ntext")]
    public string Content { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime CreateDate { get; set; }
}

这是我的数据库上下文类：

public class TestDbContext : DbContext
{
    public TestDbContext()
        : base("DefaultConnection")
    {
    }

    public DbSet<UserProfile> UserProfiles { get; set; }
    public DbSet<Question> Questions { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // After update
        modelBuilder.Entity<UserProfile>()
            .HasMany(p => p.Questions)
            .WithRequired()
            .HasForeignKey(c => c.UserProfileId)
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<UserProfile>()
            .HasMany(p => p.Answers)
            .WithRequired()
            .HasForeignKey(c => c.UserProfileId)
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<UserProfile>()
            .HasMany(p => p.AnswerComments)
            .WithRequired()
            .HasForeignKey(c => c.UserProfileId)
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<UserProfile>()
            .HasMany(p => p.QuestionComments)
            .WithRequired()
            .HasForeignKey(c => c.UserProfileId)
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<Question>()
            .HasMany(p => p.Answers)
            .WithRequired()
            .HasForeignKey(c => c.QuestionId)
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<Question>()
            .HasMany(p => p.QuestionComments)
            .WithRequired()
            .HasForeignKey(c => c.QuestionId)
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<Answer>()
            .HasMany(p => p.AnswerComments)
            .WithRequired()
            .HasForeignKey(c => c.AnswerId)
            .WillCascadeOnDelete(false);
        // After update
    }
}

在使用以上声明创建数据库时，我遇到了以下错误：

引入外键约束 AnswerComment_UserProfile 到表 AnswerComment 可能会导致循环或多重级联路径。请指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION，或修改其他外键约束。\r\n无法创建约束。请参考前面的错误。

我应该如何解决这个问题？

提前感谢您的帮助！

- anilca

Answer 1

尝试将以下内容添加到您的UserProfile实体中：

public virtual ICollection<AnswerComment> AnswerComments { get; set; }

然后将以下内容添加到您的 modelBuilder，这样就可以消除您问题中的错误，您可能还需要对 QuestionComment、Question 和 Answer 进行相同操作:

modelBuilder.Entity<AnswerComment>()
            .HasRequired(u => u.UserProfile)
            .WithMany(a => a.AnswerComments)
            .WillCascadeOnDelete(false);

- SOfanatic

---

你确定吗？添加这些行后，我无法构建解决方案。我收到以下错误信息：无法隐式转换类型'System.Collections.Generic.ICollection<NS.Model.Question.AnswerComment>'为'NS.Model.Question.AnswerComment'。存在显式转换（是否缺少强制转换？） - xkcd

@anilca 给我的编辑种下种子，尝试将 WithOptional 更改为 WithMany。 - SOfanatic

Answer 2

您有一张名为A的表格，其中包含另一张名为B的表格的外键（FK）和另一张名为C的表格的外键。同时，表格B和表格C都包含对表格D的外键。

如果所有外键都已定义为级联删除，那么表格C中的记录可能会被删除两次。这不是逻辑问题，但是SQL Server不支持此选项。

为避免这个问题，请将“on delete no action”设置为默认值。

- asafrob

---

我该如何为这种情况设置不执行删除操作？ - xkcd

在你的外键中，尝试添加cascadeDelete=false。 - asafrob