Home > Back-end >  Duplicate key value violates unique constraint | EF Core PostgreSQL
Duplicate key value violates unique constraint | EF Core PostgreSQL

Time:10-16

I have seen similar questions, but none of them seem to be related to my problem.

I have 2 entities, EducationResult links TestModule and Student with additional information,
and TestModule with Questions and deeper with Answers to those questions.

When trying to link same TestModule with same Student one more time I'm getting error:

duplicate key value violates unique constraint "IX_EducationResults_TestModuleId"

I don't see the TestModule entity as unique and problem for sure is laying in configuration.

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

And configuration:

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

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

And the error:

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"

I want to use TestModule contents like, let me say, static field in code,
these values should be reusable even if they were already used in EducationResult
What is the problem and how to allow using same entity multiple times?

CodePudding user response:

You have declared the relation 1:1 for EducationResult and TestModule :

public void Configure(EntityTypeBuilder<EducationResult> builder)
{
    ...
    builder
        .HasOne(x => x.TestModule) // EducationResult can have one TestResult
        .WithOne() // TestResult can have ONE EducationResult
        .HasForeignKey<EducationResult>(x => x.TestModuleId);
}

In this case, EF Core will generate a constraint to ensure a TestModule is referenced in only one EducationResult.

For TestModule can be referenced several time in EducationResult, you need to declare a relation 1:n :

public void Configure(EntityTypeBuilder<EducationResult> builder)
{
    ...
    builder
        .HasOne(x => x.TestModule) // EducationResult can have one TestResult
        .WithMany() // TestResult can have MANY EducationResult
        .HasForeignKey<EducationResult>(x => x.TestModuleId);
}

It's like you do with the relation EducationResult|Student where a Student can have several result.

  • Related