Rebuild column when migrating from computed to non-computed

[stilettk]

I also have a problem with computed column migrations. The reason is computed column as in #12726, but this one doesn't depend on statement order.

My model has the following properties:

public string CustomData { get; set; }
public string InstanceType { get; set; }

I have added HasComputedColumnSql part to the following code:

modelBuilder.Entity<Resource>().Property(x => x.InstanceType).HasMaxLength(64)
    .HasComputedColumnSql($"JSON_VALUE({nameof(Resource.CustomData)}, '$.{nameof(Instance.InstanceType)}')");

Generated migration code:

public partial class ResourceJsonProperties : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AlterColumn<string>(
            name: "InstanceType",
            table: "Resources",
            maxLength: 64,
            nullable: true,
            computedColumnSql: "JSON_VALUE(CustomData, '$.InstanceType')",
            oldClrType: typeof(string),
            oldMaxLength: 64,
            oldNullable: true);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AlterColumn<string>(
            name: "InstanceType",
            table: "Resources",
            maxLength: 64,
            nullable: true,
            oldClrType: typeof(string),
            oldMaxLength: 64,
            oldNullable: true,
            oldComputedColumnSql: "JSON_VALUE(CustomData, '$.InstanceType')");
    }
}

The exception occurs when reverting the migration:

Reverting migration '20181204163459_ResourceJsonProperties'.
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (29ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      DECLARE @var0 sysname;
      SELECT @var0 = [d].[name]
      FROM [sys].[default_constraints] [d]
      INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
      WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Resources]') AND [c].[name] = N'InstanceType');
      IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Resources] DROP CONSTRAINT [' + @var0 + '];');
      ALTER TABLE [Resources] ALTER COLUMN [InstanceType] nvarchar(64) NULL;
System.Data.SqlClient.SqlException (0x80131904): Cannot alter column 'InstanceType' because it is 'COMPUTED'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)

Further technical details

EF Core version: 2.1.4
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017 15.9.2

[stilettk]

Just noticed that EF Core 2.2 was released. Just checked, this is still reproduced in 2.2.

[bricelam]

Looks like we need to rebuild the column (#329) when going from computed to non-computed.

[cmorgan091]

Still a problem in EF Core v3.1 over 2 years after being reported.

I guess the only option is to manually change the migration to a DropColumn followed by AddColumn