Avoid separate table for each base entity

[weitzhandler]

Hi,

I'm trying to subclass IdentityDbContext to support multi-tenancy. I want to implement something similar to JSkimming's solution.

Anyway, generally if I have one Person and one Teacher and I have a DbSet<Person> in my context, two tables containing ALL the properties of both Person and Teacher are being generated in the database.

Is there a way to avoid that?

I have an entity Tenant, here is the code:

public class Tenant : Tenant<string>
{
}

public class Tenant<TKey>
  where TKey : IEquatable<TKey>
{
  public virtual TKey Id { get; set; }
  public virtual string TenantName { get; set; }

  public virtual ICollection<TenantUser<TKey>> Users { get; set; } = 
    new HashSet<TenantUser<TKey>>();
}

public DbSet<TTenant> Tenants { get; set; }

protected override void OnModelCreating(ModelBuilder builder)
{
  builder.Entity<TTenant>(b =>
  {
    b.Key(t => t.TenantId);
    b.Index(t => t.TenantName).Unique().IndexName("TenantNameIndex");
    b.ToTable("Tenants");

    b.Collection(t => t.Users).InverseReference().ForeignKey(u => u.TenantId);
  });
}

But when I try to add a migration, I get the following error:

Microsoft.Data.Entity.Metadata.ModelItemNotFoundException: The entity type 'Microsoft.AspNet.Identity.EntityFramework.Tenant.Tenant' requires a key to be defined.

If I add a Key attribute on Tenant<TKey>.Id the following is generated to the migration:

migration.CreateTable(
    name: "Tenants",
    columns: table => new
    {
        TenantId = table.Column(type: "nvarchar(450)", nullable: false),
        TenantName = table.Column(type: "nvarchar(16)", nullable: true)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Tenant", x => x.TenantId);
    });
migration.CreateTable(
    name: "Tenant<string>",
    columns: table => new
    {
        TenantId = table.Column(type: "nvarchar(450)", nullable: false),
        TenantName = table.Column(type: "nvarchar(16)", nullable: true)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Tenant<string>", x => x.TenantId);
    });

[divega]

Inheritance support is still being worked on and depending on which build you are using you might get different levels of functionality. In fact we have only recently added convention and configuration support for discriminator columns in Code First.

@AndriySvyryd can comment on what the minimal level of configuration you will have to do to get inheritance working is currently.

Regardless, if as you said in the comment on #247 you don't want the base class (in this case Person) to be mapped in your model, then do not add a DbSet<Person> to your DbContext. As a general rule, EF will not walk up the inheritance hierarchy to find a base type and add it to the model unless you mention it explicitly, e.g. either in a DbSet, with the modelBuilder.Entity<T>() method or in a navigation property. Another thing you may want to try is to make the base type abstract.

[weitzhandler]

@divega
Hi Diego and thanks for your reply.
Unfortunately the DbSet<Person> is already declared in the base DbContext.
Besides, I don't want it NOT to be declared, I just want TPerson of DbContext<TPerson> where TPerson : Person to be initialized as table, all the base class' properties should be part of this one.

[weitzhandler]

@divega @rowanmiller @AndriySvyryd
I've posted a new repository that tries to implement support for multi-tenancy leveraging the existing Identity API, would thank anyone who can direct me in making it work by addressing the above issue.

How can I get rid of EF generating two SQL tables for TenantUser and TenantUser<string> both consisting of ALL properties.

This is a blocking issue.

https://github.com/weitzhandler/Tenants

[AndriySvyryd]

@weitzhandler
You can map derived entity types to the same table as the parent by calling

modelBuilder.Entity<TenantUser>.BaseType<TenantUser<string>>();

However inheritance support is not fully implemented yet, so you might run into errors at runtime.

[weitzhandler]

@AndriySvyryd
Thanks for your help.
With this I managed to go a bit further. Please see my updated context.

However I have a different issue. I have a one-to-many reference between TUser.Tenant and TTenant.Users.

As you can see, the the FK is generated twice, thus throwing the following error when trying to generate database:

Cannot create, drop, enable, or disable more than one constraint, column, index, or trigger named 'FK_Tenant_TenantUser' in this context. Duplicate names are not allowed.

I've posted a question about this on SO.

Well looks like this is under control:
#2910