EF Migrations: moving a table of 2 PK columns to Single Column causes ALTER before DROP and does not work

Question

I am using EF 4.3.1 First First Migrations. I have a table like:

public class Product { [Key] [Column(Order=0)] [MaxLength(100)] public string Store { get; set; } [Key] [Column(Order=1)] [MaxLength(100)] public string Sku { get; set; } }​ 

I have an existing table created with the above code. Then I moved it to one primary key column:

 public class Product { [MaxLength(100)] public string Store { get; set; } [Key] [MaxLength(100)] public string Sku { get; set; } }​ 

This will cause EF to crash in the following automatic migration, complaining:

ALTER TABLE [Product] ALTER COLUMN [Store] nvarchar

The PK_Product object depends on the Store column. ALTER TABLE ALTER COLUMN Could not be stored because one or more objects are accessing this column.

Obviously, PK_Product needs to be discarded before trying to run this ALTER statement (why does it even change the column?), But instead the migration fails.

Am I doing something wrong or is it a mistake? Workarounds?

Answer 1

You cannot do this with automatic migration. You will need to create the migration using Add-Migration , and then modify it so that it only modifies PK.

Migration can be simple:

 public partial class TheMigration : DbMigration { public override void Up() { DropPrimaryKey("Products", new[] { "Store", "Sku" }); AddPrimaryKey("Products", "Sku"); } public override void Down() { DropPrimaryKey("Products", new[] { "Sku" }); AddPrimaryKey("Products", new[] { "Store", "Sku" }); } } 

EF modifies the column because when it is part of Key , it is implicitly NOT NULL . You can leave it as it is, add the [Required] attribute, or allow EF to change the columns after , dropping the PK.