如何将AspNetUsers表Id作为外键关联Hospital表的CreatedBy等字段

1. 修改Common基类，添加关联导航属性 #

在Common类中新增三个指向ApplicationUser的导航属性，和已有的CreatedBy/EditedBy/DeletedBy外键字段对应：

public class Common
{
    public DateTime? CreatedOn { get; set; }        
    public string CreatedBy { get; set; }
    // 对应创建操作人导航属性
    public ApplicationUser? CreatedUser { get; set; }

    public DateTime? EditedOn { get; set; }        
    public string EditedBy { get; set; }
    // 对应编辑操作人导航属性
    public ApplicationUser? EditedUser { get; set; }

    public DateTime? DeletedOn { get; set; }        
    public string DeletedBy { get; set; }
    // 对应删除操作人导航属性
    public ApplicationUser? DeletedUser { get; set; }

    public bool? IsDeleted { get; set; } = false;
    public bool? IsActive { get; set; } = true;        
}

2. 在DbContext中配置外键约束 #

修改ApplicationDbContext的OnModelCreating方法，配置三个外键和AspNetUsers表主键的关联关系，同时避免级联删除冲突：

protected override void OnModelCreating(ModelBuilder builder)
{
    // 必须先调用父类方法，否则Identity相关表的默认配置会丢失
    base.OnModelCreating(builder);

    // 配置CreatedBy关联AspNetUsers.Id
    builder.Entity<Hospital>()
        .HasOne(h => h.CreatedUser)
        .WithMany()
        .HasForeignKey(h => h.CreatedBy)
        .OnDelete(DeleteBehavior.Restrict);

    // 配置EditedBy关联AspNetUsers.Id
    builder.Entity<Hospital>()
        .HasOne(h => h.EditedUser)
        .WithMany()
        .HasForeignKey(h => h.EditedBy)
        .OnDelete(DeleteBehavior.Restrict);

    // 配置DeletedBy关联AspNetUsers.Id
    builder.Entity<Hospital>()
        .HasOne(h => h.DeletedUser)
        .WithMany()
        .HasForeignKey(h => h.DeletedBy)
        .OnDelete(DeleteBehavior.Restrict);
}

配置完成后执行Add-Migration 加外键关联、Update-Database命令即可更新数据库生成外键约束。

3. 关联查询示例 #

查询医院详情时加载对应的操作用户信息，就能直接获取操作人名称：

var hospitalInfo = await _context.Hospitals
    .Include(h => h.CreatedUser)
    .Include(h => h.EditedUser)
    .Include(h => h.DeletedUser)
    .FirstOrDefaultAsync(h => h.HospitalId == 你要查询的医院ID);

// 直接取对应操作人名称
var createUserName = hospitalInfo?.CreatedUser?.UserName;
var editUserName = hospitalInfo?.EditedUser?.UserName;
var deleteUserName = hospitalInfo?.DeletedUser?.UserName;

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