EF Migrations: Move Table from 2 Column PK to Single Column causes ALTER before DROP and fails

Question

I'm using EF 4.3.1 Code 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. I then moved it to a single-column Primary Key:

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

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

This causes EF to fail in the next automatic migration, complaining:

ALTER TABLE [Product] ALTER COLUMN [Store] nvarchar

The object 'PK_Product' is dependent on column 'Store'. ALTER TABLE ALTER COLUMN Store failed because one or more objects access this column.

Clearly the PK_Product needs to be dropped before attempting to fire this ALTER statement (why is it altering the column at all?), but instead the migration fails.

Am I doing something wrong or is this a bug? Workarounds?

Answer 1

You won't be able to do this with an automatic migration. You'll have to create a migration using Add-Migration and then change it so it only modifies the PK.

The migration can be as simple as:

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 is altering the column because, when it's part of a Key, it's implicitly NOT NULL. You can leave it as-is, add a [Required] attribute, or allow EF to alter the column after dropping the PK.