Entity Framework Core 1.0 Migrations in a Separate Class Library

At the beginning of last year I started blogging about ASP.NET Core 1.0 (née ASP.NET 5). But it was early days and things were far from stable with the product, so I soon came to the conclusion that being such an early adopter was more trouble than it was worth due to the number of bugs, missing features, and breaking changes. Roll on a year, and ASP.NET Core 1.0 along with Entity Framework Core 1.0 and the requisite Visual Studio 2015 tooling has finally been released. So time to start playing again.

I can’t say that I’ve done a huge amount with it yet, so can’t really comment on it’s stability. But I would assume that it’s about as stable as you would expect from a 1.0.0 version. Good enough to work with but challenging at times.

And it didn’t take me long to come across a challenge, which I’d like to share with you here.

In any but the smallest of solutions, it’s good practice to seperate data concerns from domain and presentational concerns. In the case of ASP.NET MVC and Entity Framework, that typically involves placing the data context and data model classes into a separate class library.

If I do this and then attempt to add a migration, as follows –

PM> Add-Migration -Name "Initial" -Project "SandboxCore.Data"

I get an error telling me that the preview of EF does not support commands on class library projects –

Could not invoke this command on the startup project 'SandboxCore.Data'. 
This preview of Entity Framework tools does not support commands on class library projects in ASP.NET Core 
and .NET Core applications. See http://go.microsoft.com/fwlink/?LinkId=798221 for details and workarounds.

The link in the error message describes 2 workarounds. However, at the time of writing it is out-of-date, and the workarounds aren’t as simple as described.

So here’s what I believe to be the easiest solution to the problem, which is to create the data project, not as a class library, but as a console application.

1. Add a .NET Core console project, to your solution, to contain the data context and data model classes.

2. Add the entity framework core provider, for your chosen database, as a project dependency to your data project. If targetting SQL Server, add Microsoft.EntityFrameworkCore.SqlServer.

3. Add the entity framework tools, Microsoft.EntityFrameworkCore.Tools, as both a project dependency and as a tool to your data project. Adding it as a project dependency ensures that it is resolved and downloaded from NuGet. Whereas, adding it as a tool ensures that the entity framework commands are accessible. Your data project’s project.json should now look like this –

{
  "version": "1.0.0-*",
  "buildOptions": {
    "emitEntryPoint": true
  },

  "dependencies": {
    "Microsoft.NETCore.App": {
      "type": "platform",
      "version": "1.0.0"
    },
    "Microsoft.EntityFrameworkCore.SqlServer": "1.0.0",
    "Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final"
  },

  "frameworks": {
    "netcoreapp1.0": {
      "imports": "dnxcore50"
    }
  },

  "tools": {
    "Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final"
  }
}

4. Add data model classes to your data project. I like to add mine to a Models folder to keep the root of the project tidy.

using System.ComponentModel.DataAnnotations;

namespace SandboxCore.Data.Models
{
    public class Product
    {
        public int ProductId { get; set; }

        [StringLength(200)]
        public string ProductName { get; set; }
    }
}

5. And finally, add an implmentation of DbContext to the data project. The implementation should have a publically accessible Dbset<TEntity> for each data model class, and the OnConfiguring method should be overridden in order to configure the context to target your database of choice. This last part is necessary for migrations.

using Microsoft.EntityFrameworkCore;
using SandboxCore.Data.Models;

namespace SandboxCore.Data
{
    public class SandboxCoreDbContext : DbContext
    {
        public DbSet<Product> Projects { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=SandboxCore;Trusted_Connection=True;");
        }
    }
}

6. You can now enable and add migrations using the following command.

PM> Add-Migration -Name "Initial" -Project "SandboxCore.Data" -StartupProject "SandboxCore.Data"
 

The -Project option specifies SandboxCore.Data as the target for migrations, whilst the -StartupProject option specifies SandboxCore.Data as the entry point for running the EF command.

 

Inheritance Modelling Patterns with Entity Framework 6 Code First

Introduction

