.NET: Entity Framework & DbContext: Use same model/class for multiple tables

Question

I am creating a new project on top of an existing database which I cannot modify - the database design unfortunately is rather poor in some aspects. However, amongst others this database has eight tables that have the same structure. For the sake of simplicity of this question, let's assume it is two tables, Table1 and Table2 with a very limited set of fields. It is an Azure SQL database in case that matters.

Note: This question was highlighted as a duplicate of this one but I don't believe it is. I am not interested in how to get around this problem or solve it (I have suggested a solution), I want to know why it appears in the first place.

In my database context, this looks like this:

    public virtual DbSet<Table1> Table1{ get; set; }
    public virtual DbSet<Table2> Table2{ get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Table1>(entity =>
        {
            entity.HasKey(e => e.PkId);

            entity.ToTable("table1");

            entity.Property(e => e.PkId).HasColumnName("pk_id");

            entity.Property(e => e.Version1)
                .IsRequired()
                .HasMaxLength(50)
                .HasColumnName("version1");
            }

        modelBuilder.Entity<Table2>(entity =>
        {
            entity.HasKey(e => e.PkId);

            entity.ToTable("table2");

            entity.Property(e => e.PkId).HasColumnName("pk_id");

            entity.Property(e => e.Version2)
                .IsRequired()
                .HasMaxLength(50)
                .HasColumnName("version2");
            }
    }

Note that the field names are slightly different. Now I thought I was super smart when I decided to simplify this code by using the same model for these two classes - as this would simplify my code a lot.

    public virtual DbSet<Table> Table1{ get; set; }
    public virtual DbSet<Table> Table2{ get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Table>(entity =>
        {
            entity.HasKey(e => e.PkId);

            entity.ToTable("table1");

            entity.Property(e => e.PkId).HasColumnName("pk_id");

            entity.Property(e => e.Version)
                .IsRequired()
                .HasMaxLength(50)
                .HasColumnName("version1");
            }

        modelBuilder.Entity<Table>(entity =>
        {
            entity.HasKey(e => e.PkId);

            entity.ToTable("table2");

            entity.Property(e => e.PkId).HasColumnName("pk_id");

            entity.Property(e => e.Version)
                .IsRequired()
                .HasMaxLength(50)
                .HasColumnName("version2");
            }
    }

Generally, this seems to work, but when running AddRangeAsync() on one of the table, it silently fails - nothing is written to the database when calling SaveChangesAsync().

I found myself a way around this by using AutoMapper: Basically I work with my Table objects, and just before adding them to the DB Context I map them to their respective target objects, e.g. Table1. This works without any problems and is a proper way around this issue.

However, I'm still curious to know why my ingenious solution silently fails - I was quite convinced that you could use the same model class for different tables. Is it the modelBuilder that cannot handle the same entity type multiple times? Is there a better way than using AutoMapper to handle situations like that? I thought I could avoid the mapping process for performance reasons and the sake of simplicity.

Again, I cannot modify the database design, if it was my DB I would have used one table and introduced an additional ID column or sth like that, but I cannot do that - don't blame me for the poor DB design.

Answer 1

In general EF Core uses the entity class CLR type as identity of a separate entity, thus the associated database table or view, columns, relationships etc. - see Entity Types. It can be seen from many generic methods of the DbContext having TEntity generic type argument, or even non generic methods like Attach, Add etc. just receiving object instance, and using the GetType() for finding IEntityType in the model, and in turn provide the entity CRUD services. But most noticeable is the Set<TEntity>() method used to retrieve/query the entity data from the corresponding table. Even only the presence of that method is clear indication that the entity CLR type and table relationship is one to one (if speaking in relational database terms).

Now, in latest versions in theory EF Core also supports the so called Shared-type entity types which are used for implementing the automatic join entity for many-to-many with artificial Dictionary<string, object> "entity", but could be be used with user defined types as well. The problem is though, they require special configuration providing a name for the entity, and then passing that name for different methods (including aforementioned Set<TEntity>()), and some other which receive just object instances do not work at all. Because of all that, I find them not very practical usable.

With that in mind, it's much better the use a base class (with or without any EF Core database inheritance mapping) and empty derived (but separate) types. In your example, you would have a base class Table and derived classes Table1 : Table, Table2 : Table etc. It's similar to workaround you mentioned, but does not require AutoMapper (or similar mapper).

Finally, what's wrong with your attempt. Well, code wise it's ok, also there is no EF Core fluent mapping bug, so what's the problem then.

The problem is that EF Core fluent API allow you multiple table/attribute redefinition, with later simply overriding the previous configuration (last wins). So here

modelBuilder.Entity<Table>(entity =>
{
    entity.HasKey(e => e.PkId);

    entity.ToTable("table1");

    entity.Property(e => e.PkId).HasColumnName("pk_id");

    entity.Property(e => e.Version)
        .IsRequired()
        .HasMaxLength(50)
        .HasColumnName("version1");
    }

modelBuilder.Entity<Table>(entity =>
{
    entity.HasKey(e => e.PkId);

    entity.ToTable("table2");

    entity.Property(e => e.PkId).HasColumnName("pk_id");

    entity.Property(e => e.Version)
        .IsRequired()
        .HasMaxLength(50)
        .HasColumnName("version2");
}

you are effectively mapping entity Table to table "table2" with Version column mapped to "version2" column etc. Essentially everything from the first configuration is ignored/replaced by the second. You can easily verify that by generating EF Core migration from the above model/fluent configuration, and you'll see that there would be just one CreateTable instead of 2,3,4 you expect.

What about

    public virtual DbSet<Table> Table1 { get; set; }
    public virtual DbSet<Table> Table2 { get; set; }

think of them (and what you'll get actually) as two variables pointing to one and the same list/object/definition, in this case - table.