Entity Framework change Key Type

Question

I have created a model with various values but stupidly used a GUID for my key, I am currently attempting to change that to an Int but am getting an error when I do so.

I have run the enable migration command:

Enable-Migrations -Force -ContextTypeName project.Models.MyContext

This creates the migration I would expect but when I run:

Update-Database -Force

The error I'm getting is:

Operand type clash: uniqueidentifier is incompatible with int

I don't care about the data currently contained within the database since it is just using a SQL Server Express database just now, but I would prefer to find a way to migrate this instead of just having to drop the DB altogether, what's the best way to do this?

I have already got

Database.SetInitializer(new DropCreateDatabaseIfModelChanges<MyContext>());

in Global.asax.

Answer 1

I would expect that the generated migration is using AlterColumn to try and change the type of the field from guid to int. This is not possible, so you'll need to modify the generated migration yourself:

Assuming your table is dbo.People and the key is called Id, you probably have this at the moment:

DropPrimaryKey("dbo.People");
AlterColumn("dbo.People", "Id", c => c.Int(nullable: false, identity: true));
AddPrimaryKey("dbo.People", "Id");

Change it to:

DropPrimaryKey("dbo.People");
DropColumn("dbo.People", "Id");
AddColumn("dbo.People", "Id", c => c.Int(nullable: false, identity: true));
AddPrimaryKey("dbo.People", "Id");

Note that if you've got this key referenced elsewhere, this technique will not work if you've got any data present, as the keys are all being regenerated.

Answer 2

Update for EF Core generated migrations:

dotnet : System.Data.SqlClient.SqlException: Operand type clash: int is incompatible with uniqueidentifier

change

migrationBuilder.AlterColumn<Guid>(
            name: "VATID",
            schema: "catalogue",
            table: "Products",
            nullable: false,
            oldClrType: typeof(int));

into

        migrationBuilder.DropColumn(
            name: "VATID",
            schema: "catalogue",
            table: "Products");

        migrationBuilder.AddColumn<Guid>(
            name: "VATID",
            schema: "catalogue",
            table: "Products",
            nullable: false);

Of course, this will destroy your data for the certain column. But they obviously cannot be converted into GUID.