c# - Switching to artificial key with different type in Entity Framework Migrations

Question

I'm working on an Entity Framework Code First project where, previously, I had a class with a field called "Id" that was a string type and using the hash.

That's specified like this:

[Key]
public string Id { get; set; }

That's no longer what I need because with updates I can now have duplicate values, so I want to change it to look like this:

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }

[Required]
[Index]
public string Hash { get; set; } //This represents what used to be Id

I ran into some trouble and went digging and found a bug report suggesting that this would work in Entity Framework 6.1.0, so I've updated, but it's still not really working for me. I hand-entered some SQL into the migration file to set the Hash column to the Id value before it gets blown away, but the update fails when it goes to update the foreign keys because it can't figure out how to go from an alphanumeric nvarchar to an int. Of course, what I'd like it to do is insert the integer ID that corresponds to the old hash.

Is there some nondestructive way I can achieve this?

Answer 1

Well, I was a little bit hesitant to post the solution I ended up going with, because it feels like a bit of a hack, but since I don't really expect any other answers at this point, here it is.

I ended up hand-editing the migration with this method. Essentially, I'm dropping the constraints, doing an update query to get an nvarchar representation of the integer key, then converting to an int and adding the constraints again.

public override void Up()
  {
    DropIndex("dbo.ValueSetElements", new[] { "Parent_Id" });
    DropIndex("dbo.SectionElements", new[] { "Choices_Id" });
    DropForeignKey("dbo.ValueSetElements", "Parent_Id", "dbo.ValueSets");
    DropForeignKey("dbo.SectionElements", "Choices_Id", "dbo.ValueSets");
    DropPrimaryKey(ValueSetTable, "PK_dbo.ValueSets");
    RenameColumn(ValueSetTable, "Id", "Hash");
    AddColumn(ValueSetTable, "Id", c => c.Int(nullable: false, identity: true, name: "Id"));
    AddPrimaryKey(ValueSetTable, "Id");
    CreateIndex(ValueSetTable, "Hash");
    Sql("UPDATE dbo.SectionElements SET Choices_Id = CONVERT(nvarchar(10), (SELECT Id FROM dbo.ValueSets WHERE dbo.ValueSets.Hash = dbo.SectionElements.Choices_Id))");
    AlterColumn("dbo.SectionElements", "Choices_Id", c => c.Int());
    AddForeignKey("dbo.SectionElements", "Choices_Id", "dbo.ValueSets", "Id");
    CreateIndex("dbo.SectionElements", "Choices_Id");
    Sql("ALTER TABLE dbo.ValueSetElements DROP CONSTRAINT [DF__ValueSetE__Paren__0F63164F]");
    Sql("UPDATE dbo.ValueSetElements SET Parent_Id = CONVERT(nvarchar(10), (SELECT Id FROM dbo.ValueSets WHERE dbo.ValueSets.Hash = dbo.ValueSetElements.Parent_Id))");
    AlterColumn("dbo.ValueSetElements", "Parent_Id", c => c.Int(nullable: false));
    AddForeignKey("dbo.ValueSetElements", "Parent_Id", ValueSetTable, "Id", cascadeDelete: true);
    CreateIndex("dbo.ValueSetElements", "Parent_Id");
  }