How to 'merge' two different db tables to a single inherited class hierarchy in EF?

Question

I have a legacy DB I'd like to writing an application for monitoring some stuff in it.

The DB has a set of tables which is repeated for each 'project' in it.

For example if I have 2 projects, I would have project_1_table and a similar project_2_table.

Even though the 2 tables serve the same purpose for each project, there are some minor differences.

I'm looking for a way to create a model that will have a base class holding all the common elements of those tables, and then 2 inherited classes which would map the actual content.

To clarify: I have no control over the DB, and cannot change it. Looking for the best way to use EF in this situation.

How can this be done (either in the Model Editor or via CodeFirst)?

DB Schema

 project_1_table                   project_2_table
   recordID                          recordID
   title                             title
   project1field                     project2field

Entity Model (What I Want)

 BaseEntity
   ProjectType
   RecordID
   Title

 (Mapped inherited Entities)
 Project1Entity: BaseEntity        Project2Entity: BaseEntity
   ProjectType = 1                   ProjectType = 2
   RecordID                          RecordID
   Title                             Title
   Project1Field                     Project2Field

I'm new to EF, and it seems to elude me.

Is this possible? If so, how?

Answer 1

Something similar along the lines of your requirement is possible by using Table per Concrete Type inheritance model.

Entities

public abstract class BaseEntity {
    public int RecordID { get; set; }
    public abstract int ProjectType { get; }
    public string Title { get; set; }
}

public class Project1Entity : BaseEntity {
    public override int ProjectType {
        get { return 1; }
    }

    public string Project1Field { get; set; }
}

public class Project2Entity : BaseEntity {
    public override int ProjectType {
        get { return 2; }
    }

    public string Project2Field { get; set; }
}

DBContext

public class DataContext : DbContext {
    public DbSet<Project1Entity> Project1Entities { get; set; }
    public DbSet<Project2Entity> Project2Entities { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder) {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<BaseEntity>().HasKey(o => o.RecordID);
        modelBuilder.Entity<Project1Entity>().Map(m => {
            m.MapInheritedProperties();
            m.ToTable("project_1_table");
        });

        modelBuilder.Entity<Project2Entity>().Map(m => {
            m.MapInheritedProperties();
            m.ToTable("project_2_table");
        });
    }
}

However there are several limitations

• with this configuration EF isn't able to generate the RecordID for new records, so you have to provide value of the primary key yourself
• it will work only if RecordID for items in project_1_table and project_2_table doesn't collide. If they do, you need to specify another primary key - e.g. add column ProjectType to the database and use the composite primary key {RecordID, ProjectType}

---

Version with composite key

Entities

public abstract class BaseEntity {
    public int RecordID { get; set; }
    public int ProjectType { get; set; }
    public string Title { get; set; }
}

public class Project1Entity : BaseEntity {
    public string Project1Field { get; set; }
}

public class Project2Entity : BaseEntity {
    public string Project2Field { get; set; }
}

DBContext

public class DataContext : DbContext {
    ...

    protected override void OnModelCreating(DbModelBuilder modelBuilder) {
        ...
        modelBuilder.Entity<BaseEntity>().HasKey(o => new { o.RecordID, o.ProjectType });
        ...
    }
}