EF Core(Oracle)：将两张不同表映射为单个实体的实现方案

1. 定义合并后的TimeRegistration实体 #

整合原Registration（对应PUNTERING表）和DetailedRegistration（对应DEELPUNTERING表）的所有字段，包含两张表的关联键（例如PUNTERING的主键ID、DEELPUNTERING的外键PUNTERING_ID及自身主键DEELPUNTERING_ID）：

public class TimeRegistration
{
    // PUNTERING表字段
    public int Id { get; set; }
    public DateTime RegistrationDate { get; set; }
    public string EmployeeId { get; set; }
    // 添加其他PUNTERING表的字段

    // DEELPUNTERING表字段
    public int DeelpunteringId { get; set; }
    public decimal HoursWorked { get; set; }
    public string TaskDescription { get; set; }
    // 添加其他DEELPUNTERING表的字段
}

2. 配置DbContext实体映射 #

根据两张表的关联关系（一对一/一对多）选择对应配置方式：

场景1：一对一关联（单PUNTERING对应单DEELPUNTERING） #

使用EF Core的表拆分特性，将单实体映射到两张共享关联键的表：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<TimeRegistration>(entity =>
    {
        // 基础映射到PUNTERING表，指定主键
        entity.ToTable("PUNTERING");
        entity.HasKey(t => t.Id);
        entity.Property(t => t.Id).HasColumnName("ID");
        entity.Property(t => t.RegistrationDate).HasColumnName("REGISTRATION_DATE");
        entity.Property(t => t.EmployeeId).HasColumnName("EMPLOYEE_ID");
        // 映射剩余PUNTERING字段

        // 扩展映射到DEELPUNTERING表，配置关联
        entity.Property(t => t.DeelpunteringId).HasColumnName("DEELPUNTERING_ID");
        entity.Property(t => t.HoursWorked).HasColumnName("HOURS_WORKED");
        entity.Property(t => t.TaskDescription).HasColumnName("TASK_DESCRIPTION");
        // 映射剩余DEELPUNTERING字段

        // 配置表拆分的关联约束（假设DEELPUNTERING通过PUNTERING_ID关联到PUNTERING的ID）
        entity.HasOne<TimeRegistration>().WithOne()
              .HasForeignKey<TimeRegistration>(t => t.DeelpunteringId)
              .HasPrincipalKey<TimeRegistration>(t => t.Id)
              .IsRequired();
        entity.ToTable("DEELPUNTERING");
    });
}

场景2：一对多关联（单PUNTERING对应多DEELPUNTERING） #

通过EF Core的虚拟视图映射，将两张表的关联查询直接绑定到实体：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<TimeRegistration>(entity =>
    {
        // 定义虚拟视图，关联两张表的查询
        entity.ToView("TimeRegistrationView", query =>
            query.FromSqlRaw(@"
                SELECT p.ID, p.REGISTRATION_DATE, p.EMPLOYEE_ID,
                       d.DEELPUNTERING_ID, d.HOURS_WORKED, d.TASK_DESCRIPTION
                FROM PUNTERING p
                INNER JOIN DEELPUNTERING d ON p.ID = d.PUNTERING_ID
            "));

        // 配置组合主键（因为一对多场景下单个ID无法唯一标识实体）
        entity.HasKey(t => new { t.Id, t.DeelpunteringId });

        // 字段列名映射（需与数据库列名一致，Oracle默认大写）
        entity.Property(t => t.Id).HasColumnName("ID");
        entity.Property(t => t.RegistrationDate).HasColumnName("REGISTRATION_DATE");
        // 映射剩余字段
    });
}

3. 清理冗余代码 #

• 删除原Registration、DetailedRegistration实体类
• 移除DbContext中对应的DbSet<Registration>和DbSet<DetailedRegistration>，仅保留：

public class YourDbContext : DbContext
{
    public DbSet<TimeRegistration> TimeRegistrations { get; set; }

    // 其他DbSet及配置
}

4. 增删改操作补充（针对虚拟视图场景） #

如果使用虚拟视图映射且需要支持写操作，需手动配置SQL命令：

// 配置插入命令
entity.HasInsertCommand((entity, cmd) =>
{
    cmd.Sql(@"
        INSERT INTO PUNTERING (ID, REGISTRATION_DATE, EMPLOYEE_ID)
        VALUES ({0}, {1}, {2});
        INSERT INTO DEELPUNTERING (DEELPUNTERING_ID, PUNTERING_ID, HOURS_WORKED, TASK_DESCRIPTION)
        VALUES ({3}, {0}, {4}, {5});
    ", entity.Id, entity.RegistrationDate, entity.EmployeeId, entity.DeelpunteringId, entity.HoursWorked, entity.TaskDescription);
});

// 配置更新命令
entity.HasUpdateCommand((entity, cmd) =>
{
    cmd.Sql(@"
        UPDATE PUNTERING SET REGISTRATION_DATE = {1}, EMPLOYEE_ID = {2} WHERE ID = {0};
        UPDATE DEELPUNTERING SET HOURS_WORKED = {4}, TASK_DESCRIPTION = {5} WHERE DEELPUNTERING_ID = {3};
    ", entity.Id, entity.RegistrationDate, entity.EmployeeId, entity.DeelpunteringId, entity.HoursWorked, entity.TaskDescription);
});

// 配置删除命令
entity.HasDeleteCommand((entity, cmd) =>
{
    cmd.Sql(@"
        DELETE FROM DEELPUNTERING WHERE DEELPUNTERING_ID = {0};
        DELETE FROM PUNTERING WHERE ID = {1};
    ", entity.DeelpunteringId, entity.Id);
});

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