In this post I’m going to demonstrate how to model inheritance using Entity Framework 6 Code First. In particular, I’m going to demonstrate the following alternative database mapping strategies –

  • Table Per Hierarchy (TPH)
  • Table Per Concrete Type (TPC)
  • Table Per Type (TPT)

My model is as follows –

contract

Contract, the base class, has a start and end date, and a unique identifier. CreditContract and PrepaymentContract derive from Contract and hence inherit it’s properties. In addition, CreditContract has credit limit and currency properties, whilst PrepaymentContract has prepayment amount and period properties..

Code First Classes

First step is to create the model classes.

Contract

Contract is the base class for the hierarchy, hence the abstract modifier.

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace CodeFirstInheritance.Models
{
    public abstract class Contract
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int ContractID { get; set; }

        [DataType(DataType.Date)]
        [Required]
        public DateTime StartDate { get; set; }

        [DataType(DataType.Date)]
        [Required]
        public DateTime EndDate { get; set; }
    }
}

CreditContract

CreditContract is derived from Contract.

For simplicity I have modelled the CreditLimitCurrency property as a string with data annotations mapping it to a char(3) column. In practice it would be better to model this as a Enum.

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace CodeFirstInheritance.Models
{
    public class CreditContract : Contract
    {
        [Required]
        public float CreditLimit { get; set; }

        [Column(TypeName = "char")]
        [StringLength(3)]
        [Required]
        public string CreditLimitCurrency { get; set; }
    }
}

PrepaymentContract

PrepaymentContract also derives from Contract.

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace CodeFirstInheritance.Models
{
    public class PrepaymentContract : Contract
    {
        [Required]
        public float PrepaymentAmount { get; set; }

        [Column(TypeName = "char")]
        [StringLength(1)]
        [Required]
        public string PrepaymentPeriod { get; set; }
    }
}

Table Per Hierarchy (TPH)

With the TPH mapping strategy, all classes in the inheritance tree are mapped to a single database table. A database field is used as a discriminator in determining which concrete type each database record should be mapped to.

To instruct Entity Framework to use this strategy, all that is required is to derive a class from the DbContext class, and add a DBSet property for the Contract type, whilst NOT adding DBSet properties for CreditContract nor PrepaymentContract.

I have also added an override of the OnModelCreating method. I’ll discuss this in a moment.

CodeFirstInheritanceDbContext

using System.Data.Entity;

namespace CodeFirstInheritance.Models
{
    public class CodeFirstInheritanceDbContext : DbContext
    {
        public DbSet<Contract> Contracts { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Contract>()
                .Map<CreditContract>(m => m.Requires("Discriminator").HasValue("C"))
                .Map<PrepaymentContract>(m => m.Requires("Discriminator").HasValue("P"));

            base.OnModelCreating(modelBuilder);
        }
    }
}

If we then enable migrations and add an initial migration, the following DbMigration class is generated –

namespace CodeFirstInheritance.Migrations
{
    using System;
    using System.Data.Entity.Migrations;

    public partial class Initial : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.Contracts",
                c => new
                    {
                        ContractID = c.Int(nullable: false, identity: true),
                        StartDate = c.DateTime(nullable: false),
                        EndDate = c.DateTime(nullable: false),
                        CreditLimit = c.Single(),
                        CreditLimitCurrency = c.String(maxLength: 3, fixedLength: true, unicode: false),
                        PrepaymentAmount = c.Single(),
                        PrepaymentPeriod = c.String(maxLength: 1, fixedLength: true, unicode: false),
                        Discriminator = c.String(nullable: false, maxLength: 128),
                    })
                .PrimaryKey(t => t.ContractID);

        }

        public override void Down()
        {
            DropTable("dbo.Contracts");
        }
    }
}

A single table, Contract, is created with fields from all types. Of note is the Discriminator field, which we did not explicitly specify in our hierarchy. EF has generated this field for us and will use it to discriminate between credit and prepayment contract records.

If we update the database and then execute the following code –

