EF Core PostgreSQL重复键约束冲突问题排查与解决

我查过同类问题，但都和我的情况不匹配。我有两个实体：EducationResult用来关联TestModule和Student并存储附加信息，TestModule包含Questions和对应的Answers。当尝试让同一学生再次关联同一TestModule时，出现以下错误：

duplicate key value violates unique constraint "IX_EducationResults_TestModuleId"

我认为问题出在EF Core的配置上，TestModule应该可以像静态资源一样重复使用。

实体代码 #

EducationResult #

public class EducationResult : AuditableEntity
{
    public EducationResultId EducationResultId { get; set; }

    public IsDone IsDone { get; set; }

    public Score Score { get; set; }

    public MaxScore MaxScore { get; set; }

    public IsPassed IsPassed { get; set; }

    // Student
    public StudentId StudentId { get; set; }

    public virtual Student Student { get; set; }

    // TestModule
    public TestModuleId TestModuleId { get; set; }

    public virtual TestModule TestModule { get; set; }
}

TestModule #

public class TestModule : AuditableEntity
{
    public TestModuleId TestModuleId { get; set; }

    public TestModuleName Name { get; set; }

    public TestModuleLevel Level { get; set; }

    public TestModuleDescription Description { get; set; }

    public ICollection<Question> Questions { get; set; }
}

配置代码 #

EducationResultConfiguration #

public class EducationResultConfiguration : IEntityTypeConfiguration<EducationResult>
{
    public void Configure(EntityTypeBuilder<EducationResult> builder)
    {
        builder.HasKey(x => x.EducationResultId);
     
        builder.Property(x => x.EducationResultId)
            .HasConversion(x => x.Value, x => new EducationResultId(x));
        
        builder.Property(x => x.IsDone)
            .HasConversion(x => x.Value, x => new IsDone(x));

        builder.Property(x => x.Score)
            .HasConversion(x => x.Value, x => new Score(x));
        
        builder.Property(x => x.MaxScore)
            .HasConversion(x => x.Value, x => new MaxScore(x));
        
        builder.Property(x => x.IsPassed)
            .HasConversion(x => x.Value, x => new IsPassed(x));

        // Entity configurations
        builder
            .HasOne(x => x.Student)
            .WithMany(x => x.EducationResults)
            .HasForeignKey(q => q.StudentId);
        
        builder
            .HasOne(x => x.TestModule)
            .WithOne()
            .HasForeignKey<EducationResult>(x => x.TestModuleId);
    }
}

TestModuleConfiguration #

public class TestModuleConfiguration : IEntityTypeConfiguration<TestModule>
{
    public void Configure(EntityTypeBuilder<TestModule> builder)
    {
        builder.HasKey(x => x.TestModuleId);

        builder.Property(x => x.TestModuleId)
            .HasConversion(x => x.Value, x => new TestModuleId(x));

        builder.Property(x => x.Name)
            .HasConversion(x => x.Value, x => new TestModuleName(x));

        builder.Property(x => x.Level)
            .HasConversion(x => x.Value, x => new TestModuleLevel(x));

        builder.Property(x => x.Description)
            .HasConversion(x => x.Value, x => new TestModuleDescription(x));

        builder
            .OwnsMany(module => module.Questions, question =>
            {
                #region Question owner
                
                question.WithOwner(x => x.TestModule).HasForeignKey(x => x.TestModuleId);

                #endregion

                #region Question properties

                question.HasKey(x => x.QuestionId);
                question.Property(x => x.QuestionId)
                    .HasConversion(x => x.Value, x => new QuestionId(x));

                question.Property(x => x.QuestionText)
                    .HasConversion(x => x.Value, x => new QuestionText(x));

                question.Property(x => x.Explanation)
                    .HasConversion(x => x.Value, x => new QuestionExplanation(x));

                question.Property(x => x.TestModuleId)
                    .HasConversion(x => x.Value, x => new TestModuleId(x));

                #endregion

                question.OwnsMany(x => x.Answers, answer =>
                {
                    #region Answer owner

                    answer.WithOwner(x => x.Question).HasForeignKey(x => x.QuestionId);

                    #endregion

                    #region Answer properties

                    answer.HasKey(x => x.QuestionAnswerId);
    
                    answer.Property(x => x.QuestionAnswerId)
                        .HasConversion(x => x.Value, x => new QuestionAnswerId(x));
    
                    answer.Property(x => x.Text)
                        .HasConversion(x => x.Value, x => new QuestionAnswerText(x));

                    answer.Property(x => x.IsCorrect)
                        .HasConversion(x => x.Value, x => new Correct(x));
    
                    answer.Property(x => x.QuestionId)
                        .HasConversion(x => x.Value, x => new QuestionId(x));

                    #endregion
                });
            });
    }
}

完整错误信息 #

An exception occurred in the database while saving changes for
context type 'wisse.Persistence.DAL.WisseDbContext'.
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
---> Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "IX_EducationResults_TestModuleId"

解决方法 #

问题根源在于你给EducationResult和TestModule配置了一对一关系，EF Core会自动给TestModuleId字段添加唯一约束，导致同一个TestModule只能被关联一次。

你需要把关系改成一对多：一个TestModule可以对应多个EducationResult，同时保持一个Student对应多个EducationResult的关系不变。

修改关系配置 #

在EducationResultConfiguration中，将原来的一对一关系代码：

builder
    .HasOne(x => x.TestModule)
    .WithOne()
    .HasForeignKey<EducationResult>(x => x.TestModuleId);

替换为一对多关系：

builder
    .HasOne(x => x.TestModule)
    .WithMany() // 表示一个TestModule对应多个EducationResult
    .HasForeignKey(x => x.TestModuleId)
    .OnDelete(DeleteBehavior.Restrict); // 可选：防止删除TestModule时连带删除EducationResult，根据业务需求调整

可选：添加复合唯一约束 #

如果需要确保同一学生不能重复关联同一TestModule（即每个学生对同一TestModule只有一条记录），可以添加复合唯一索引：

builder.HasIndex(x => new { x.StudentId, x.TestModuleId }).IsUnique();

这样既允许TestModule被多个学生多次关联，也能避免同一学生对同一TestModule产生重复的EducationResult记录（如果业务需要的话）。

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