EF Core 10 generates invalid down migration for MSSQL nvarchar(max) to json upgrade

[i-shikunov]

Bug description

When upgrading from nvarchar(max) column that implicitly stored json (deserialization handled in-code) to ComplexProperty in EF Core 10, we have noticed that down migration generated by EF Core cannot be applied.

Reproduction steps:

1. Update settings column in DbContext with entity.ComplexCollection(x => x.Settings, b => b.ToJson());
2. ef migrations add UpdateIntuneConfigurationPolicySettingsCacheEntitySwitchJsonDataSettingsToNativeJson - generated migration pasted in the next section
3. ef database update
4. ef migrations remove -f // to force down migration

Expected behaviour: database column has nvarchar(max) state, same as before migration
Actual behaviour: Exception, posted into stack trace field

Your code

using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace Denver.DBL.Migrations
{
    /// <inheritdoc />
    public partial class UpdateIntuneConfigurationPolicySettingsCacheEntitySwitchJsonDataSettingsToNativeJson : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AlterColumn<string>(
                name: "Settings",
                table: "IntuneConfigurationPolicySettingsCache",
                type: "json",
                nullable: false,
                oldClrType: typeof(string),
                oldType: "nvarchar(max)");
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AlterColumn<string>(
                name: "Settings",
                table: "IntuneConfigurationPolicySettingsCache",
                type: "nvarchar(max)",
                nullable: false,
                oldClrType: typeof(string),
                oldType: "json");
        }
    }
}

Stack traces

Failed executing DbCommand (16ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DECLARE @var nvarchar(max);
SELECT @var = QUOTENAME([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'[IntuneConfigurationPolicySettingsCache]') AND [c].[name] = N'Settings');
IF @var IS NOT NULL EXEC(N'ALTER TABLE [IntuneConfigurationPolicySettingsCache] DROP CONSTRAINT ' + @var + ';');
ALTER TABLE [IntuneConfigurationPolicySettingsCache] ALTER COLUMN [Settings] nvarchar(max) NOT NULL;
Microsoft.Data.SqlClient.SqlException (0x80131904): Implicit conversion from data type json to nvarchar(max) is not allowed. Use the CONVERT function to run this query.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.Execute(IReadOnlyList`1 migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, Boolean beginTransaction, Boolean commitTransaction, Nullable`1 isolationLevel)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.<>c.<ExecuteNonQuery>b__3_1(DbContext _, ValueTuple`6 s)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IReadOnlyList`1 migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, Boolean commitTransaction, Nullable`1 isolationLevel)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateImplementation(DbContext context, String targetMigration, MigrationExecutionState state, Boolean useTransaction)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.<>c.<Migrate>b__20_1(DbContext c, ValueTuple`4 s)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Denver.Migrator.MigrationGen.PatchingMigrationsScaffolder.RevertIfApplied(String lastMigrationId, IReadOnlyDictionary`2 migrations, Boolean force) in C:\Projects\Nerdio\NMW_GH\Denver.Migrator\MigrationGen\PatchingMigrationsScaffolder.cs:line 168
   at Denver.Migrator.MigrationGen.PatchingMigrationsScaffolder.RemoveMigration(String projectDir, String rootNamespace, Boolean force, String language, Boolean dryRun) in C:\Projects\Nerdio\NMW_GH\Denver.Migrator\MigrationGen\PatchingMigrationsScaffolder.cs:line 78
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.RemoveMigration(String contextType, Boolean force, Boolean dryRun)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.RemoveMigrationImpl(String contextType, Boolean force, Boolean dryRun)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.RemoveMigration.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:f8dabd25-c240-486d-aa4e-0de3f8bc0565
Error Number:257,State:3,Class:16
Implicit conversion from data type json to nvarchar(max) is not allowed. Use the CONVERT function to run this query.

Verbose output

EF Core version

10.0.4

Database provider

No response

Target framework

No response

Operating system

No response

IDE

No response