Configuring an Auto Increment field on an Abstract Class in Entity Framework

Question

I have some code first entities similar to these:

public abstract class Animal {
    public int ID { get; set; }
    public int NumberOfLegs { get; set; }
}

public class Dog : Animal {
    public string OtherDogRelatedStuff { get; set; }
}

public class Bird : Animal {
    public string OtherBirdRelatedStuff { get; set; }
}

public class MyContext : DbContext {

    public IDbSet<Animal> Animals { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder) {

        var pluraliser = PluralizationService.CreateService(new System.Globalization.CultureInfo("en-GB"));

        modelBuilder.HasDefaultSchema("vs");
        modelBuilder.Types().Configure(t => t.ToTable(pluraliser.Pluralize(t.ClrType.Name)));

        // This next line doesn't give ID column IDENTITY(1,1)
        modelBuilder.Entity<Animal>().Property(_ => _.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        // This line puts IDENTITY(1,1) on Animal.ID, but causes errors when I try to add/update data.
        //modelBuilder.Entity<Dog>().Property(_ => _.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        base.OnModelCreating(modelBuilder);
    }        
}

And I want table per type... so the tables look something like this:

CREATE TABLE Animals (
    ID INT NOT NULL IDENTITY(1,1),
    NumberOfLegs INT NOT NULL,
    CONSTRAINT pkAnimals PRIMARY KEY (ID)
)

CREATE TABLE Dogs (
    ID INT NOT NULL,
    OtherDogRelatedStuff VARCHAR(200),
    CONSTRAINT pkDogs PRIMARY KEY (ID),
    CONSTRAINT fkAnimal_Dog FOREIGN KEY (ID) REFERENCES Animals(ID)
)

CREATE TABLE Birds (
    ID INT NOT NULL,
    OtherBirdRelatedStuff VARCHAR(200),
    CONSTRAINT pkBirds PRIMARY KEY (ID),
    CONSTRAINT fkAnimal_Bird FOREIGN KEY (ID) REFERENCES Animals(ID)
)

With data looking like this:

---Animals----------
ID      NumberOfLegs
1       4
2       4
3       2

---Dogs---------------------
ID      OtherDogRelatedStuff
1       Woof1
2       Woof2

---Birds---------------------
ID      OtherCatRelatedStuff
3       Sqwark1

But I can't get the auto increment ID working, or configured correctly.

I've tried this:

modelBuilder.Entity<Animal>().Property(_ => _.ID)
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

But because Animal is abstract this doesn't seem to set the IDENTITY(1,1) property on the table.

I've also tried doing the same on the Dog entity, which correctly adds the identity property to the ID column in the Animals table, but I get an UpdateException when I try to add new entities to the database on the SaveChanges() method:

A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'ID'.

How can I correctly set the ID column on the abstract type to auto increment, and make it work when I add data?

Answer 1

I would say use the interface or separate abstract class to put common functionalities like this:

public interface Common{
  int ID { get; set; }
}
public class Animal : Common {
[DatabaseGenerated(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity)]    
public int ID { get; set; }
public int NumberOfLegs { get; set; }
}

public class Dog : Common {
    public int ID { get; set; }
    public string OtherDogRelatedStuff { get; set; }
}

Answer 2

With these models...

public abstract class Animal
{
    public int ID { get; set; }
    public int NumberOfLegs { get; set; }
}

public class Dog : Animal
{
    public string OtherDogRelatedStuff { get; set; }
}

public class Bird : Animal
{
    public string OtherBirdRelatedStuff { get; set; }
}

...and this in the context...

public IDbSet<Animal> Animals { get; set; }

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    modelBuilder.Entity<Dog>().ToTable("Dogs");
    modelBuilder.Entity<Bird>().ToTable("Birds");
}

...then in the package manager execute Add-Migration "Animals" then Update-Database -Script

You should get:

CREATE TABLE [dbo].[Animals] (
    [ID] [int] NOT NULL IDENTITY,
    [NumberOfLegs] [int] NOT NULL,
    CONSTRAINT [PK_dbo.Animals] PRIMARY KEY ([ID])
)
CREATE TABLE [dbo].[Dogs] (
    [ID] [int] NOT NULL,
    [OtherDogRelatedStuff] [nvarchar](max),
    CONSTRAINT [PK_dbo.Dogs] PRIMARY KEY ([ID])
)
CREATE INDEX [IX_ID] ON [dbo].[Dogs]([ID])
CREATE TABLE [dbo].[Birds] (
    [ID] [int] NOT NULL,
    [OtherBirdRelatedStuff] [nvarchar](max),
    CONSTRAINT [PK_dbo.Birds] PRIMARY KEY ([ID])
)
CREATE INDEX [IX_ID] ON [dbo].[Birds]([ID])
ALTER TABLE [dbo].[Dogs] 
ADD CONSTRAINT [FK_dbo.Dogs_dbo.Animals_ID] FOREIGN KEY ([ID]) 
REFERENCES [dbo].[Animals] ([ID])
ALTER TABLE [dbo].[Birds] 
ADD CONSTRAINT [FK_dbo.Birds_dbo.Animals_ID] FOREIGN KEY ([ID]) 
REFERENCES [dbo].[Animals] ([ID])
INSERT [dbo].[__MigrationHistory]([MigrationId], [ContextKey], [Model], [ProductVersion])
VALUES (N'etc)

which is what you want, right?

Reference: Inheritance with EF Code First: Part 2 – Table per Type (TPT)

Be aware that sometimes you can run into issues with a field created with an identity if it has been initially created without an identity (and vice versa). You can fix that by dropping and recreating the table: https://stackoverflow.com/a/18917348/150342

Answer 3

I faced the same problem and I fixed it using data annotation in the abstract class, like this:

public abstract class Animal {
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }
    public int NumberOfLegs { get; set; }
}