ASP.NET中如何修改带外键约束的数据库？Survey表更新外键冲突求解

• 操作顺序完全颠倒：SurveyOption的SurveyQuestionId是关联SurveyQuestions表主键的外键，约束要求该字段值必须是SurveyQuestions表中已存在的记录ID。你当前代码先处理子表SurveyOption的新增/修改并提交，之后才处理父表SurveyQuestions的更新提交。如果是新增问题、或者问题ID未提前持久化的场景，子表提交时父表对应记录还不存在，直接触发外键冲突。
• 额外风险点：代码中直接修改SurveyQuestions的主键SurveyQuestionsId，主键是唯一标识不允许随意更新，会引发数据关联混乱；多次高频调用SaveChanges没有事务包裹，中间操作失败会出现数据不一致。

调整操作顺序，先持久化父表问题数据，再处理子表选项数据，修正后代码如下：

// 开启事务保证数据一致性
using (var transaction = _mcniDbContext.Database.BeginTransaction())
{
    try
    {
        foreach (var question in questionList)
        {
            // 第一步：先处理父表SurveyQuestions
            var questionData = _mcniDbContext.SurveyQuestions.FirstOrDefault(e => e.SurveyQuestionsId == question.SurveyQuestionsId);
            if (questionData == null)
            {
                // 新增问题场景
                questionData = new SurveyQuestions()
                {
                    SurveyQuestionsId = question.SurveyQuestionsId,
                    SurveyMasterId = surveyMasterId,
                    QuestionText = question.QuestionText,
                    QuestionType = question.QuestionType,
                    Required = question.Required,
                    ExplanationLink = question.ExplanationLink
                };
                _mcniDbContext.SurveyQuestions.Add(questionData);
            }
            else
            {
                // 禁止修改主键SurveyQuestionsId
                questionData.SurveyMasterId = surveyMasterId;
                questionData.QuestionText = question.QuestionText;
                questionData.QuestionType = question.QuestionType;
                questionData.Required = question.Required;
                questionData.ExplanationLink = question.ExplanationLink;
                _mcniDbContext.Entry(questionData).State = EntityState.Modified;
            }
            // 先提交父表，保证问题ID已经存在于数据库
            _mcniDbContext.SaveChanges();

            // 第二步：再处理子表SurveyOptions
            var options = question.OptionsList;
            foreach (var option in options)
            {
                // 强制绑定当前问题ID，避免前端传值错误
                option.SurveyQuestionId = question.SurveyQuestionsId;
                
                var optionData = _mcniDbContext.SurveyOptions.FirstOrDefault(e => e.SurveyOptionId == option.SurveyOptionId);
                if (optionData == null)
                {
                    _mcniDbContext.SurveyOptions.Add(new SurveyOptions()
                    {
                        OptionValue = option.OptionValue,
                        Description = option.Description,
                        Sequence = option.Sequence,
                        SurveyOptionId = option.SurveyOptionId,
                        SurveyQuestionId = option.SurveyQuestionId,
                        ImageId = option.ImageId
                    });
                }
                else
                {
                    // 禁止修改主键SurveyOptionId
                    optionData.SurveyQuestionId = option.SurveyQuestionId;
                    optionData.Sequence = option.Sequence;
                    optionData.OptionValue = option.OptionValue;
                    optionData.Description = option.Description;
                    optionData.ImageId = option.ImageId;
                    _mcniDbContext.Entry(optionData).State = EntityState.Modified;
                }
            }
            // 批量提交当前问题的所有选项修改
            _mcniDbContext.SaveChanges();
        }
        // 所有操作完成提交事务
        transaction.Commit();
    }
    catch
    {
        // 出错回滚所有修改
        transaction.Rollback();
        throw;
    }
}

• 新增事务包裹，所有操作要么全部成功要么全部回滚，避免残留无效数据
• 调整父子表操作顺序，父表先提交保证外键合法性
• 主动给Option赋值当前Question的ID，避免前端传错关联ID引发的异常
• 移除主键修改操作，避免主键冲突
• 减少SaveChanges调用次数，降低数据库IO开销

内容的提问来源于stack exchange，提问作者Anirudh Singh Rawat