sqlasp.netentity-framework-coreblazor-webassembly

.Net Blazor id The MERGE statement conflicted with the FOREIGN KEY constraint problem


I am working on a project and there is a trainer I follow. When I came to the update process, I started to get an error, when I went with Breakpoints, I realized that the QuizId value came 0000 in the save phase. When I check the database, all my connections are correct, id values are also available, but I have a problem with the output.

Questions.cs

[Key]
public Guid Id { get; set; }
public string Text { get; set; }

public Guid QuizId { get; set; }
[ForeignKey(nameof(QuizId))]
public virtual Quiz Quiz { get; set; }

public virtual ICollection<Options> Options { get; set; } = [];

Options.cs

[Key]
public int Id { get; set; }

public string Text { get; set; }
public bool IsCorrect { get; set; }
public Guid QuestionId { get; set; }

[ForeignKey(nameof(QuestionId))]
public virtual Questions Questions { get; set; }

Quiz.cs

  [Key]
  public Guid Id { get; set; }
  public string? Name { get; set; }
  public int TotalQuestions { get; set; }
  public int TimeInMinutes { get; set; }
  public bool IsActive { get; set; }

  public Guid CategoryId { get; set; }
  [ForeignKey(nameof(CategoryId))]
  public virtual Category? Category { get; set; }

  public ICollection<Questions> Questions { get; set; } = [];

below is the block of code I used to save the data

public async Task<QuizApiResponse> SaveQuizAsync(QuizSaveDto dto)
{
    var questions = dto.Question.Select(q => new Questions
    {
        Id = Guid.NewGuid(),
        Text = q.Text,
        Options = q.Option.Select(o => new Options
        {
            Id = 0,
            Text = o.Text,
            IsCorrect = o.IsCorrect
        }).ToArray()
    }).ToArray();

    if (dto.Id == Guid.Empty)
    {

        var quiz = new Quiz
        {
            Id = Guid.NewGuid(),
            Name = dto.Name,
            CategoryId = dto.CategoryId,
            TotalQuestions = dto.TotalQuestions,
            TimeInMinutes = dto.TimeInMinutes,
            IsActive = dto.IsActive,
            Questions = questions
        };
        _context.Quizzes.Add(quiz);
    }
    else
    {
        var dbQuiz = await _context.Quizzes.FirstOrDefaultAsync(q => q.Id == dto.Id);//QuizId have
        if (dbQuiz == null)
        {
            return QuizApiResponse.Fail("Quiz doesn't exists");
        }
        dbQuiz.CategoryId = dto.CategoryId;
        dbQuiz.IsActive = dto.IsActive;
        dbQuiz.Name = dto.Name;
        dbQuiz.TimeInMinutes = dto.TimeInMinutes;
        dbQuiz.TotalQuestions = dto.TotalQuestions;
        dbQuiz.Questions = questions;//QuizId is 0000

        _context.Quizzes.Update(dbQuiz);// burada QuizId null
    }

    try
    {
        await _context.SaveChangesAsync();
        return QuizApiResponse.Success();
    }
    catch (Exception ex)
    {
        return QuizApiResponse.Fail(ex.Message);
    }
}

here is a visual of my database schemadatabse

the point I detected when I proceeded with breakpoint enter image description here

the error I get is as follows

Microsoft.EntityFrameworkCore.Update[10000] An exception occurred in the database while saving changes for context type 'BlazingQuiz.Api.Data.Repositories.QuizContext'. Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details. ---> Microsoft.Data.SqlClient.SqlException (0x80131904): The MERGE statement conflicted with the FOREIGN KEY constraint "FK_Options_Questions_QuestionId". The conflict occurred in database "BlazingQuiz", table "dbo.Questions", column 'Id'. at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)


Solution

  • The issue is likely around how you are doing your update. For a start, when you have a collection navigation property you should never expose or use a setter. For instance with Quiz.Questions this should be:

    public virtual ICollection<Questions> Questions { get; } = [];
    

    No setter. Any code using a setter is going to cause you problems. When you load an existing quiz to update the questions you cannot merely replace the collection reference with a new set like you can with in-memory collections. The quiz has questions assigned to it, so you either need to determine which items to add vs. remove, or remove all and add completely new ones provided the new question list has completely new, unique IDs. Setting a navigation property collection causes all kinds of problems because it removes the tracking proxy EF would have when reading the collection and it doesn't know after the fact to "delete the whole collection" when it goes to save changes once that reference is blasted.

    So when loading the existing Quiz, eager load it's existing questions:

    var dbQuiz = await _context.Quizzes
        .Include(q => q.Questions)
        .FirstOrDefaultAsync(q => q.Id == dto.Id);
    

    From there to replace the list of questions with new ones:

    dbQuiz.Questions.Clear();
    foreach(var question in questions)
        dbQuiz.Questions.Add(question);
    

    lastly, to save the changes, do not use Update. Update is used for detached entities and ignores the change tracker tracking for modifications. When you load the Quiz and questions by default these are tracking entities so all you need to do is call SaveChanges() after making your changes:

    await _context.SaveChangesAsync();
    

    EF will manage all of the FK assignments for the related entities. The only other step that might be needed in terms of handling the orphan questions removed from the quiz is that you might need to explicitly mark them as deleted if the database relationship isn't set up to delete orphans:

    _context.Questions.RemoveRange(dbQuiz.Questions); // This step might be needed.
    dbQuiz.Questions.Clear();
    

    That should hopefully get you closer to a working solution.