Why I am getting an error when i try to save form into quiz model with entity framework?

Question

I am to new both entity framework and dotnet core. Shortly i want to explain what i did and what kind of an error i got?

What i did?

First I created a few models below.

public class Quiz
{
    public int QuizID { get; set; }
    public int UserID { get; set; }
    public string Text { get; set; }
    public User User { get; set; }

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

public class Question
{
    public int QuestionID { get; set; }
    public string Text { get; set; }
    public int QuizID { get; set; }

    public Quiz Quiz { get; set; }

    public IList<Option> Options { get; set; }
}

public class User
{
    public int ID { get; set; }
    public string Username { get; set; }
    public string Password { get; set; }

    public ICollection<Quiz> Quizzes { get; set; }
}

public class Option
{
    public int OptionID { get; set; }
    public string Choice { get; set; }
    public int QuestionID { get; set; }

    public bool? IsCorrect { get; set; }

    public Question Question { get; set; }
}

Second I created IActionResult method for saving informations that comes from user.

public async Task<IActionResult> CreateQuiz()
        {
            Quiz quiz = new Quiz();
            quiz.UserID = 0;
            quiz.Text = Request.Form["content"].ToString();
            _context.Add(quiz);
            await _context.SaveChangesAsync();

            for (int i = 1; i <= 4; i++)
            {
                Question question = new Question();
                question.QuizID = quiz.QuizID;
                question.Text = Request.Form["title_" + i].ToString(); 
                _context.Add(question);
                _context.SaveChanges();

                for (int j = 1; j <= 4; j++)
                {
                    Option option = new Option();
                    option.QuestionID = question.QuestionID;
                    option.Choice = Request.Form["option_a" + i].ToString();
                    option.IsCorrect = j == int.Parse(Request.Form["correct_answer_" + i].ToString());
                    _context.Add(option);
                }
            }

            await _context.SaveChangesAsync();
            return RedirectToAction(nameof(Index));
        }

Finally i got this error when i save form.

An unhandled exception occurred while processing the request. SqliteException: SQLite Error 19: 'FOREIGN KEY constraint failed'. Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(int rc, sqlite3 db)

DbUpdateException: An error occurred while updating the entries. See the inner exception for details. Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)

I need your help guys.

*Sorry for spelling rules i am also new to english.

Answer 1

There are a few things you can do to help avoid issues like this. It's a bit hard to pin down from your description exactly what is wrong, but you can simplify your code considerably which should take care of it.

Firstly, follow a convention for your key names and use annotations to nominate your Keys rather than relying on convention. You have a mix of naming like "QuestionId" for the PK on Question, yet "Id" for the PK on User.

Next, I would remove all FK fields and use Shadow Properties for the FK fields. The issue with FK columns is they are a second source of truth for relationships when you use Navigation Properties. Is option.QuestionId the ID of the question, or option.Question.QuestionId? How do you guarantee these are always in sync?

Lastly for the entity definitions, declare the navigation properties as virtual. This serves both lazy-loading (as a failsafe) as well as change tracking via proxies.

So updating the entity definitions to something like:

public class Quiz
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int QuizID { get; set; }
    public string Text { get; set; }
    public virtual User User { get; set; }

    public virtual ICollection<Question> Questions { get; set; } = new List<Question>();
}

public class Question
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int QuestionID { get; set; }
    public string Text { get; set; }

    [ForeignKey("QuizID")] // Creates a shadow property mapped to a QuizID column in table.
    public virtual Quiz Quiz { get; set; }

    public virtual ICollection<Option> Options { get; set; } = new List<Option>();
}

public class User
{
    [Key]
    public int UserID { get; set; }
    public string Username { get; set; }
    public string Password { get; set; }

    public virtual ICollection<Quiz> Quizzes { get; set; } = new List<Quiz>();
}

public class Option
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int OptionID { get; set; }
    public string Choice { get; set; }
    public bool? IsCorrect { get; set; }

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

Now, when it comes to creating your question data you can leverage EF to manage the FKs by populating a complete set of related entities and saving them together.

public async Task<IActionResult> CreateQuiz()
{
    var systemUser = _context.Users.Single(x => x.UserId == 0);
    Quiz quiz = new Quiz();
    quiz.User = systemUser;
    quiz.Text = Request.Form["content"].ToString();

    for (int i = 1; i <= 4; i++)
    {
        Question question = new Question();
        question.Text = Request.Form["title_" + i].ToString(); 
        quiz.Questions.Add(question);

        for (int j = 1; j <= 4; j++)
        {
            Option option = new Option();
            option.Choice = Request.Form["option_a" + i].ToString();
            option.IsCorrect = j == int.Parse(Request.Form["correct_answer_" + i].ToString());
            question.Options.Add(option);
        }
    }

    _context.Quizes.Add(quiz);
    await _context.SaveChangesAsync();
    return RedirectToAction(nameof(Index));
}

This can likely be simplified even more, but I kept it close to the original so it should be easier to follow. Rather that calling SaveChanges several times and trying to set FKs, it is far better to create the objects and associate them with each other. We create our quiz, then go through and create the questions, associating them to the Quiz by adding them to the quiz.Questions collection. Then go through and do the same for the Question Options. Once we are done, we tell the context to add the Quiz to it's Quizes DbSet, and call SaveChanges once at the very end. EF will save all of the entities and their relationships automatically, populating the appropriate FKs. The benefit here is that all of the changes are committed together in one transaction rather than separate saves where a quiz might be saved, but there was an issue with one question or one option, leaving the database in an incomplete state at the point of failure.