EF Core中从Personel的Eblaghs提取Vahed到School表时出现跟踪冲突错误

错误核心是重复添加了复合键相同的PersonelSchool实例：

• 同一个Personel的多个Eblagh可能包含相同的Vahed值，导致循环中多次创建PersonelId + SchoolId完全一致的关联实体
• EF Core的上下文跟踪机制会检测到同一个复合键的实体被多次添加，从而抛出跟踪冲突异常

1. 避免重复添加相同关联（基础修正） #

在处理每个Personel时，先记录已关联的SchoolId，跳过重复的Vahed值：

var personelsWithEblagh = 
    await db.Personels
            .Where(x => x.AreaId == Area.Id)
            .Include(p => p.Eblaghs)
            // 预加载已有关联，避免遗漏数据库中已存在的记录
            .Include(p => p.PersonelSchools)
            .ToListAsync();

foreach (var personel in personelsWithEblagh)
{
    // 用HashSet存储已关联的SchoolId，快速判断重复
    var linkedSchoolIds = personel.PersonelSchools.Select(ps => ps.SchoolId).ToHashSet();
    
    foreach (var eblagh in personel.Eblaghs)
    {
        var schoolName = eblagh.Vahed;
        if (string.IsNullOrWhiteSpace(schoolName))
            continue;

        var existingSchool = await db.Schools.FirstOrDefaultAsync(s => s.Name == schoolName);

        if (existingSchool == null)
        {
            existingSchool = new School { Name = schoolName };
            db.Schools.Add(existingSchool);
        }

        // 仅当该关联不存在时才添加
        if (!linkedSchoolIds.Contains(existingSchool.Id))
        {
            personel.PersonelSchools.Add(new PersonelSchool
            {
                PersonelId = personel.Id,
                SchoolId = existingSchool.Id
            });
            linkedSchoolIds.Add(existingSchool.Id);
        }
    }
}

// 统一提交所有变更，减少数据库交互次数
await db.SaveChangesAsync();

2. 批量优化（提升性能） #

一次性处理所有School的查询和创建，避免循环中多次访问数据库：

// 第一步：提取目标区域下所有不重复的Vahed值
var targetVaheds = await db.Personels
    .Where(x => x.AreaId == Area.Id)
    .SelectMany(p => p.Eblaghs.Select(e => e.Vahed))
    .Where(v => !string.IsNullOrWhiteSpace(v))
    .Distinct()
    .ToListAsync();

// 第二步：批量获取已有School，创建新增School
var existingSchools = await db.Schools
    .Where(s => targetVaheds.Contains(s.Name))
    .ToListAsync();

var newSchoolNames = targetVaheds.Except(existingSchools.Select(s => s.Name)).ToList();
var newSchools = newSchoolNames.Select(name => new School { Name = name }).ToList();
db.Schools.AddRange(newSchools);
await db.SaveChangesAsync();

// 建立名称到Id的映射，快速查找
var schoolNameMap = existingSchools.Concat(newSchools)
    .ToDictionary(school => school.Name, school => school.Id);

// 第三步：处理Personel与School的关联，避免重复
var personels = await db.Personels
    .Where(x => x.AreaId == Area.Id)
    .Include(p => p.PersonelSchools)
    .Include(p => p.Eblaghs)
    .ToListAsync();

foreach (var personel in personels)
{
    var linkedSchoolIds = personel.PersonelSchools.Select(ps => ps.SchoolId).ToHashSet();
    // 去重当前Personel的Vahed值
    var uniqueVaheds = personel.Eblaghs.Select(e => e.Vahed)
        .Where(v => !string.IsNullOrWhiteSpace(v) && schoolNameMap.ContainsKey(v))
        .Distinct();

    foreach (var vahed in uniqueVaheds)
    {
        var schoolId = schoolNameMap[vahed];
        if (!linkedSchoolIds.Contains(schoolId))
        {
            personel.PersonelSchools.Add(new PersonelSchool
            {
                PersonelId = personel.Id,
                SchoolId = schoolId
            });
            linkedSchoolIds.Add(schoolId);
        }
    }
}

await db.SaveChangesAsync();

3. 跟踪状态校验（可选排查） #

如果仍有异常，可以提前检查上下文是否已跟踪该关联实体：

// 添加前检查本地跟踪的实体
var trackedAssociation = db.PersonelSchools.Local
    .FirstOrDefault(ps => ps.PersonelId == personel.Id && ps.SchoolId == existingSchool.Id);

if (trackedAssociation == null && !linkedSchoolIds.Contains(existingSchool.Id))
{
    personel.PersonelSchools.Add(new PersonelSchool
    {
        PersonelId = personel.Id,
        SchoolId = existingSchool.Id
    });
}

• 复合键PersonelSchool(PersonelId, SchoolId)要求人员与学校的组合唯一，必须严格避免重复添加
• 减少SaveChangesAsync()调用次数，批量提交变更可提升性能，同时避免上下文跟踪频繁变化引发的异常
• 预加载已有关联关系，防止遗漏数据库中已存在的关联记录

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