实体框架错误删除具有外键关系的实体 [英] Entity Framework Error Deleting Entity with Foreign Key Relationship

问题描述

由于外键关系，我在删除某些实体时遇到问题.我了解以下错误消息，并且已经在尽我所能想到的一切来删除实体，而不会发生此错误:

I am having a problem deleting some entities due to a foreign key relationship. I understand the following error message and have been doing everything I can think of to delete the entities without incurring this error:

DELETE语句与REFERENCE约束冲突 "FK_QuizUserAnswer_QuizWithQuestion".发生冲突 数据库"SomeDatabase"，表"dbo.QuizUserAnswer"，列 'idQuizQuestion'.该声明已终止.

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 an image of the two tables in question:

我正在尝试删除 QuizWithQuestion 实体.我已将idQuizQuestion列设置为 nullable .因此，外键在QuizUserAnswer端为空. 在映射文件中，我已指定该关系为可选:

I am trying to delete QuizWithQuestion entities. I have made the idQuizQuestion column nullable. So, the foreign key is nullable on the QuizUserAnswer side. In the mapping files, I have specified that the relationship is optional:

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 have tried many, many snippets of code, so I will post the current state of the code in the hope that my intention 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();
    }

另一种尝试是这样的:

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

如何删除这些织补实体(我非常接近编写存储过程)?

How can I delete these darn entities (I'm so close to writing a stored procedure)?

推荐答案

由于您已经有要删除的问题ID，因此应该可以执行以下操作:

Since you already have the question ids to delete, something like this should work:

// 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();

如果将QuizUserAnswer实体加载到上下文中(这是include应该执行的操作)，则Entity Framework应该处理将外键设置为null的情况.

If the QuizUserAnswer entities are loaded into the context (which is what include should do), Entity Framework should handle setting the foreign keys to null.

这篇关于实体框架错误删除具有外键关系的实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！