When we are using Enitity Framework code first to define our model, chances are that we will probably want to use some inheritance here and there. Using migrations to create our database, there are different ways to define this inheritance in our database. I will show you the most commonly used ways of generating tables based on inherited classes.

Setup

For this post we'll get the help of our favorite pets, a cat and a dog, which both happen to be animals. Great, we'll go with Kitty the cat and Buddy the dog!

Lets first define our Animal. Well first of all we are using Entity Framework to save them in the database so they should both have an Id. The most common thing our pets have in common is that they both have a name. So we'll add that as a property of our animal.

Now lets talk about Kitty, our cat. We all know cats have 9 lives and dogs don't. So lets create a property to determine how many lives our cat has got left (LivesLeft).

To make Buddy feel unique lets give the dog a unique property. Hmm... what do dogs have or do, that cats don't? ... Of course! They guard our homes! Lets keep track of how many times Buddy has successfully defended our home (HomesDefended).

public abstract class Animal
{
    public int Id { get; set; }
    public string Name { get; set; }
}
 
public class Cat : Animal
{
    public int LivesLeft { get; set; }
}
 
public class Dog : Animal
{
    public int HomesDefended { get; set; }
}

These are the classes I made for the setup I just explained. I have made Animal abstract because we never want to create an instance of Animal, it will always be a cat or a dog.

Now we got our model, but there are different ways to save our animals to the database. I'll explain the 3 most common ways you would want to do this.

1. A single table

The simplest way is probably to have just 1 table in our database which will hold all our animals. This is probably not what you are looking for but nevertheless I'll show you how to do this.

The context

In our context we have only 1 DbSet which holds our Animals (no cats, no dogs, they are all just animals).

public class ApplicationDbContext1 : DbContext
{
    public DbSet<Animal> Animals { get; set; }
    public ApplicationDbContext1() : base("Inhertiance1") { }
}

Seed

To have actual data in our database I have implemented the Seed method for our context. Because it only has a list of Animals, we'll have to add Kitty and Buddy straight to our animals collection as shown in the code below.

protected override void Seed(ApplicationDbContext1 context)
{
    context.Animals.AddRange(new List<Animal>
    {
        new Cat
        {
            Name = "Kitty",
            LivesLeft = 9
        },
        new Dog
        {
            Name = "Buddy",
            HomesDefended = 0
        }
    });
}

The migration

I have added the migration created by EF just for reference. We'll have a look at the database using the data & diagram below.

public partial class CreateDB1 : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.Animals",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Name = c.String(),
                    LivesLeft = c.Int(),
                    HomesDefended = c.Int(),
                    Discriminator = c.String(nullable: false, maxLength: 128),
                })
            .PrimaryKey(t => t.Id);
            
    }
        
    public override void Down()
    {
        DropTable("dbo.Animals");
    }
}

The database

As expected we have only created one table for our Animals in the database. This means that all records have all the properties of Animal (Id & Name), the property of our Cat (LivesLeft) and the property of our Dog (HomesDefended). In addition EF has created an extra column (Discriminator) to determine which record is which animal. As you can see in the sample data, the first one is Kitty the cat and the second one is Buddy the dog. You also notice that the record for Kitty has a NULL value in HomesDefended because it is not a cat property, and the same is true for Buddy's record. I personally don't like having these NULL values in my DB and of course we all know putting cats and dogs together is usually a bad idea! To fix this, lets have a look at the second approach.

2. A table for everyone

If we want to normalize our database and keep different things in different tables, this approach will work better. Here we will create a table for each of our classes. A table for Animals, which will hold the common properties, A table for Cats and a table for Dogs which will be linked to our Animal table.

The context

For the reasons stated above I have created 3 DbSets 1 for Animals, 1 for Cats and 1 for Dogs. If this would be the only changes made, we would still end up with the same table as in the 1st example. To have our Cats and Dogs tables created I have overridden the method OnModelCreating. In this method I have specified that Kitty and Buddy both want there own separate tables.

public class ApplicationDbContext2 : DbContext
{
    public DbSet<Animal> Animals { get; set; }
    public DbSet<Cat> Cats { get; set; }
    public DbSet<Dog> Dogs { get; set; }
    public ApplicationDbContext2() : base("Inhertiance2") { }
 
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Cat>().ToTable("Cats");
        modelBuilder.Entity<Dog>().ToTable("Dogs");
 
        base.OnModelCreating(modelBuilder);
    }
}

Seed

Because we now have 3 properties on our context we have multiple ways to add Kitty and Buddy to the database. Since we still have our Animals property we could just keep the seed exactly the same as in the first example. But because we have seperate properties for cats and dogs I'dd rather use those.

