EF多对多关系中避免重复插入及关联记录清理问题咨询

问题修复方案 #

修复Bug1：当checkReq为true时插入新的ExpertisePlan记录 #

根因分析：
原代码中，当checkReq为true时，通过IndustryExpertsModule.Utilities.GetCurrentRequest获取的ExpertisePlan实例来自独立的DbContext，未被当前上下文跟踪。后续关联ExpertisePlanLecturer时，EF会将其视为新实体插入数据库。

修复措施：

1. 直接使用当前上下文查询现有ExpertisePlan，确保实例被上下文跟踪；
2. 直接在跟踪的实体上更新状态，避免跨上下文操作导致的状态不一致。

修复Bug2：添加新记录前删除ExpertisePlanLecturers中的对应记录 #

修复措施：
在创建新关联记录前，根据ExpertisePlanId和emp_num删除现有关联。提供两种策略：

• 仅删除本次传入讲师的关联记录（避免重复）；
• 删除该ExpertisePlan下的所有关联记录（完全替换关联关系）。

完整修复后的代码 #

bool checkReq = IndustryExpertsModule.Utilities.CheckRequest(payload_object.Main.req_ser, payload_object.Main.req_year);
List<Lecturer> newLecturers = new List<Lecturer>();
List<int> lecturerEmpNums = payload_object.Main.expertise_plans.Select(p => int.Parse(p.lecturer.emp_num)).ToList();

using (IndustryExpertCTX ctx = new IndustryExpertCTX())
{
    var existingLecturers = ctx.Lecturers.Where(l => lecturerEmpNums.Contains(l.emp_num)).ToList();

    newLecturers = lecturerEmpNums
        .Except(existingLecturers.Select(l => l.emp_num))
        .Select(lect => {
            var mainExpert = payload_object.Main.expertise_plans.FirstOrDefault(m => m.lecturer.emp_num == lect.ToString());
            return mainExpert != null ? new Lecturer
            {
                emp_num = lect,
                name = mainExpert.lecturer.name,
                birth_date = mainExpert.lecturer.birth_date,
                current_category_id = mainExpert.lecturer.current_category_id,
                current_job = mainExpert.lecturer.current_job,
                Email = mainExpert.lecturer.email,
                employer = mainExpert.lecturer.employer,
                mobile_num = mainExpert.lecturer.mobile_no,
                nationality = mainExpert.lecturer.nationality,
                SSN = mainExpert.lecturer.ssn
            } : null;
        })
        .Where(l => l != null)
        .ToList();

    ExpertisePlan expertisePlan = null;

    if (!checkReq)
    {
        expertisePlan = new ExpertisePlan()
        {
            action_type = short.Parse(payload_object.Main.action_type.ToString()),
            batch_name = payload_object.Main.batch_name,
            batch_num = payload_object.Main.batch_num,
            camp_code = payload_object.Main.camp_code,
            camp_name = payload_object.Main.camp_name,
            compos_instance_id = task.sca.compositeInstanceId,
            confirm_date = DateTime.Now,
            dept_code = depCode,
            dept_name = depName,
            fac_code = payload_object.Main.fac_code,
            fac_name = payload_object.Main.fac_name,
            requester_num = empNum,
            request_date = payload_object.Main.req_date,
            req_ser = payload_object.Main.req_ser,
            req_year = payload_object.Main.req_year,
            state = State.FacDean
        };
        ctx.ExpertisePlans.Add(expertisePlan);
    }
    else
    {
        // 使用当前上下文查询现有计划，确保实例被跟踪
        expertisePlan = ctx.ExpertisePlans.FirstOrDefault(x => x.req_ser == payload_object.Main.req_ser && x.req_year == payload_object.Main.req_year);
        if (expertisePlan != null)
        {
            desc = "اعادة اعتماد لعميد الكلية";
            // 直接更新跟踪实体的状态和时间
            expertisePlan.state = State.FacDean;
            expertisePlan.confirm_date = DateTime.Now;
        }
    }

    // 修复Bug2：删除现有关联记录
    if (checkReq && expertisePlan != null)
    {
        // 选项1：仅删除本次传入讲师的关联记录（避免重复）
        var existingLinks = ctx.ExpertisePlanLecturers
            .Where(ep => ep.ExpertisePlanId == expertisePlan.Id && lecturerEmpNums.Contains(ep.emp_num))
            .ToList();
        
        // 选项2：删除该计划下的所有关联记录（完全替换）
        // var existingLinks = ctx.ExpertisePlanLecturers
        //     .Where(ep => ep.ExpertisePlanId == expertisePlan.Id)
        //     .ToList();
        
        ctx.ExpertisePlanLecturers.RemoveRange(existingLinks);
    }

    var expertisePlanLecturers = existingLecturers.Select(lect => {
        var mainExpert = payload_object.Main.expertise_plans.FirstOrDefault(m => m.lecturer.emp_num == lect.emp_num.ToString());
        return mainExpert != null ? new ExpertisePlanLecturer
        {
            ExpertisePlan = expertisePlan,
            emp_num = lect.emp_num,
            category_id = mainExpert.category_id,
            category_name = mainExpert.category_name,
            notes = mainExpert.notes,
            original = mainExpert.original
        } : null;
    })
    .Where(ep => ep != null)
    .ToList();

    expertisePlanLecturers.AddRange(newLecturers.Select(newLecturer => {
        var mainExpert = payload_object.Main.expertise_plans.FirstOrDefault(m => m.lecturer.emp_num == newLecturer.emp_num.ToString());
        return mainExpert != null ? new ExpertisePlanLecturer
        {
            ExpertisePlan = expertisePlan,
            emp_num = newLecturer.emp_num,
            category_id = mainExpert.category_id,
            category_name = mainExpert.category_name,
            notes = mainExpert.notes,
            original = mainExpert.original
        } : null;
    })
    .Where(ep => ep != null));

    ctx.Lecturers.AddRange(newLecturers);
    ctx.ExpertisePlanLecturers.AddRange(expertisePlanLecturers);
    ctx.SaveChanges();
}

额外优化点 #

• 提前过滤null值，减少后续无效操作；
• 统一使用当前上下文进行所有数据操作，避免跨上下文实体跟踪问题。

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