using (var db = new CodeFirstInheritanceDbContext())
{
    CreditContract creditContract = new CreditContract();
    creditContract.StartDate = new DateTime(2015, 1, 1);
    creditContract.EndDate = new DateTime(2015, 12, 31);
    creditContract.CreditLimit = 100000;
    creditContract.CreditLimitCurrency = "USD";
    db.Contracts.Add(creditContract);

    PrepaymentContract prepaymentContract = new PrepaymentContract();
    prepaymentContract.StartDate = new DateTime(2015, 1, 1);
    prepaymentContract.EndDate = new DateTime(2015, 12, 31);
    prepaymentContract.PrepaymentAmount = 10000;
    prepaymentContract.PrepaymentPeriod = "M";
    db.Contracts.Add(prepaymentContract);

    db.SaveChanges();
}

The following records are added to the Contract table –

tphresults

By default EF will use the full names of the types as the discriminator values. This may significantly increase the size of each record. EF allows us to override these values using the fluent API. This is what I did in the OnModelCreating method of the CodeFirstInheritanceDbContext class, in order to shorten the discriminator to a single character.

We can then query the model across contract types and for specific contract types as follows –

using (var db = new CodeFirstInheritanceDbContext())
{
    var countOfAllContract = db.Contracts.Count();
    var countOfCreditContracts = db.Contracts.OfType<CreditContract>().Count();
}

TPH is the simplest mapping strategy to implement. And as all data is stored in a single table there are no joins required when querying data. It is therefore the most performant strategy.

However, there are a few issues –

  • If you look back at the CreditContract and PrepaymentContract classes you’ll note that all the properties are decorated with the [Required] attribute. However, to fit records of both types into a single database table EF has had to generate the fields as nullable. Data integrity has potentially been compromised.
  • It is not clear from the database table which fields relate to which type. This may make life difficult when populating the table from other sources.
  • The table breaks 3rd normal form as the values contained in the sub-type fields depend on the discriminator. 3rd normal form states that fields should only depend on the table key.

Table Per Concrete Type (TPC)

With the TPC mapping strategy, each concrete class is mapped to it’s own database table. So we get 2 tables, CreditContract and PrepaymentContract, with each having all fields in the respective branch of the hierarchy. We do not get a table for the abstract base class.

Before we instruct EF to use this strategy, we need to make a small modification to the Contract class – change the type of the ContractID property from int to Guid. I’ll discuss why in a moment.

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace CodeFirstInheritance.Models
{
    public abstract class Contract
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public Guid ContractID { get; set; }

        [DataType(DataType.Date)]
        [Required]
        public DateTime StartDate { get; set; }

        [DataType(DataType.Date)]
        [Required]
        public DateTime EndDate { get; set; }
    }
}

As per the TPH strategy, we derive a class from DbContext, and add a DBSet property for the Contract type, whilst NOT adding DBSet properties for CreditContract nor PrepaymentContract.

We then override the OnModelCreating method and add fluent API code to instruct EF to map the CreditContract entity to a specific table. And to also map the inherited properties to this table. The table name does not have to be the same as the entity name.

We do the same for PrepaymentContract.

using System.Data.Entity;

namespace CodeFirstInheritance.Models
{
    public class CodeFirstInheritanceDbContext : DbContext
    {
        public DbSet<Contract> Contracts { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<CreditContract>().Map(m =>
            {
                m.MapInheritedProperties();
                m.ToTable("CreditContract");
            });

            modelBuilder.Entity<PrepaymentContract>().Map(m =>
            {
                m.MapInheritedProperties();
                m.ToTable("PrepaymentContract");
            });   
           
            base.OnModelCreating(modelBuilder);
        }
    }
}

If we then enable migrations and add an initial migration, we can see from the migration script that 2 separate tables are created on the database.

namespace CodeFirstInheritance.Migrations
{
    using System;
    using System.Data.Entity.Migrations;
    
