EF Core多对多关联数据无法保存，求问题原因

调用云API获取供应商数据并新增/更新到SQL数据库，数据库中Vendor与VendorClassifications为多对多关系（映射配置如下），但EF Core无法保存两者的关联数据。

API调用正常，返回数据符合预期：每个供应商包含vendor_classifications字段，是一组仅含id和name的供应商分类对象。

实现代码：

var apiVendorClassifications = await _bClient.GetVendorClassifications();
var savedVendorClassifications = _appDbContext.VendorClassifications.ToList();
var apiVendorClassificationsProjected = apiVendorClassifications.Select(x => new Entities.VendorClassification
{
    Id = new Guid(x.id),
    Name = x.name
}).ToList();
foreach (var apiVC in apiVendorClassificationsProjected)
{
    if (savedVendorClassifications.Find(x => x.Id == apiVC.Id) is Entities.VendorClassification found)
    {
        _appDbContext.Entry(found).CurrentValues.SetValues(apiVC);
    }
    else
        _appDbContext.Add(apiVC);
}


var apiVendors = await _bClient.GetVendors();
var savedVendors = _appDbContext.Vendors.ToList();
var apiVendorsProjected = apiVendors.Select(x => new Entities.Vendor
{
    Id = new Guid(x.id),
    IsActive = x.is_active,
    IsVerified = x.address.verified,
    UpdatedAt = x.updated_at,
    ExternalKey = x.external_key,
    Email = x.email,
    IsDeleted = x.deleted,
    Name = x.name,
    Phone = x.phone,
    VendorClassifications = _appDbContext.VendorClassifications.Where(y => x.vendor_classifications.Select(z => new Guid(z.id)).ToList().Contains(y.Id)).ToList()
}).ToList();
foreach (var apiV in apiVendorsProjected)
{
    if (savedVendors.Find(x => x.Id == apiV.Id) is Entities.Vendor found)
    {
        _appDbContext.Entry(found).CurrentValues.SetValues(apiV);
    }
    else
        _appDbContext.Add(apiV);
}

_appDbContext.SaveChanges();

OnModelCreating中的多对多映射配置：

// many-to-many relationship between Vendors and Vendor Classifications as we find in Prism
modelBuilder.Entity<Vendor>().HasMany(e => e.VendorClassifications).WithMany(e => e.Vendors);

核心原因 #

• 更新场景未处理关联关系：CurrentValues.SetValues(apiV)仅更新实体的标量属性，不会处理VendorClassifications这类导航属性，EF Core无法自动感知多对多关联的变化。
• 关联集合逻辑缺失：无论新增还是更新供应商，都没有显式维护关联关系，更新时旧关联未清除、新关联未正确添加，导致关联数据无法被EF Core识别并保存。

解决方案 #

修改供应商同步逻辑，针对新增和更新场景分别处理多对多关联：

var apiVendorClassifications = await _bClient.GetVendorClassifications();
var savedVendorClassifications = _appDbContext.VendorClassifications.ToList();
var apiVendorClassificationsProjected = apiVendorClassifications.Select(x => new Entities.VendorClassification
{
    Id = new Guid(x.id),
    Name = x.name
}).ToList();
foreach (var apiVC in apiVendorClassificationsProjected)
{
    if (savedVendorClassifications.Find(x => x.Id == apiVC.Id) is Entities.VendorClassification found)
    {
        _appDbContext.Entry(found).CurrentValues.SetValues(apiVC);
    }
    else
        _appDbContext.Add(apiVC);
}
// 先保存分类，确保实体被上下文跟踪
await _appDbContext.SaveChangesAsync();

var apiVendors = await _bClient.GetVendors();
var savedVendors = _appDbContext.Vendors.ToList();
// 缓存所有需要的分类，减少重复查询
var allClassificationIds = apiVendors.SelectMany(x => x.vendor_classifications.Select(z => new Guid(z.id))).Distinct().ToList();
var classificationLookup = _appDbContext.VendorClassifications
    .Where(vc => allClassificationIds.Contains(vc.Id))
    .ToDictionary(vc => vc.Id);

foreach (var apiVendor in apiVendors)
{
    var vendorId = new Guid(apiVendor.id);
    var targetVendor = savedVendors.Find(v => v.Id == vendorId);

    if (targetVendor != null)
    {
        // 更新标量属性
        _appDbContext.Entry(targetVendor).CurrentValues.SetValues(new Entities.Vendor
        {
            Id = vendorId,
            IsActive = apiVendor.is_active,
            IsVerified = apiVendor.address.verified,
            UpdatedAt = apiVendor.updated_at,
            ExternalKey = apiVendor.external_key,
            Email = apiVendor.email,
            IsDeleted = apiVendor.deleted,
            Name = apiVendor.name,
            Phone = apiVendor.phone
        });

        // 重置关联：先清除旧关联，再添加新关联
        targetVendor.VendorClassifications.Clear();
        var currentClassificationIds = apiVendor.vendor_classifications.Select(z => new Guid(z.id)).ToList();
        foreach (var id in currentClassificationIds)
        {
            if (classificationLookup.TryGetValue(id, out var classification))
            {
                targetVendor.VendorClassifications.Add(classification);
            }
        }
    }
    else
    {
        // 新增供应商并关联分类
        var newVendor = new Entities.Vendor
        {
            Id = vendorId,
            IsActive = apiVendor.is_active,
            IsVerified = apiVendor.address.verified,
            UpdatedAt = apiVendor.updated_at,
            ExternalKey = apiVendor.external_key,
            Email = apiVendor.email,
            IsDeleted = apiVendor.deleted,
            Name = apiVendor.name,
            Phone = apiVendor.phone,
            VendorClassifications = new List<Entities.VendorClassification>()
        };

        var currentClassificationIds = apiVendor.vendor_classifications.Select(z => new Guid(z.id)).ToList();
        foreach (var id in currentClassificationIds)
        {
            if (classificationLookup.TryGetValue(id, out var classification))
            {
                newVendor.VendorClassifications.Add(classification);
            }
        }

        _appDbContext.Add(newVendor);
    }
}

await _appDbContext.SaveChangesAsync();

关键优化点 #

• 先保存分类实体，确保其被EF Core上下文跟踪，避免关联时出现未跟踪实体的问题。
• 更新供应商时显式重置关联集合，确保关联关系与API数据完全同步。
• 使用字典缓存分类，减少数据库查询次数，提升同步性能。

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