protected override void Seed(ApplicationDbContext2 context)
{ 
    //context.Animals.AddRange(new List<Animal>
    //{
    //    new Cat
    //    {
    //        Name = "Kitty",
    //        LivesLeft = 9
    //    },
    //    new Dog
    //    {
    //        Name = "Buddy",
    //        HomesDefended = 0
    //    }
    //});
 
    //Or this
    
    context.Cats.Add(new Cat
    {
        Name = "Kitty",
        LivesLeft = 9
    });
    context.Dogs.Add(new Dog
    {
        Name = "Buddy",
        HomesDefended = 0
    });
}

The migration

Again, for reference.

public partial class CreateDB2 : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.Animals",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Name = c.String(),
                })
            .PrimaryKey(t => t.Id);
            
        CreateTable(
            "dbo.Cats",
            c => new
                {
                    Id = c.Int(nullable: false),
                    LivesLeft = c.Int(nullable: false),
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("dbo.Animals", t => t.Id)
            .Index(t => t.Id);
            
        CreateTable(
            "dbo.Dogs",
            c => new
                {
                    Id = c.Int(nullable: false),
                    HomesDefended = c.Int(nullable: false),
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("dbo.Animals", t => t.Id)
            .Index(t => t.Id);
            
    }
        
    public override void Down()
    {
        DropForeignKey("dbo.Dogs", "Id", "dbo.Animals");
        DropForeignKey("dbo.Cats", "Id", "dbo.Animals");
        DropIndex("dbo.Dogs", new[] { "Id" });
        DropIndex("dbo.Cats", new[] { "Id" });
        DropTable("dbo.Dogs");
        DropTable("dbo.Cats");
        DropTable("dbo.Animals");
    }
}

The database

EF has created 3 tables in our database, one that holds the basic properties of our animals, and 2 other that hold the specifics for each animal. Our Cats and Dogs tables both have a foreign key to our Animals table so if we want to query them to read all there properties we will have to join with our Animals table. This way our database is normalized and we don't have any unnecessary NULL values which is great! But on the other hand this adds some complexity to our database when querying it manually. That's why we"ll take a look at yet an other approach we can take.

3. Only tables for subclasses

This time we just want a table that holds our cats and a table that holds our dogs. This means that we won't have a table for our animals, so the Id and Name properties will have to become columns of our Cats and Dogs tables.

The context

Doing this is easy, all we have to do is create 2 DbSets, 1 for our Cats and 1 for our Dogs.

public class ApplicationDbContext3 : DbContext
{
    public DbSet<Cat> Cats { get; set; }
    public DbSet<Dog> Dogs { get; set; }
    public ApplicationDbContext3() : base("Inhertiance3") { }
}

Seed

We can reuse the same seed as we used in our 2nd approach, Kitty goes in the Cats DbSet and Buddy is added to our Dogs.

protected override void Seed(ApplicationDbContext3 context)
{
    context.Cats.Add(new Cat
    {
        Name = "Kitty",
        LivesLeft = 9
    });
    context.Dogs.Add(new Dog
    {
        Name = "Buddy",
        HomesDefended = 0
    });
}

The migration

You know why I put this here. :)

public partial class CreateDB3 : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.Cats",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    LivesLeft = c.Int(nullable: false),
                    Name = c.String(),
                })
            .PrimaryKey(t => t.Id);
            
        CreateTable(
            "dbo.Dogs",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    HomesDefended = c.Int(nullable: false),
                    Name = c.String(),
                })
            .PrimaryKey(t => t.Id);
            
    }
        
    public override void Down()
    {
        DropTable("dbo.Dogs");
        DropTable("dbo.Cats");
    }
}

The database

The tables created here are nice and simple, no relations, no NULL values. You can see that each table has the properties which were defined in our Animal class and the animal specific properties are only in there responsive tables.

Conclusion

I have given you 3 common ways to create different database layouts using the same model. When it comes to choosing the right one for your needs, you will have to analyse what you want to achieve.

If you like simplicity and don't care about NULL values in your database, you could choose the first approach. If you are concerned about database normalization and have complex inheritance with lots of properties, you should probably go for the 2nd approach. If your don't have lots of inheritance and you want to have separate tables for your entities I think you should go for the 3rd approach. But of course it all depends on what you prefer.

For this setup I would definitely go for the 3rd approach. There is only 1 property which our animals have in common so that's why I find it a bit overkill to have 3 separate tables. And I wouldn't put them all in one table because I don't like having NULL values in my database. Apart from that, I think its better to keep cats and dogs as far away from each other as possible, we all know how Kitty and Buddy can be. ;)

Thanks for reading, and if you have any suggestions, question or you want to share something else, please comment below!