    public partial class Initial : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.CreditContract",
                c => new
                    {
                        ContractID = c.Guid(nullable: false, identity: true),
                        StartDate = c.DateTime(nullable: false),
                        EndDate = c.DateTime(nullable: false),
                        CreditLimit = c.Single(nullable: false),
                        CreditLimitCurrency = c.String(nullable: false, maxLength: 3, fixedLength: true, unicode: false),
                    })
                .PrimaryKey(t => t.ContractID);
            
            CreateTable(
                "dbo.PrepaymentContract",
                c => new
                    {
                        ContractID = c.Guid(nullable: false, identity: true),
                        StartDate = c.DateTime(nullable: false),
                        EndDate = c.DateTime(nullable: false),
                        PrepaymentAmount = c.Single(nullable: false),
                        PrepaymentPeriod = c.String(nullable: false, maxLength: 1, fixedLength: true, unicode: false),
                    })
                .PrimaryKey(t => t.ContractID);
            
        }
        
        public override void Down()
        {
            DropTable("dbo.PrepaymentContract");
            DropTable("dbo.CreditContract");
        }
    }
}

Update the database and then execute the following code –

using (var db = new CodeFirstInheritanceDbContext())
{
    CreditContract creditContract = new CreditContract();
    creditContract.StartDate = new DateTime(2015, 1, 1);
    creditContract.EndDate = new DateTime(2015, 12, 31);
    creditContract.CreditLimit = 100000;
    creditContract.CreditLimitCurrency = "USD";
    db.Contracts.Add(creditContract);

    PrepaymentContract prepaymentContract = new PrepaymentContract();
    prepaymentContract.StartDate = new DateTime(2015, 1, 1);
    prepaymentContract.EndDate = new DateTime(2015, 12, 31);
    prepaymentContract.PrepaymentAmount = 10000;
    prepaymentContract.PrepaymentPeriod = "M";
    db.Contracts.Add(prepaymentContract);

    db.SaveChanges();
}

The CreditContract entity has been persisted to the CreditContract table –

tpc1

Whilst the PrepaymentContract entity has been persisted to the PrepaymentContract table –

tpc2

We can then query the entities exactly as we did when the TPH strategy was adopted. The consumer of the model does not need to be aware of the mapping strategy.

We changed the ContractID property from int to Guid. The reason is that we are asking the database server to generate unique identifiers for our records. The concrete entity types are mapped to separate tables, and the database server does not and should not know this. Integer identity fields are only unique within the context of a single table, and so if the type was int we would soon experience errors similar to the following –

The changes to the database were committed successfully, but an error occurred while updating the object context. The ObjectContext might be in an inconsistent state. Inner exception message: AcceptChanges cannot continue because the object's key values conflict with another object in the ObjectStateManager. Make sure that the key values are unique before calling AcceptChanges.

However, by changing the type to Guid we are, to all intents and purposes, generating unique fields.

The downside of doing this is that SQL Server GUID fields are 16 bytes in length, compared to 4 bytes for an int.

Compared to TPH, TPC is less performant when querying across types as the queries require a UNION between the tables. However, it will be equally performant when querying a single type.

As TPC stores the data in separate tables, it allows for non-nullable columns, hence ensuring data integrity. And it is clear which properties are required by which type.

Table Per Type (TPT)

With the Table By Type (TPT) strategy, both abstract and concrete types are mapped to their own tables in the database with the inheritance relationships represented by foreign key relationships.

To instruct EF to use this strategy we have 2 options.

The first option is to override the OnModelCreating method and add Fluent API code to instruct EF to map the CreditContract entity to a specific table, and the PrepaymentContract entity to a different specific table.

using System.Data.Entity;

namespace CodeFirstInheritance.Models
{
    public class CodeFirstInheritanceDbContext : DbContext
    {
        public DbSet<Contract> Contracts { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<CreditContract>().Map(m =>
            {
                m.ToTable("CreditContract");
            });

            modelBuilder.Entity<PrepaymentContract>().Map(m =>
            {
                m.ToTable("PrepaymentContract");
            });   
           
            base.OnModelCreating(modelBuilder);
        }
    }
}

