EF Core复合主键下多对多关系配置及LINQ内联查询实现

1. 多对多关系配置 #

由于Company和Customer均采用(ID, Lang)作为复合主键，中间关联表CompanyCustomers需匹配完整复合键进行外键关联，具体配置如下：

步骤1：完善实体类字段与导航属性 #

CompanyCustomer实体类需包含关联双方的复合主键字段及导航属性：

public class CompanyCustomer
{
    public int CompanyID { get; set; }
    public string CompanyLang { get; set; }
    public int CustomerID { get; set; }
    public string CustomerLang { get; set; }

    public Company Company { get; set; }
    public Customer Customer { get; set; }
}

Company和Customer类添加中间表导航属性：

public class Company
{
    public int ID { get; set; }
    public string Lang { get; set; }
    // 其他业务属性
    public ICollection<CompanyCustomer> CompanyCustomers { get; set; } = new List<CompanyCustomer>();
}

public class Customer
{
    public int ID { get; set; }
    public string Lang { get; set; }
    // 其他业务属性
    public ICollection<CompanyCustomer> CompanyCustomers { get; set; } = new List<CompanyCustomer>();
}

步骤2：Fluent API配置 #

根据业务场景选择主键配置：

• 若同一公司与客户在不同语言下属于不同关联，设置复合主键：

modelBuilder.Entity<CompanyCustomer>(entity =>
{
    entity.HasKey(pk => new { pk.CompanyID, pk.CompanyLang, pk.CustomerID, pk.CustomerLang });

    // 关联Company实体
    entity.HasOne(cc => cc.Company)
          .WithMany(c => c.CompanyCustomers)
          .HasForeignKey(cc => new { cc.CompanyID, cc.CompanyLang })
          .OnDelete(DeleteBehavior.Cascade);

    // 关联Customer实体
    entity.HasOne(cc => cc.Customer)
          .WithMany(c => c.CompanyCustomers)
          .HasForeignKey(cc => new { cc.CustomerID, cc.CustomerLang })
          .OnDelete(DeleteBehavior.Cascade);
});

• 若同一公司与客户跨语言为同一关联，保留原主键但需关联完整复合键：

modelBuilder.Entity<CompanyCustomer>(entity =>
{
    entity.HasKey(pk => new { pk.CompanyID, pk.CustomerID });

    entity.HasOne(cc => cc.Company)
          .WithMany(c => c.CompanyCustomers)
          .HasForeignKey(cc => new { cc.CompanyID, cc.CompanyLang })
          .OnDelete(DeleteBehavior.Cascade);

    entity.HasOne(cc => cc.Customer)
          .WithMany(c => c.CompanyCustomers)
          .HasForeignKey(cc => new { cc.CustomerID, cc.CustomerLang })
          .OnDelete(DeleteBehavior.Cascade);
});

• EF Core 5+ 可直接配置无中间实体的多对多：

modelBuilder.Entity<Company>()
    .HasMany(c => c.Customers)
    .WithMany(c => c.Companies)
    .UsingEntity<CompanyCustomer>(
        j => j.HasOne<Customer>().WithMany().HasForeignKey(c => new { c.CustomerID, c.CustomerLang }),
        j => j.HasOne<Company>().WithMany().HasForeignKey(c => new { c.CompanyID, c.CompanyLang }),
        j => j.HasKey(pk => new { pk.CompanyID, pk.CompanyLang, pk.CustomerID, pk.CustomerLang })
    );

此方式下Company和Customer可直接添加ICollection<Customer>和ICollection<Company>导航属性，无需手动维护中间实体。

2. LINQ方法语法实现查询 #

方式1：使用导航属性（推荐） #

利用已配置的导航属性简化查询逻辑：

public IEnumerable<Customer> GetCompanyCustomers(int companyId, string lang)
{
    return _db.Companies
        .Where(company => company.ID == companyId && company.Lang == lang)
        .SelectMany(company => company.CompanyCustomers)
        .Select(cc => cc.Customer)
        .ToList();
}

方式2：显式Join查询 #

若未配置导航属性，可通过多次Join实现关联查询：

public IEnumerable<Customer> GetCompanyCustomers(int companyId, string lang)
{
    return _db.Companies
        .Join(
            _db.CompanyCustomers,
            company => new { company.ID, company.Lang },
            cc => new { cc.CompanyID, cc.CompanyLang },
            (company, cc) => new { cc.CustomerID, cc.CustomerLang }
        )
        .Join(
            _db.Customers,
            cc => new { cc.CustomerID, cc.CustomerLang },
            customer => new { customer.ID, customer.Lang },
            (cc, customer) => customer
        )
        .ToList();
}

特殊场景：中间表无Lang字段 #

若CompanyCustomers仅存储CompanyID和CustomerID，需额外匹配语言条件：

public IEnumerable<Customer> GetCompanyCustomers(int companyId, string lang)
{
    return _db.Companies
        .Where(company => company.ID == companyId && company.Lang == lang)
        .Join(
            _db.CompanyCustomers,
            company => company.ID,
            cc => cc.CompanyID,
            (company, cc) => cc.CustomerID
        )
        .Join(
            _db.Customers.Where(c => c.Lang == lang),
            customerId => customerId,
            customer => customer.ID,
            (id, customer) => customer
        )
        .ToList();
}

内容的提问来源于stack exchange，提问作者Aftab Ahmed Kalhoro