Adding a lookup field to an existing table using Entity Framework code first migrations

As you build out a site using the code first approach, at some point you will most likely need to add an additional lookup field to a class. There are a couple of steps required above what’s automatically done for you by EF code first migrations to make things work happily. If you consider the following simple class

class Client
    {
        public int ID { get; set; }
        public string ClientName { get; set; }
        public string ClientAddress { get; set; }
    }

If you add a migration at this point you’ll end up with something like

public partial class InitialSetup : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "Clients",
                c => new
                    {
                        ID = c.Int(nullable: false, identity: true),
                        ClientName = c.String(),
                        ClientAddress = c.String(),
                    })
                .PrimaryKey(t => t.ID);

        }

        public override void Down()
        {
            DropTable("Clients");
        }
    }

Once this class has been added to the database and you have existing data, adding another non nullable lookup field can be tricky. When you add your lookup and it tries to add the lookupID field to the existing Clients table, it’ll fail because the existing data will fail referential integrity due to their being no data in the lookup table. Once we added a lookup named ClientStatus to the model, it would look like

class Client
    {
        public int ID { get; set; }
        public string ClientName { get; set; }
        public string ClientAddress { get; set; }
        public int ClientStatusID { get; set; }

        public virtual ClientStatus ClientStatus { get; set; }
    }

    class ClientStatus
    {
        public int ClientStatusID { get; set; }
        public int ClientStatusText { get; set; }
    }

and our migration code will look like

public override void Up()
        {
            CreateTable(
                "ClientStatus",
                c => new
                    {
                        ClientStatusID = c.Int(nullable: false, identity: true),
                        ClientStatusText = c.Int(nullable: false),
                    })
                .PrimaryKey(t => t.ClientStatusID);

            AddColumn("Clients", "ClientStatusID", c => c.Int(nullable: false));
            AddForeignKey("Clients", "ClientStatusID", "ClientStatus", "ClientStatusID", cascadeDelete: true);
            CreateIndex("Clients", "ClientStatusID");
        }

        public override void Down()
        {
            DropIndex("Clients", new[] { "ClientStatusID" });
            DropForeignKey("Clients", "ClientStatusID", "ClientStatus");
            DropColumn("Clients", "ClientStatusID");
            DropTable("ClientStatus");
        }

The problem lies between the CreateTable and the AddColumn commands. The lookup table will be created, but the ClientStatusID non nullable field can’t be added to the Clients table as there are no records on the ClientStatus table.

To get around this in my code, I am tweaking the Up function to insert a default row into the lookup table and assigning the value of 1 to the default value of the ClientStatusID field in the Clients table. I’m adding

Sql("INSERT INTO ClientStatus (ClientStatusName) VALUES ('Active')");

and updating

AddColumn("Clients", "ClientStatusID", c => c.Int(nullable: false));

to be

AddColumn("Clients", "ClientStatusID", c => c.Int(nullable: false, defaultValue:1));

As we are just creating the ClientStatus Table, we can reasonably assume that the first record created will have an CLientStatusID of 1.