在Entity Framework中如何基于ILKODU关联Doctor与City实体？

问题场景 #

现有C#实体类定义如下：

public class City:IEntity
{
    public int ID { get; set; }
    public string ILKODU { get; set; }
    public string ILADI { get; set; }
}
        
public class Doctor : IEntity
{
    public int ID { get; set; }
    public string KIMLIKNO { get; set; }
    public string UNVANI { get; set; }
    public string ADSOYAD { get; set; }
    public int? UZMANLIK1ID { get; set; }
    public int? UZMANLIK2ID { get; set; }
    public int? UNITE1ID { get; set; }
    public int? UNITE2ID { get; set; }
    public int? UNITE3ID { get; set; }
    public int? BRICKID { get; set; }
    public string CEPTELEFONU { get; set; }
    public string EMAIL { get; set; }
    public string SABITELEFON { get; set; }
    public string? ILKODU { get; set; }
    public bool? AKTIF { get; set; }

    //public int? CITYID { get; set; }
    //[NotMapped]
    public virtual City? CITY { get; set; }
    public virtual Expertise? UZMANLIK1 { get; set; }
    public virtual Expertise? UZMANLIK2 { get; set; }
    public virtual Unit? UNITE1 { get; set; }
    public virtual Unit? UNITE2 { get; set; }
    public virtual Unit? UNITE3 { get; set; }
    public virtual Brick? BRICK { get; set; }
}

当前查询代码：

var result = await _doctorDal.GetAll(index: pageRequest.PageIndex,
        size: pageRequest.PageSize,
        include: d => d.Include(d => d.BRICK)
                    .Include(d => d.UZMANLIK1)
                    .Include(d => d.UZMANLIK2)
                    .Include(d => d.UNITE1)
                    .Include(d => d.UNITE2)
                    .Include(d => d.UNITE3));

需求：希望使用Entity Framework的Include方法，通过Doctor与City实体的ILKODU字段进行关联查询，但Doctor对应的数据库表中无CITYID等外键字段，该如何实现？

解决方案 #

要实现这种无传统外键的关联查询，需要通过EF的Fluent API手动配置两个实体之间的关联关系，具体步骤如下：

1. 配置实体间的关联关系 #

在你的DbContext的OnModelCreating方法中，添加以下配置，指定Doctor和City通过ILKODU字段建立关联：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 配置Doctor与City的关联：通过ILKODU字段匹配
    modelBuilder.Entity<Doctor>()
        .HasOne(d => d.CITY)
        .WithMany() // 如果City没有反向导航属性，就用WithMany()
        .HasForeignKey(d => d.ILKODU)
        .HasPrincipalKey(c => c.ILKODU);
}

• HasOne(d => d.CITY)：表示Doctor实体有一个City导航属性
• WithMany()：表示City实体没有对应的集合导航属性（如果有的话可以传入对应的属性）
• HasForeignKey(d => d.ILKODU)：指定Doctor的ILKODU作为外键字段
• HasPrincipalKey(c => c.ILKODU)：指定City的ILKODU作为关联匹配的主健字段（City本身的主键仍是ID）

2. 修改查询代码，Include关联的City #

配置完成后，就可以在查询中直接IncludeCITY导航属性了：

var result = await _doctorDal.GetAll(index: pageRequest.PageIndex,
        size: pageRequest.PageSize,
        include: d => d.Include(d => d.BRICK)
                    .Include(d => d.UZMANLIK1)
                    .Include(d => d.UZMANLIK2)
                    .Include(d => d.UNITE1)
                    .Include(d => d.UNITE2)
                    .Include(d => d.UNITE3)
                    .Include(d => d.CITY)); // 添加这一行

注意事项 #

• 确保Doctor和City表中的ILKODU字段数据类型一致（都是string），且数据格式匹配，否则关联会失效
• 如果City的ILKODU字段不是唯一的，这种关联可能会导致数据重复或查询异常，建议确保ILKODU在City表中是唯一的，可以在配置中添加约束：

modelBuilder.Entity<City>()
    .HasIndex(c => c.ILKODU)
    .IsUnique();

内容的提问来源于stack exchange，提问作者Mesut Kızılay