How to change the string key to int


I use C# EntityFramework and CodeFirst. Also I use migrations (automatic & manual migrations).

I have a model -

public class MyClass
    public string Key { set; get; }
    public string Something { set; get; }

And I already have a database with rows.

Now I want to change the model :

a. Change "Key" column to "Name" (which is not a problem with migration, especially automatic migrations)

b. Add a new property - an int "Key" that will be identity (1, 2, 3...).

How can I do that without deleting the existing rows? (I want them to get an automatic id)

as far as I'm aware you cannot alter the type schema of a database without dropping the existing entries.

given the above, I would make a new table, and foreach entity in the table submit it to the new table with the string key's converted to ints (assuming they are all valid ints in string format).


list<Entity> Entities = new list<Entity>(); //put all the database entities in here.
List<AlteredEntity> AlteredEntities = new List<AlteredEntity>(); // for the new table

foreach (Entity r in Entities)
    AlteredEntity TempEnt = new AlteredEntity();
    TempEnt.Key = Convert.toint32(r.Key);
    TempEnt.Something = r.Something;
// iterate through AlteredEntities to input into the database

I'd do it this way so that if any entities are lost or there is a disruption with the database, you still have the original records intact. once you verify that the original data has been transfered entirely you can delete the old table and switch in the new one.