Change data type in Model while preserving data (Entity Framework Core)

Question

I'm learning the Entity Framework core, and I want to perform migrations, with changing the data type to custom one, while preserving the data. I have the following model:

public class PageDataSections
{
    [Key]
    public int PageDataSectionId { get; set; }
    public string DataText { get; set; }
}

which is converted to this one:

public class PageDataSections
{
    [Key]
    public int PageDataSectionId { get; set; }
    public Translation DataText { get; set; }
}

While the class Translation is the following:

public class Translation
{
    [Key]
    public int TranslationId { get; set; }
    public string TranslateEN { get; set; }
    public string TranslateRU { get; set; }
    public string TranslateUA { get; set; }
}

The migration that was created:

public partial class ChangeDataTextColumn : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {

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

            migrationBuilder.DropColumn(
                name: "DataText",
                table: "PageDataSections");      

            migrationBuilder.CreateIndex(
                name: "IX_PageDataSections_DataTextTranslationId",
                table: "PageDataSections",
                column: "DataTextTranslationId");

            migrationBuilder.AddForeignKey(
                name: "FK_PageDataSections_Translations_DataTextTranslationId",
                table: "PageDataSections",
                column: "DataTextTranslationId",
                principalTable: "Translations",
                principalColumn: "TranslationId",
                onDelete: ReferentialAction.Restrict);

            migrationBuilder.Sql(@"
            INSERT INTO Translations(TranslateRU)
            OUTPUT inserted.TranslationId
            VALUES(SELECT DataText FROM PageDataSections)
            INTO PageDataSections(DataTextTranslationId)
");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropForeignKey(
                name: "FK_PageDataSections_Translations_DataTextTranslationId",
                table: "PageDataSections");

            migrationBuilder.DropIndex(
                name: "IX_PageDataSections_DataTextTranslationId",
                table: "PageDataSections");

            migrationBuilder.DropColumn(
                name: "DataTextTranslationId",
                table: "PageDataSections");

            migrationBuilder.AddColumn<string>(
                name: "DataText",
                table: "PageDataSections",
                nullable: true);
        }
    }

But Visual Studio is complaining with this one error:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

                  INSERT INTO Translations(TranslateRU)
                  OUTPUT inserted.TranslationId
                  VALUES(SELECT DataText FROM PageDataSections)
                  INTO PageDataSections(DataTextTranslationId)
System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'SELECT'.
Incorrect syntax near ')'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.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)
ClientConnectionId:14012b7c-68b2-4f43-9db1-b685734a2e37
Error Number:156,State:1,Class:15
Failed executing DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

            INSERT INTO Translations(TranslateRU)
            OUTPUT inserted.TranslationId
            VALUES(SELECT DataText FROM PageDataSections)
            INTO PageDataSections(DataTextTranslationId)
System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'SELECT'.
Incorrect syntax near ')'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.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)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:14012b7c-68b2-4f43-9db1-b685734a2e37
Error Number:156,State:1,Class:15
Incorrect syntax near the keyword 'SELECT'.
Incorrect syntax near ')'.

Can anyone please help?

EDIT

After @Fabio reply, I have tried his code

migrationBuilder.Sql(@"
    INSERT Translations (TranslateRU)
    OUTPUT inserted.TranslationId INTO PageDataSections(DataTextTranslationId)
    (SELECT DataText FROM PageDataSections) 
");

and it gave me the following:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (231ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      INSERT Translations (TranslateRU)
          OUTPUT inserted.TranslationId INTO PageDataSections(DataTextTranslationId)
          (SELECT DataText FROM PageDataSections)
System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'DataText'.
The target table 'PageDataSections' of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship. Found reference constraint 'FK_PageDataSections_Translations_DataTextTranslationId'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.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)
ClientConnectionId:3224c672-8dbd-4690-8953-ee6108b65cff
Error Number:207,State:1,Class:16
Failed executing DbCommand (231ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

INSERT Translations (TranslateRU)
    OUTPUT inserted.TranslationId INTO PageDataSections(DataTextTranslationId)
    (SELECT DataText FROM PageDataSections)
System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'DataText'.
The target table 'PageDataSections' of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship. Found reference constraint 'FK_PageDataSections_Translations_DataTextTranslationId'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.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)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:3224c672-8dbd-4690-8953-ee6108b65cff
Error Number:207,State:1,Class:16
Invalid column name 'DataText'.
The target table 'PageDataSections' of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship. Found reference constraint 'FK_PageDataSections_Translations_DataTextTranslationId'.

Which seems quite odd, because that column exists in the table:

UPDATE

This is how the foreign key is referenced

UPDATE With this queries I have achieved some result, but it not modifies rows, but rather adds.. How to "Update" rather than "Insert"?

            migrationBuilder.Sql(@"ALTER TABLE Pages DROP CONSTRAINT FK_Pages_PageDataSections_PageDataSectionId");

            migrationBuilder.Sql(@"
                INSERT Translations (TranslateRU)
                OUTPUT inserted.TranslationId INTO PageDataSections(DataTextTranslationId)
                SELECT DataText FROM PageDataSections;
");


            migrationBuilder.Sql(@"ALTER TABLE Pages ADD CONSTRAINT FK_Pages_PageDataSections_PageDataSectionId FOREIGN KEY (PageDataSectionId) REFERENCES PageDataSections(PageDataSectionId)");

Answer 1

When the problem is complex, I prefer divide that.

For this migration, I will make 2 update :

1. Create and populate Translations table
2. Populate PageDataSections table

1) Create and populate Translations table

See the temporary column Translations.PageDataSectionId

public partial class Update1 : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<int>(
            name: "PageDataSectionOrigin",
            table: "Translations",
            nullable: true);

        migrationBuilder.Sql(@"
            INSERT INTO Translations (TranslateRU, PageDataSectionOrigin)
            SELECT DataText, PageDataSectionId FROM PageDataSections
        ");
    }
}

2) Populate PageDataSections table

See the temporary column is deleted.

public partial class Update2 : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: "DataText",
            table: "PageDataSections");

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

        migrationBuilder.Sql(@"
            UPDATE PageDataSections SET DataTextTranslationId = (select TranslationId FROM Translations WHERE PageDataSectionOrigin=PageDataSectionId)
        ");

        migrationBuilder.DropColumn(
            name: "PageDataSectionOrigin",
            table: "Translations");
    }
}

You can combine this two update in one.

I don't know why, but EF have named the column PageDataSection.DataText to DataTextTranslationId. But you can replace easily this name.

Answer 2

I think your sql statement is invalid. Whole OUTPUT statement should be between INSERT and inserted values.
Besides you don't need VALUES keyword when you use result of anther query.

migrationBuilder.Sql(@"
    INSERT Translations (TranslateRU)
    OUTPUT inserted.TranslationId INTO PageDataSections(DataTextTranslationId)
    (SELECT DataText FROM PageDataSections) 
");