How to Change the name of a primary key in EF Code

问题:

I have a scenario where i would like to change the primary key name in an entity and be able to run update-database -force. See below for code and error getting when i try.

Entity was:

public class Team
{
    [Key]
    [HiddenInput(DisplayValue = false)]
    public virtual int Id { get; set; }

    [Display(Name = "Full Name:")]
    public virtual string Name { get; set; }
}

Entity Changed to:

public class Team
{
    [Key]
    [HiddenInput(DisplayValue = false)]
    public virtual int TeamId { get; set; }

    [Display(Name = "Full Name:")]
    public virtual string Name { get; set; }
}

When i run Update-database -Force i get the following error.

Multiple identity columns specified for table 'Teams'. Only one identity column per table is allowed.

Its a matter of naming convention and i need this to be TeamId when i reference it latter, simply Id conflicts with child entity classes.

Any ideas on how i can do this successfully?

回答1:

Depends on the version of EF you are using. Even with migrations the result that you will see is something like:

"drop column Id " and "add column TeamId".

With this you will lose all values and "child connections"......

The only "secure" solution I am seeing at this point is a mix of Migrations and "hand SQL operations".

EASY solution:

1- taking in consideration that you already have a "base" migration creating the table with ID, now create the new migration with the "update". Now do NOT run it yet.

2- Open that file and write a new line BEFORE the generated lines and use a SQL command, something like this:

     SQL("ALTER TABLE table_name RENAME COLUMN old_name to new_name;");

This will change the name BEFORE the migration deletes the column and create a new one, what will happen is: you change the name before the delete, then the delete is executed but it will "fail" but it will not hurt nothing.

But now you ask: why do I do this? well if you are using migrations even if you delete the lines to delete the column and create a new one, the next time you create automatically a new migration file this new lines will be there...... this is why.

UPDATED ANSWERS #1

When I talk about Entity Framework Migrations I am referring to this: http://blogs.msdn.com/b/adonet/archive/2012/02/09/ef-4-3-code-based-migrations-walkthrough.aspx When you Run the ‘Add-Migration AddBlogUrl’ command in Package Manager Console, a new file (*.cs) is created.

Example of this file migration file with SQL commands:

public partial class AddAbsencesTypesAndCategories : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "pvw_AbsenceType",
                c => new
                    {
                        Id = c.Int(nullable: false, identity: true),
                        Name = c.String(nullable: false),
                        CountAsVacation = c.Boolean(nullable: false),
                        IsIncremental = c.Boolean(nullable: false),
                    })
                .PrimaryKey(t => t.Id);

          .....

            AddColumn("pvw_Absence", "CategoryId", c => c.Int(nullable: false));
                        AddForeignKey("pvw_Absence", "StatusId", "pvw_AbsenceStatusType", "Id");
            AddForeignKey("pvw_Absence", "CategoryId", "pvw_AbsenceType", "Id");
            CreateIndex("pvw_Absence", "StatusId");
            CreateIndex("pvw_Absence", "CategoryId");
            DropColumn("pvw_Absence", "MainCategoryId");
            DropColumn("pvw_Absence", "SubCategoryId");
           ......
            Sql(@"
                                        SET IDENTITY_INSERT [dbo].[pvw_AbsenceStatusType] ON
                    INSERT pvw_AbsenceStatusType (Id, Name) VALUES (1, N'Entwurf')                       
                                        SET IDENTITY_INSERT [dbo].[pvw_AbsenceStatusType] OFF
            ");    
            .....
        }

        public override void Down()
        {
            ........
        }

回答2:

After fiddling with suggestion by both marvc1 and emanyalpsid. I decided to just drop the database and creating it again. This is done by simply deleting the database under Server Explorer in VS2012, and also make sure that the .mdf file under App_Data is also deleted. The .mdf file is usually hidden, to see it just under Solution Explorer toolbar click on Show All Files and you will see it. when those steps are done, simply run the code below in Package Manager Console:

update-database -Verbose

-Verbose simply lets you verify what you are creating.

marvc1's Answer

Works just fine, except it does not change names in the database, if you are not too worried about database names, it is the safest way to go about it. By names in the database i mean, In the entity Team, Id would still be Id and not TeamId

回答3:

Easiest solution is to not rename the primary key in the database and instead map your class to your primary key and give it any name you want. Like this:

public class Team
{
    [Key]
    [HiddenInput(DisplayValue = false)]
    [Column("Id")] //this attribute maps TeamId to the column Id in the database
    public virtual int TeamId { get; set; }

    [Display(Name = "Full Name:")]
    public virtual string Name { get; set; }
}

Personally, I would keep the class name as Id. The naming convention [TableName + Id] is old school and overkill for a primary key (for a foreign key it is good). To me it just adds noise to your lines of code. team.TeamId is no better than team.Id.

回答4:

User Dryadwoods recommends to execute the following sql inside migration:

SQL("ALTER TABLE table_name RENAME COLUMN old_name to new_name;");

but :

It is not possible to rename a column using the ALTER TABLE statement in MS SQL Server. Use sp_rename instead.

so, I change it to the following code:

Sql("exec sp_rename 'dbo.Batches.BatchNo', 'Id', N'COLUMN';");

pay attention, that old name should be fully name, but new name should be short. Otherwise it will work incorrectly

回答5:

I have a nice solution base on @Marvin Rounce solution. This solution apply the change both class and database very easily.

1. map your class, this step rename the column only in the database.

public class Team

 {
        [Key]
        [HiddenInput(DisplayValue = false)]
        [Column("ID")] 
        public virtual int TeamId { get; set; }
    ....
    }

2. in Package Manager Console add a migration.

PM> Add-Migration rename_TeamID

3. update the database.

PM> update-database

4. rename the column in the class to this name, this step rename the colum in your project too.

note: you don't need the key attribute, the EF know that ID keyword is a primary key.

public class Team
        {

       [HiddenInput(DisplayValue = false)]
       public virtual int ID { get; set; }

    ....
    }

you don't need to add migration because the last change doesn't affect database.