EF Core 7 同一实体多关系配置错误求助

一个Item会被分配两个不同角色的ServiceCompany（分销公司和制造公司），而一个ServiceCompany需要拥有其被分配的所有Item的列表，用于权限控制。实体类定义如下：

public class ServiceCompany
{
    public Guid Id { get; set; } = Guid.NewGuid();
    public string Name { get; set; } = default!;
    public List<Item> Items { get; set; } = new List<Item>();
}

public class Item
{
    public Guid Id { get; set; } = Guid.NewGuid();
    public string Name { get; set; } = default!;
    public Guid? DistributionCompanyId { get; set; }
    public ServiceCompany? DistributionCompany { get; set; }
    public Guid? ManufacturingCompanyId { get; set; }
    public ServiceCompany? ManufacturingCompany { get; set; }
}

尝试配置多关系时使用了如下代码：

public void Configure(EntityTypeBuilder<Item> builder)
{
    builder.HasOne(x => x.DistributionCompany)
        .WithMany(x => x.Items)
        .HasForeignKey(x => x.DistributionCompanyId)
        .HasConstraintName("FK_Items_Companies_DistributionCompanyId");

    builder.HasOne(x => x.ManufacturingCompany)
        .WithMany(x => x.Items)
        .HasForeignKey(x => x.ManufacturingCompanyId)
        .HasConstraintName("FK_Items_Companies_ManufacturingCompanyId");
}

出现错误：

System.InvalidOperationException : Cannot create a relationship between 'ServiceCompany.Items' and 'Item.ManufacturingCompany' because a relationship already exists between 'ServiceCompany.Items' and 'Item.DistributionCompany'. Navigations can only participate in a single relationship. If you want to override an existing relationship call 'Ignore' on the navigation 'Item.ManufacturingCompany' first in 'OnModelCreating'.

要求不能新增实体类型，也不能在Item侧新增集合，需实现多关系配置。

错误核心原因 #

EF Core中每个导航属性只能绑定一个关系，ServiceCompany.Items已经被DistributionCompany的关系占用，无法再重复绑定到ManufacturingCompany的关系上。要实现ServiceCompany获取所有关联Item的需求，需通过查询合并数据，而非直接用一个集合映射两个外键。

方案一：将Items设为非映射属性，通过查询获取所有关联Item #

修改ServiceCompany类，给Items添加[NotMapped]标记（表示该属性不映射到数据库表），然后通过查询合并两种角色的Item：

public class ServiceCompany
{
    public Guid Id { get; set; } = Guid.NewGuid();
    public string Name { get; set; } = default!;
    
    [NotMapped]
    public List<Item> Items { get; set; } = new List<Item>();
}

配置两个单向关系（不指定ServiceCompany侧的反向导航）：

public void Configure(EntityTypeBuilder<Item> builder)
{
    // 配置分销公司关系
    builder.HasOne(x => x.DistributionCompany)
        .WithMany() // 不指定反向导航集合
        .HasForeignKey(x => x.DistributionCompanyId)
        .HasConstraintName("FK_Items_Companies_DistributionCompanyId")
        .OnDelete(DeleteBehavior.Restrict); // 根据业务需求设置删除行为

    // 配置制造公司关系
    builder.HasOne(x => x.ManufacturingCompany)
        .WithMany() // 不指定反向导航集合
        .HasForeignKey(x => x.ManufacturingCompanyId)
        .HasConstraintName("FK_Items_Companies_ManufacturingCompanyId")
        .OnDelete(DeleteBehavior.Restrict);
}

在业务逻辑中查询所有关联Item并填充：

var targetCompany = await _context.ServiceCompanies
    .FirstOrDefaultAsync(sc => sc.Id == targetCompanyId);

if (targetCompany != null)
{
    targetCompany.Items = await _context.Items
        .Where(i => i.DistributionCompanyId == targetCompanyId || i.ManufacturingCompanyId == targetCompanyId)
        .ToListAsync();
}

方案二：保留一个双向导航，查询补充另一种角色的Item #

如果你希望保留其中一个关系的双向导航（比如分销公司），可以这样配置：

public void Configure(EntityTypeBuilder<Item> builder)
{
    // 分销公司双向导航，对应ServiceCompany.Items
    builder.HasOne(x => x.DistributionCompany)
        .WithMany(x => x.Items)
        .HasForeignKey(x => x.DistributionCompanyId)
        .HasConstraintName("FK_Items_Companies_DistributionCompanyId")
        .OnDelete(DeleteBehavior.Restrict);

    // 制造公司单向导航，无反向集合
    builder.HasOne(x => x.ManufacturingCompany)
        .WithMany() // 不指定反向导航
        .HasForeignKey(x => x.ManufacturingCompanyId)
        .HasConstraintName("FK_Items_Companies_ManufacturingCompanyId")
        .OnDelete(DeleteBehavior.Restrict);
}

此时ServiceCompany.Items仅包含作为分销公司关联的Item，需额外查询制造公司关联的Item并合并：

var targetCompany = await _context.ServiceCompanies
    .Include(sc => sc.Items) // 加载分销关联的Item
    .FirstOrDefaultAsync(sc => sc.Id == targetCompanyId);

if (targetCompany != null)
{
    var manufacturingItems = await _context.Items
        .Where(i => i.ManufacturingCompanyId == targetCompanyId)
        .ToListAsync();
    
    targetCompany.Items.AddRange(manufacturingItems);
}

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