The second option is to annotate the concrete types with the [Table()] attribute.

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace CodeFirstInheritance.Models
{
    public abstract class Contract
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int ContractID { get; set; }

        [DataType(DataType.Date)]
        [Required]
        public DateTime StartDate { get; set; }

        [DataType(DataType.Date)]
        [Required]
        public DateTime EndDate { get; set; }
    }

    [Table("CreditContract")]
    public class CreditContract : Contract
    {
        [Required]
        public float CreditLimit { get; set; }

        [Column(TypeName = "char")]
        [StringLength(3)]
        [Required]
        public string CreditLimitCurrency { get; set; }
    }

    [Table("PrepaymentContract")]
    public class PrepaymentContract : Contract
    {
        [Required]
        public float PrepaymentAmount { get; set; }

        [Column(TypeName = "char")]
        [StringLength(1)]
        [Required]
        public string PrepaymentPeriod { get; set; }
    }
}

Enabling migrations and adding an initial migration gives us the following –

namespace CodeFirstInheritance.Migrations
{
    using System;
    using System.Data.Entity.Migrations;

    public partial class Initial : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.Contracts",
                c => new
                    {
                        ContractID = c.Int(nullable: false, identity: true),
                        StartDate = c.DateTime(nullable: false),
                        EndDate = c.DateTime(nullable: false),
                    })
                .PrimaryKey(t => t.ContractID);

            CreateTable(
                "dbo.CreditContract",
                c => new
                    {
                        ContractID = c.Int(nullable: false),
                        CreditLimit = c.Single(nullable: false),
                        CreditLimitCurrency = c.String(nullable: false, maxLength: 3, fixedLength: true, unicode: false),
                    })
                .PrimaryKey(t => t.ContractID)
                .ForeignKey("dbo.Contracts", t => t.ContractID)
                .Index(t => t.ContractID);

            CreateTable(
                "dbo.PrepaymentContract",
                c => new
                    {
                        ContractID = c.Int(nullable: false),
                        PrepaymentAmount = c.Single(nullable: false),
                        PrepaymentPeriod = c.String(nullable: false, maxLength: 1, fixedLength: true, unicode: false),
                    })
                .PrimaryKey(t => t.ContractID)
                .ForeignKey("dbo.Contracts", t => t.ContractID)
                .Index(t => t.ContractID);

        }

        public override void Down()
        {
            DropForeignKey("dbo.PrepaymentContract", "ContractID", "dbo.Contracts");
            DropForeignKey("dbo.CreditContract", "ContractID", "dbo.Contracts");
            DropIndex("dbo.PrepaymentContract", new[] { "ContractID" });
            DropIndex("dbo.CreditContract", new[] { "ContractID" });
            DropTable("dbo.PrepaymentContract");
            DropTable("dbo.CreditContract");
            DropTable("dbo.Contracts");
        }
    }
}

3 tables are created, one per type, as shown in the following database schema –

dbdiagram

Update the database and then execute the following code –

using (var db = new CodeFirstInheritanceDbContext())
{
    CreditContract creditContract = new CreditContract();
    creditContract.StartDate = new DateTime(2015, 1, 1);
    creditContract.EndDate = new DateTime(2015, 12, 31);
    creditContract.CreditLimit = 100000;
    creditContract.CreditLimitCurrency = "USD";
    db.Contracts.Add(creditContract);

    PrepaymentContract prepaymentContract = new PrepaymentContract();
    prepaymentContract.StartDate = new DateTime(2015, 1, 1);
    prepaymentContract.EndDate = new DateTime(2015, 12, 31);
    prepaymentContract.PrepaymentAmount = 10000;
    prepaymentContract.PrepaymentPeriod = "M";
    db.Contracts.Add(prepaymentContract);

    db.SaveChanges();
}

And this is what we get in our Contract table –

tpt1

And in the CreditContract table –

tpt2

And finally the PrepaymentContract table –

tpt3

TPT is the least performant of the strategies, but enables the highest degree of database integrity and normalization.