How to alter datatypes of primary and foreign keys using EF migration?

Question

I have to change the datatypes from int to bigint on all of the primary and foreign keys that are used in an already existing database / tables.

To work around the following error...

Msg 5074, Level 16, State 8, Line 1
The object 'Primary Key Constraint Name' is dependent on column 'Primary Key Column Name'.
ALTER TABLE ALTER COLUMN Column Name failed because one or more objects access this column.

I first have to drop the constraint and then re-create it later on. This is how I do it using SSMS:

alter table Meta.Playground
drop constraint Pk_Playground
go

alter table Meta.Playground
alter column id bigint not null
go

alter table Meta.Playground
add constraint Pk_Playground primary key (id)
go

But what would be the best way to do this using entity-framework's Up() and Down() methods?

I don't know how I can retrieve the key and constraint names from within those methods.

By using SQL I would retrieve them as follows:

select COLUMN_NAME, CONSTRAINT_NAME 
into #result
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where TABLE_NAME = @table and TABLE_SCHEMA = @schema

Answer 1

I have the same issue and the solution is working fine

I have two table Skill and JobSkills and Skill.Id is primary key and identity

this solution is working fine

1. Skill.Id and JobSkills.SkillId changed to Long as the screen shots

2. add new migration EditSkillIdBigint

3. Replace code from up and down methods and write this code in up method

        migrationBuilder.Sql("DROP INDEX [IX_JobSkills_SkillId] ON [dbo].[JobSkills]");
        migrationBuilder.Sql("ALTER TABLE [dbo].[JobSkills] DROP CONSTRAINT [FK_JobSkills_Skill_SkillId]");
        migrationBuilder.Sql("ALTER TABLE [dbo].[Skill] DROP CONSTRAINT [PK_Skill] WITH ( ONLINE = OFF )");
        migrationBuilder.Sql("ALTER TABLE [dbo].[Skill] ALTER column Id bigint;");
        migrationBuilder.Sql("ALTER TABLE [dbo].[JobSkills] ALTER column skillid bigint;");
        migrationBuilder.Sql(@"ALTER TABLE [dbo].[Skill] ADD  CONSTRAINT [PK_Skill] PRIMARY KEY CLUSTERED 
        (  [Id] ASC)WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY] GO");
   
        migrationBuilder.Sql(@"CREATE NONCLUSTERED INDEX [IX_JobSkills_SkillId] ON [dbo].[JobSkills]
        ([SkillId] ASC
        )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO");
   
        migrationBuilder.Sql("ALTER TABLE [dbo].[JobSkills]  WITH CHECK ADD  CONSTRAINT [FK_JobSkills_Skill_SkillId] FOREIGN KEY([SkillId]) REFERENCES[dbo].[Skill]([Id])");
        migrationBuilder.Sql("ALTER TABLE [dbo].[JobSkills] CHECK CONSTRAINT [FK_JobSkills_Skill_SkillId]");
   

Answer 2

I realized that the way I am trying to solve this problem is ways to complicated.

Adding an additional migration step solves my problem in an astonishing easy way. So no need for an additional SQL script. Entity framework is fully able to migrate the primary and foreign keys (at least version 6.1.3 which I am using).

This is what the code looks like after calling Add-Migration with the appropriate parameters.

public override void Up()
{
    DropForeignKey(...)
    // ...
    DropIndex(...) 
    // ... 
    DropPrimaryKey(...)
    // ...
    AlterColumn(...)
    // ...
    AddPrimaryKey(...)
    // ...
    CreateIndex(...)
    // ...
    AddForeignKey) 
    // ...
}

Answer 3

For working with constraints in EF migrations you will need to execute SQL statements directly. You can do this with the Sql function.

For example:

public override void Up()
{
    Sql("ALTER TABLE Meta.Playground DROP CONSTRAINT Pk_Playground");
}

To support dynamic name of the constraint you're gonna want to pass in a SQL statement that gets the name and then executes the ALTER statement.

There's an example of that here: EF migration for changing data type of columns

 DECLARE @con nvarchar(128)
 SELECT @con = name
 FROM sys.default_constraints
 WHERE parent_object_id = object_id('dbo.Received')
 AND col_name(parent_object_id, parent_column_id) = 'FromNo';
 IF @con IS NOT NULL
     EXECUTE('ALTER TABLE [dbo].[Received] DROP CONSTRAINT ' + @con)

This then would be the statement you pass via the Sql function.