How to change 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)

回答1:

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

sudo-code:

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;
    AlteredEntities.Add(TempEnt);
}
// 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.

回答2:

I decided to test this out. I started with this class, added a migration, updated the database, and inserted some data:

public class MyClass
{
    [Key]//Added this to get rid of the error: 
         //EntityType 'Class1' has no key defined. Define the key for this EntityType.
    public string Key { set; get; }
    public string Something { set; get; } 
}

Then I changed it to this and ran a second migration.

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

Here is the generated migration. By default, EF will make an integer column named "ID" the key - with identity true:

public override void Up()
{
    DropPrimaryKey("dbo.MyClasses");
    AddColumn("dbo.MyClasses", "ID", c => c.Int(nullable: false, identity: true));
    AlterColumn("dbo.MyClasses", "Key", c => c.String());
    AddPrimaryKey("dbo.MyClasses", "ID");
}

Then I changed Key to Name and ran another migration. But to avoid losing data, I added some sql before the column is dropped:

public override void Up()
{
    AddColumn("dbo.MyClasses", "Name", c => c.String());
    Sql("UPDATE dbo.MyClasses SET Name = [Key]"); //Manually added to migration
    DropColumn("dbo.MyClasses", "Key");
}

public override void Down()
{
    AddColumn("dbo.MyClasses", "Key", c => c.String());
    Sql("UPDATE dbo.MyClasses SET [Key] = Name"); //Manually added to migration
    DropColumn("dbo.MyClasses", "Name");
}