I'm having some issues with updating a parent entity that has child entities that already exist in the database.

I have a Question entity with a many-to-many relationship with the Answer entity. The simplified version looks like this:

public class Question
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }
    
    [JsonIgnore]
    public virtual List<QuestionParentRelation>? ParentQuestions { get; set; }
    [JsonIgnore]
    public virtual List<QuestionParentRelation>? ChildQuestions { get; set; }
    
    public string Name { get; set; }
    
    public virtual List<Answer>? PossibleAnswers { get; set; }
}

public class Answer
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }
    
    public string AnswerText { get; set; }
    
    [JsonIgnore]
    public List<Question> Questions { get; set; }
}

But there is also a many-to-many relationship with other questions, because on the UI side there is a drag-and-drop that allows to create child follow-up questions etc. This relationship holds a ParentQuestionAnswerId (a foreign key that indicates which answer should be given on the parent question, before the child question will be shown).

public class QuestionParentRelation
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid RelationId { get; set; }

    public Guid? ParentId { get; set; }
    public Guid ChildId { get; set; }
    
    public virtual Question ParentQuestion { get; set; }
    public virtual Question ChildQuestion { get; set; }
    
    public int ParentHashNum { get; set; }
    public int ChildHashNum { get; set; }
    
    // FK: The answer that is needed before the child question is shown
    public Guid? ParentQuestionAnswerId { get; set; }
    [JsonIgnore]
    public virtual Answer? ParentQuestionAnswer { get; set; }

    public int Level { get; set; }
    public int Order { get; set; }
}

Now the problem is that every time I try to update a Question entity (with a given list of already existing Answer entities):

1. I get an error saying that there is a duplicate entry for that specific Answer Id
2. If I try to remove the Answer from the database first, I get an error saying that there is a Foreign Key constraint (ParentQuestionAnswerId) that fails. (But if removing the Answer entity first is the only soluiton, I do NOT want all QuestionParentRelation entities to set the ParentQuestionAnswerId to NULL, because that would also affect relations that are not supposed to be editted)

This Is what I have configured in FluentAPI:

builder.Entity<Question>()
    .HasMany(q => q.PossibleAnswers)
    .WithMany(a => a.Questions)
    .UsingEntity(join => join.ToTable("QuestionPossibleAnswers"));

builder.Entity<QuestionParentRelation>()
    .ToTable("QuestionParentRelations")
    .HasKey(r => new { r.RelationId });

builder.Entity<Question>()
    .HasMany(q => q.ParentQuestions)
    .WithOne(r => r.ChildQuestion)
    .HasForeignKey(r => r.ChildId);

builder.Entity<Question>()
    .HasMany(q => q.ChildQuestions)
    .WithOne(r => r.ParentQuestion)
    .HasForeignKey(r => r.ParentId);

CodePudding user response：

Alright so for anyone who stumbles upon this question: I still don't know if there is a way in which EF can handle this issue all by itself, but I solved it by looping over the Answers inside the Question's DTO object and then comparing them with the entity.

If an answer already exists in the entity, I simply remove it from the DTO and send the updated DTO to the service. This way, existing answers won't be added twice.

Inside QuestionsController's HttpPut method

var question = _questionService.EagerGetBy(q => q.Id == Guid.Parse(id));
if (question is null)
    return NotFound("Geen resultaat gevonden voor Id "   id);

if (questionDto.PossibleAnswers != null && question.PossibleAnswers != null)
{
    questionDto.PossibleAnswers.ForEach(a =>
    {
        if (a.Id != null && question.PossibleAnswers.Contains(a))
            questionDto.PossibleAnswers.Remove(a);
    });
}