由于外键关系,我在删除某些实体时遇到问题。我理解以下错误消息,并已尽我所能想到删除实体而不会出现此错误:
DELETE 语句与 REFERENCE 约束冲突
“FK_QuizUserAnswer_QuizWithQuestion”。冲突发生在
数据库“SomeDatabase”,表“dbo.QuizUserAnswer”,列
'id测验问题'。该语句已终止。
这是相关两个表的图像:
我正在尝试删除有问题的测验实体。我制作了 idQuizQuestion 专栏nullable。因此,外键在 QuizUserAnswer 端可以为空。
在映射文件中,我指定了关系是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);
我已经尝试了很多很多代码片段,所以我将发布代码的当前状态,希望我的意图是明确的:
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();
}
我怎样才能删除这些该死的实体(我快要写一个存储过程了)?