Entity Framework Error Deleting Entity with Foreign Key Relationship

entity-framework entity-framework-6 poco

Question

A foreign key connection is preventing me from completely removing some entities. I am aware of the following error message, and I have taken all reasonable steps to avoid it by deleting the entities:

The DELETE statement conflicted with the REFERENCE constraint "FK_QuizUserAnswer_QuizWithQuestion". The conflict occurred in database "SomeDatabase", table "dbo.QuizUserAnswer", column 'idQuizQuestion'. The statement has been terminated.

Here is a picture of the two concerned tables:

enter image description here

I want to get rid of QuizWithQuestion entities. The idQuizQuestion column is now nullable, as I've made it. Therefore, on the QuizUserAnswer side, the foreign key is nullable. I have indicated that the relationship is optional in the mapping files:

HasMany(t => t.QuizUserAnswers)
    .WithOptional(t => t.QuizWithQuestion)
    .HasForeignKey(t => t.idQuizQuestion);

HasOptional(t => t.QuizWithQuestion)
    .WithMany(t => t.QuizUserAnswers)
    .HasForeignKey(d => d.idQuizQuestion);

I've tried a ton of different code snippets, so I'll post the one that works right now in the hopes that my aim is clear:

    public void RemoveQuestionsFromQuiz(IEnumerable<int> deletedQuestions, int quizId)
    {
        var quiz = // code which retrieves quiz

        foreach (var deletedQuestion in deletedQuestions)
        {
            var quizWithQuestion = quiz.QuizWithQuestions.FirstOrDefault(q => q.Id == deletedQuestion);

            if (!ReferenceEquals(null, quizWithQuestion))
            {
                db.Entry(quizWithQuestion).State = EntityState.Deleted;                    
            }
        }
        db.SaveChanges();
    }

An additional effort looks like this:

public void RemoveQuestionsFromQuiz(IEnumerable<int> deletedQuestions, int quizId)
{
    var quiz = // code which retrieves quiz

    foreach (var deletedQuestion in deletedQuestions)
    {
        var quizWithQuestion = quiz.QuizWithQuestions.FirstOrDefault(q => q.Id == deletedQuestion);

        if (!ReferenceEquals(null, quizWithQuestion))
        {
            foreach (var quizUserAnswer in quizWithQuestion.QuizUserAnswers)
            {
                quizUserAnswer.idQuizQuestion = null; // nullable
                quizWithQuestion.QuizUserAnswers.Remove(quizUserAnswer);
                db.Entry(quizUserAnswer).State = EntityState.Modified;
            }

            quiz.QuizWithQuestions.Remove(quizWithQuestion);

            db.Entry(quizWithQuestion).State = EntityState.Deleted;
        }
    }
    _db.SaveChanges();
}

I'm so close to building a stored procedure; how can I get rid of these annoying entities?

1
3
5/20/2015 1:52:19 PM

Accepted Answer

Something like this ought to function since you already have the question ids to delete:

// assuming db is your DbContext
var questions = db.QuizWithQuestions
                  .Where(q => deletedQuestions.Contains(q.Id))
                  .Include(q => q.QuizUserAnswers);

// assuming this is your DbSet
db.QuizWithQuestions.RemoveRange(questions);

db.SaveChanges();

If theQuizUserAnswer Entity Framework should take care of setting the foreign keys to null once entities have been loaded into the context (which is what include should do).

4
5/21/2015 12:42:51 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow