Entity Framework Fluent API配置多对零/一关系报错问题

实体类代码 #

public abstract class Entity
{
    [Key]
    public int Id { get; set; }
}

public class Inventory: Entity
{
    public int Version { get; set; }
    public string Set_num { get; set; }
    [JsonIgnore]
    public virtual ICollection<InventoryPart> InventoryParts { get; set; }
}

public class InventoryPart : Entity
{
    public int Inventory_id { get; set; }
    public string Part_num { get; set; }
    public int Color_id { get; set; }
    public int Quantity { get; set; }
    public string Is_spare { get; set; }
    public string Img_url { get; set; }                        
    [JsonIgnore]
    public virtual UserPart? UserPart { get; set; }
}

public class UserPart : Entity
{                
    public string Part_num { get; set; }          
    public int Color_id { get; set; }        
    public int Quantity { get; set; }
    public string Location { get; set; }   
    [JsonIgnore]
    public virtual ICollection<InventoryPart> InventoryParts { get; set; }
}

当前Fluent API配置代码 #

modelBuilder.Entity<Inventory>()
            .HasMany(b => b.InventoryParts)
            .WithOne()
            .HasForeignKey(lim => lim.Inventory_id);
        
modelBuilder.Entity<InventoryPart>()
            .HasKey(l => new { l.Part_num, l.Color_id }); // 此处主键不唯一！实际唯一键应为Inventory_id, Part_num, Color_id, Is_spare
                
modelBuilder.Entity<UserPart>()
            .HasKey(u => new { u.Part_num, u.Color_id }); // 此处主键唯一

modelBuilder.Entity<UserPart>()
            .HasMany(u => u.InventoryParts)
            .WithOne(l => l.UserPart)
            .HasForeignKey(l => new { l.Part_num, l.Color_id });

查询需求与代码 #

需要过滤Inventory并关联查询其下的InventoryParts及对应的UserPart，当前查询代码如下：

public class GetSetPartsBySpecification : Specification<Inventory>, ISingleResultSpecification
{
    public GetSetPartsBySpecification(string setnum)            
    {
        Query
            .Where(inv => inv.Set_num == setnum)                            
            .Include(ip => ip.InventoryParts)
                .ThenInclude(ip=>ip.UseParts) // 存在笔误，应为UserPart
            .AsSplitQuery();
    }
}

数据与关系要求 #

• Inventory与InventoryParts数据从Excel导入SQLite，Inventory.Id对应InventoryPart.Inventory_id，该值非自动生成，来自导入文件
• 关系规则：
  • InventoryPart与UserPart为多对零/一关系（多个InventoryPart可关联同一个UserPart，也可不关联）
  • UserPart与InventoryPart为一对多关系，但不需要UserPart端保留反向关联的InventoryParts集合

当前问题 #

当前配置中给InventoryPart设置的复合键{Part_num, Color_id}并不唯一，导致EF报错，询问能否基于现有实体结构通过Fluent API正确配置所需关系。

解决方案 #

1. 修正InventoryPart的主键配置 #

首先必须把InventoryPart的主键设置为实际唯一的复合键{Inventory_id, Part_num, Color_id, Is_spare}：

modelBuilder.Entity<InventoryPart>()
    .HasKey(l => new { l.Inventory_id, l.Part_num, l.Color_id, l.Is_spare });

2. 移除UserPart的反向集合（符合需求） #

根据需求，不需要UserPart端的InventoryParts反向集合，直接从UserPart实体类中删除该属性：

// 从UserPart中删除以下代码
[JsonIgnore]
public virtual ICollection<InventoryPart> InventoryParts { get; set; }

3. 正确配置InventoryPart与UserPart的多对零/一关系 #

通过Fluent API明确映射关系，指定WithMany()不带参数，表示不需要反向集合：

// Inventory与InventoryPart的一对多关系保持不变
modelBuilder.Entity<Inventory>()
    .HasMany(b => b.InventoryParts)
    .WithOne()
    .HasForeignKey(lim => lim.Inventory_id)
    .OnDelete(DeleteBehavior.Cascade); // 可根据业务需求调整删除行为

// UserPart的主键配置保持不变
modelBuilder.Entity<UserPart>()
    .HasKey(u => new { u.Part_num, u.Color_id });

// 配置InventoryPart到UserPart的多对零/一关系
modelBuilder.Entity<InventoryPart>()
    .HasOne(ip => ip.UserPart)
    .WithMany() // 空参数表示无需UserPart端的反向集合
    .HasForeignKey(ip => new { ip.Part_num, ip.Color_id })
    .OnDelete(DeleteBehavior.Restrict); // 避免删除UserPart时误删关联的InventoryPart，可按需调整

4. 修正查询代码的笔误 #

将查询中的UseParts改为UserPart：

public class GetSetPartsBySpecification : Specification<Inventory>, ISingleResultSpecification
{
    public GetSetPartsBySpecification(string setnum)            
    {
        Query
            .Where(inv => inv.Set_num == setnum)                            
            .Include(ip => ip.InventoryParts)
                .ThenInclude(ip => ip.UserPart)
            .AsSplitQuery();
    }
}

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