EF关联表更新异常：Include查询后无法更新已有记录

问题根源 #

当前代码无论传入的MeetingPollingQuestionId是否存在，都会创建新的EFModel.MeetingPollingQuestion实例并调用Add方法，EF将其标记为新增实体而非更新已有实体，导致即使ID存在也会生成新记录。

修改方案 #

需要先判断目标Question是否存在，分别处理更新和新增逻辑，同时同步处理关联的子实体（MeetingPollingParts及MeetingPollingPartsValues）：

public ResultStatus SaveMeetingPollingQuestion(Model.MeetingPollingQuestion mpq)
{
    using (var db = new NccnEcommerceEntities())
    {
        using (DbContextTransaction dbTran = db.Database.BeginTransaction())
        {
            ResultStatus result = new ResultStatus();
            try
            {
                var meetingPolling = db.MeetingPollings
                    .Include(x => x.MeetingPollingQuestions)
                    .Include(x => x.MeetingPollingQuestions.Select(q => q.MeetingPollingParts))
                    .Single(x => x.MeetingPollingId == mpq.MeetingPollingId);

                // 查找是否存在已有Question
                var existingQuestion = meetingPolling.MeetingPollingQuestions
                    .FirstOrDefault(q => q.MeetingPollingQuestionId == mpq.MeetingPollingQuestionId);

                if (existingQuestion != null)
                {
                    // 更新已有Question的基本属性
                    existingQuestion.MeetingPollingQuestionTypeId = mpq.MeetingPollingQuestionTypeId;
                    existingQuestion.SequenceOrder = mpq.SequenceOrder;

                    // 处理关联的MeetingPollingParts：先删除旧记录，再添加新记录
                    // 若需要精细更新（而非全删全加），需额外基于PartID对比处理
                    db.MeetingPollingParts.RemoveRange(existingQuestion.MeetingPollingParts);
                    existingQuestion.MeetingPollingParts = mpq.MeetingPollingParts.Select(p => new EFModel.MeetingPollingPart
                    {
                        MeetingPollingPartsTypeId = p.MeetingPollingPartsTypeId,
                        MeetingPollingPartsValues = p.MeetingPollingPartsValues.Select(v => new EFModel.MeetingPollingPartsValue
                        {
                            QuestionValue = v.QuestionValue,
                            MeetingPollingPartsValuesTypeId = v.MeetingPollingPartsValuesTypeId
                        }).ToList()
                    }).ToList();
                }
                else
                {
                    // 新增Question：仅当不存在时创建并添加
                    var newMeetingPollingQuestion = new EFModel.MeetingPollingQuestion
                    {
                        MeetingPollingQuestionId = mpq.MeetingPollingQuestionId,
                        MeetingPollingId = mpq.MeetingPollingId,
                        MeetingPollingQuestionTypeId = mpq.MeetingPollingQuestionTypeId,
                        SequenceOrder = mpq.SequenceOrder,
                        MeetingPollingParts = mpq.MeetingPollingParts.Select(p => new EFModel.MeetingPollingPart
                        {
                            MeetingPollingPartsTypeId = p.MeetingPollingPartsTypeId,
                            MeetingPollingPartsValues = p.MeetingPollingPartsValues.Select(v => new EFModel.MeetingPollingPartsValue
                            {
                                QuestionValue = v.QuestionValue,
                                MeetingPollingPartsValuesTypeId = v.MeetingPollingPartsValuesTypeId
                            }).ToList()
                        }).ToList()
                    };
                    meetingPolling.MeetingPollingQuestions.Add(newMeetingPollingQuestion);
                }

                db.SaveChanges();
                dbTran.Commit();
                result.ResultCode = Convert.ToInt32(Enums.Result.Code.Success);
                result.Message = "Successfully uploaded.";
            }
            catch (Exception e)
            {
                dbTran.Rollback();
                result.Message = "Error Save Meeting Polling Question" + e.Message;
                result.ResultCode = Convert.ToInt32(Enums.Result.Code.Error);
            }
            return result;
        }
    }
}

关键改动说明 #

• 判断实体存在性：通过FirstOrDefault从已加载的集合中查找目标Question，避免额外查询数据库
• 更新属性：直接修改已有实体的属性，EF会自动跟踪变化并生成更新语句
• 处理子实体：删除旧的关联Parts后添加新实例，确保子数据与传入模型同步；若需要更高效的更新（如仅修改变化的Part），可基于MeetingPollingPartId做对比更新
• 仅新增时创建实体：避免重复添加导致的新增记录

内容的提问来源于stack exchange，提问作者Jefferson