EF core (for oracle) map two different tables to one entity

Question

We are working on a very old oracle db that is impossible to modify, anyways there are two tables that should be gathered to one and since we cannot do that we want to do it in the api with ef core so create a mapping that merges the two entites (with different properties and different PKs) to one entity, how can we possibly do that?

First table:

public class RegistrationMapper : IEntityTypeConfiguration<Registration>
{
    public void Configure(EntityTypeBuilder<Registration> registration)
    {
        registration.ToTable("PUNTERING", "PUNT");

        registration.HasIndex(e => new { e.ProjectId, e.TaskId }, "IX_PUNTERING");

        registration.HasIndex(e => e.DayRegistrationId, "IX_PUNTERING_DAGID");

        registration.Property(e => e.RegistrationId)
            .HasPrecision(8)
            .ValueGeneratedOnAdd()
            .UseHiLo("SEQ_PUNTERING", "PUNT")
            .HasColumnName("PUNTERINGID");

        registration.Property(e => e.AutoCalculate)
            .HasMaxLength(1)
            .IsUnicode(false)
            .HasColumnName("AUTOCALCULATE")
            .HasDefaultValueSql("'J' ");

        registration.Property(e => e.ProjectId)
            .HasMaxLength(20)
            .IsUnicode(false)
            .HasColumnName("AXPROJECTNR");

        registration.Property(e => e.Comment)
            .HasMaxLength(500)
            .IsUnicode(false)
            .HasColumnName("COMMENTAAR");

        registration.Property(e => e.CreationDate)
            .HasColumnType("DATE")
            .HasColumnName("DATUM_CREATIE");

        registration.Property(e => e.ModificationDate)
            .HasColumnType("DATE")
            .HasColumnName("DATUM_UPDATE");

        registration.Property(e => e.Billable)
            .HasMaxLength(1)
            .IsUnicode(false)
            .HasColumnName("FACTUREREN");

        registration.Property(e => e.CreationHumanResourceId)
            .HasMaxLength(5)
            .IsUnicode(false)
            .HasColumnName("PERSNR_CREATIE");

        registration.Property(e => e.ModificationHumanResourceId)
            .HasMaxLength(5)
            .IsUnicode(false)
            .HasColumnName("PERSNR_UPDATE");

        registration.Property(e => e.ProjectId)
            .HasMaxLength(20)
            .IsUnicode(false)
            .HasColumnName("AXPROJECTNR");

        registration.Property(e => e.DayRegistrationId)
            .HasPrecision(7)
            .HasColumnName("PUNTERING_DAGID");

        registration.Property(e => e.TaskId)
            .HasPrecision(4)
            .HasColumnName("SUBPROJECTID");

        registration.Property(e => e.Transport)
            .HasMaxLength(1)
            .IsUnicode(false)
            .HasColumnName("VERPLAATSING");

        registration.HasOne(d => d.Project)
            .WithMany(p => p.Registrations)
            .HasForeignKey(d => d.ProjectId)
            .OnDelete(DeleteBehavior.ClientSetNull)
            .HasConstraintName("FK_PUNTERING_AXPROJECT");

        registration.HasOne(d => d.DayRegistration)
            .WithMany(p => p.Registrations)
            .HasForeignKey(d => d.DayRegistrationId)
            .OnDelete(DeleteBehavior.ClientSetNull)
            .HasConstraintName("FK_PUNTERING_PUNTERINGDAG");

        registration.HasOne(d => d.Task)
            .WithMany(p => p.Registrations)
            .HasForeignKey(d => new { d.ProjectId, d.TaskId })
            .OnDelete(DeleteBehavior.ClientSetNull)
            .HasConstraintName("FK_PUNTERING_SUBPROJECT");
    }
}

second one:

public class DetailedRegistrationMapper : IEntityTypeConfiguration<DetailedRegistration>
{
    public void Configure(EntityTypeBuilder<DetailedRegistration> detailedRegistration)
    {
        detailedRegistration.ToTable("DEELPUNTERING", "PUNT");

        detailedRegistration.HasIndex(e => e.RegistrationId, "IX_DEELPUNTERING");

        detailedRegistration.Property(e => e.DetailedRegistrationId)
            .HasPrecision(8)
            .ValueGeneratedOnAdd()
            .UseHiLo("SEQ_DEELPUNTERING", "PUNT")
            .HasColumnName("DEELPUNTERINGID");

        detailedRegistration.Property(e => e.Percentage)
            .HasColumnType("NUMBER(6,2)")
            .HasConversion(v => WritePercentageType(v), v => ReadPercentageType(v!))
            .HasColumnName("PERCENTAGE_FACTURATIE");

        detailedRegistration.Property(e => e.RegistrationId)
            .HasPrecision(8)
            .HasColumnName("PUNTERINGID");

        detailedRegistration.Property(e => e.Rest)
            .HasMaxLength(1)
            .IsUnicode(false)
            .HasColumnName("REST");

        detailedRegistration.Property(e => e.EndDate)
            .HasColumnType("DATE")
            .HasColumnName("TIJD_TOT");

        detailedRegistration.Property(e => e.FromDate)
            .HasColumnType("DATE")
            .HasColumnName("TIJD_VAN");

        detailedRegistration.HasOne(d => d.Registration)
            .WithMany(p => p.DetailedRegistrations)
            .HasForeignKey(d => d.RegistrationId)
            .OnDelete(DeleteBehavior.ClientSetNull)
            .HasConstraintName("FK_DEELPUNT_PUNT");
    }
}

new entity

public partial class TimeRegistration {
    public int TimeRegistrationId { get; set; }
    public string HumanResourceId { get; set; } = null!;
    public string ProjectId { get; set; } = null!;
    public int TaskId { get; set; }
    public DateTime? StartDateTime { get; set; }
    public DateTime? EndDateTime { get; set; }
    public PercentageType Percentage { get; set; }
    public string? Comment { get; set; }
    public bool? Billable { get; set; }
    public string? CreationHumanResourceId { get; set; }     
    public double? Duration { get; set; }
}

I can get each table as its own and then do a select to the new entity, but it looks confusing in code also I want to be able to remove those two tables for the domain model and only keep the new entity.

Any ideas?

Answer 1

Simply write a SQL query that returns the properties of your entity and configure the entity to run the query.

eg

modelBuilder.Entity<TimeRegistration>().ToSqlQuery("select TimeRegistrationId, HumanResourceId ... from Registration r join DetailedRegistration rd on  ...");