How do I fix a data migration error in EntityFrameworkCore?

asp.net-core entity-framework entity-framework-core msbuild

Question

Background

I have an existing dbset with a FooCol1 field that originally was a string data type.

public class Foo
{
    [Key]
    public Guid Id { get; set; }
    public string FooCol1 { get; set; }
    public string FooCol2 { get; set; }
    public string FooCol3 { get; set; }
}

I want to change this to an int so I've carried out the following in EntityFrameworkCore:

  1. Added a migration column and populated the migration column using SQL

Model

public class Foo
{
    [Key]
    public Guid Id { get; set; }
    public string FooCol1 { get; set; }
    public string FooCol2 { get; set; }
    public string FooCol3 { get; set; }
    public int? FooCol1Migration { get; set; } //new migration column
}

Migration

migrationBuilder.AddColumn<int>(
            name: "FooCol1Migration",
            table: "Foos",
            nullable: true);

migrationBuilder.Sql("Update Foos set FooCol1Migration=1"); //script to populate the new migration column
  1. Now the data is in the new column, remove the original property and rename the migration column.

Model

public class Foo
{
    [Key]
    public Guid Id { get; set; }
    public string FooCol2 { get; set; }
    public string FooCol3 { get; set; }
    public int? FooCol1 { get; set; } //renamed from FooCol1Migration
}

Migration

The original migration scripts provided by EFC was to alter the data type of FooCol1 column and drop the FooCol1Migration column.

I changed this to do a rename instead so it kept the data I migrated in step 1.

migrationBuilder.DropColumn(
            name: "FooCol1",
            table: "Foos");

migrationBuilder.RenameColumn(
            name: "FooCol1Migration",
            newName: "FooCol1",
            table: "Foos");

I think this is where my issue came from.

Issue

When I try and publish changes to my staging environment using Web Deploy, the SQL scripts are erroring on the line that does populates the migration column.

I've looked at the scripts generated and found it erroring here:

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20190212155305_Adding FooCol1Migration field to Foo')
BEGIN
    update Foos Set FooCol1Migration=1
END;

GO

Obviously FooCol1Migration doesn't exist any more and even though it's wrapped in a NOT EXISTS, SQL server is still checking the underlying data structure and stopping the publish from completing.

Question What's the best way to progress from here so that I can successfully publish my SQL scripts? I don't want to just add the FooCol1Migation column back in to allow the scripts to run.

The versions of software I'm using

  • Visual Studio 2017 15.7.4
  • Asp.Net Core 2.1.1
1
1
2/15/2019 12:46:33 PM

Accepted Answer

Okay, so I got this working by executing the data migration SQL using a stored procedure rather than running the SQL directly

migrationBuilder.Sql("Execute sp_executesql \"update Foos Set FooCol1Migration=1\"")

Now generates:

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20190212155305_Adding FooCol1Migration field to Foos')
BEGIN
    Execute sp_executesql "update Foos Set FooCol1Migration=1"
END;

GO

If anyone has any better / alternative solutions or comments around my issue, I'd still be interested to hear them.

1
2/15/2019 2:12:11 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow