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
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
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.