TPC - Identity primary key

Question

I am using code first (EntityFramework 6.1) with a Table Per Concrete type approach.

According to the very last comment of this thread, EF 6.1 does not set the SQL auto-increment Identity on. I understand that.

But I want to force this Identity on since I will never use (nor define) the base class DbSet<>. I will not access the base class collection, only the concrete ones.

More that words, some code:

public abstract class BaseModel
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)] // <- tried to force it, but does not work
    public long Id { get; set; }

    [Timestamp]
    public byte[] DataVersion { get; set; }

    public string CreatedWho { get; set; }
    public DateTime CreatedWhen { get; set; }
    public string UpdatedWho { get; set; }
    public DateTime UpdatedWhen { get; set; }
}

public class Currency : BaseModel
{
    public string IsoCode {get; set; }
}

In My DbContext class there is:

public DbSet<Currency> Currencies {get; set; }

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    // Enable TPC Inheritance
    modelBuilder.Entity<Currency>().Map(m =>
        {
            m.MapInheritedProperties();
            m.ToTable(typeof(Currency).Name);
        });

    base.OnModelCreating(modelBuilder);
}

With this code, in my generated DB Table Currency, I have all the BaseModel columns as expected, Id is the Primary Key, ok. But it does not have the Identity property set on, despite the explicite DatabaseGenerated attribute usage.

How can I force the Identity in a TPC inheritance schema?

Answer 1

It seems that the [DatabaseGenerated(DatabaseGeneratedOption.Identity)] works finally to force the Identity behaviour on my PK in a TPC inhertiance context.

The trick? Package Console commande Update-Databse -Force does not apply changes of that sort automatically. You need to remove your Tables to recreate them all fresh.

I use the following SQL script to remove everything (even __MigrationHistory), inspired from this blog :

WHILE ( EXISTS ( SELECT 1
                 FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                 WHERE  CONSTRAINT_TYPE = 'FOREIGN KEY' ) )
    BEGIN
        DECLARE @sql NVARCHAR(2000)
        SELECT TOP 1
                @sql = ( 'ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
                         + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']' )
        FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        WHERE   CONSTRAINT_TYPE = 'FOREIGN KEY'
        EXEC (@sql)
        PRINT @sql
    END
GO 

WHILE ( EXISTS ( SELECT 1
                 FROM   INFORMATION_SCHEMA.TABLES ) )
    BEGIN
        DECLARE @sql NVARCHAR(2000)
        SELECT TOP 1
                @sql = ( 'DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
                         + ']' )
        FROM    INFORMATION_SCHEMA.TABLES

        EXEC (@sql)
        PRINT @sql
    END

After that, re-run the Update-Database -Force command to regenerate all tables. Identity will be activated.