How to alter relationship keeping existing data in entity framework code first?

Question

I have two entities

public class Account
    {
        [Key]
        public int Id { get; set;
        public int MemberInfoId { get; set; }
        public Member MemberInfo { get; set; }
        //Other Properties
    }

public class Member
    {
        [Key]
        public int Id { get; set; }
        //Other Properties
    }

With following relationship

modelBuilder.Entity<Account>()
                .HasRequired(a => a.MemberInfo)
                .WithMany()
                .HasForeignKey(a => a.MemberInfoId)
                .WillCascadeOnDelete(true);

However, an Account has one Member only so the following relation is better suited (I guess)

modelBuilder.Entity<Account>()
                .HasRequired(a => a.MemberInfo)
                .WithRequiredPrincipal()
                .WillCascadeOnDelete(true);

But code first migration won’t migrate to that. It gives the following error

The object 'PK_dbo.Members' is dependent on column 'Id'.
The object 'FK_dbo.Accounts_dbo.Members_MemberInfo_Id' is dependent on column 'Id'.
ALTER TABLE DROP COLUMN Id failed because one or more objects access this column.

What can I do to alter the relation without recreating the database to keep the already inserted data?

Answer 1

First, you can read this page on mixing code-based migrations with automatic migrations to see if you even want to go this route in the first place. Generally, it's not a good idea if you have a team involved.

Next, it might be good to know that if you change the relationship between Member and Account to a one-to-one, Entity Framework requires that the primary key on the dependent side also be the foreign key. The column that used to be the primary key on the dependent side will become useless from EF's perspective. You also won't need the Account.MemberInfoId anymore, either.

Finally to create a migration, after you finish modifying the models, you can run Add-Migration with a migration name. This will create a migration file with a DbMigration class that you can modify. You'll probably need to do something like the following:

• Create a new column in Member that will hold the new foreign key
• Use Sql() to update values in that column with the primary key of the associated account
• Drop the foreign key constraint, index, and primary key
• Drop the account.memberinfo_id column and member.id column (which is optional, but if you don't drop the member.id column, you'll have to make sure to map the model's Member.Id property to the column created above).
• Rename new column in member to id
• Add primary key to new column in Member
• Add index and foreign key

I'm sure I've missed something, but that's the general gist. I'd probably also backup everything, since something's guaranteed to go wrong the first five times or so.