Entity Framework and T-SQL - Migrate data from one column to a new table and map foreign key property - Create one row even if duplicate data exist

Question

I'm using an entity that looks like this:

public class Product
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public string JiraProjectKey { get; set; }
    ...
}

Since we will be adding more settings we will create a new entity called JiraIntegrationSettings:

public class JiraIntegrationSettings
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public string JiraProjectKey { get; set; }
    ...
}

Product will then look like this:

public class Product
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public int? JiraIntegrationSettingsId { get; set; }

    public JiraIntegrationSettings JiraIntegrationSettings { get; set; }
    ...
}

The hard part here is that a product today can share JiraProjectKey but after migration every Product needs to have it's own JiraIntegrationSettings.

If a Product could share JiraProjectKey it would have been a fairly easy migration with T-SQL like this:

INSERT INTO [JiraIntegrationSettings] ([JiraProjectKey]) 
SELECT DISTINCT [JiraProjectKey]
FROM [Products]

UPDATE Products
SET Products.JiraIntegrationSettingsId = [JiraIntegrationSettings].Id
FROM [JiraIntegrationSettings]
WHERE Products.JiraProjectKey = [JiraIntegrationSettings].JiraProjectKey;

https://stackoverflow.com/a/11751050/3850405

How can I migrate data from one column to a new table and map a foreign key property and still create one row even if duplicate data exist?

score 0 · Answer 1 · answered Apr 21 '23 at 13:58

I started off by looking into loops for T-SQL

https://stackoverflow.com/a/62130/3850405

https://learn.microsoft.com/en-us/answers/questions/847097/loop-through-table-records-and-execute-stored-proc

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/while-transact-sql?view=sql-server-ver16

Getting the identity of an inserted row

https://stackoverflow.com/a/42655/3850405

UPDATE from a SELECT in SQL Server

https://stackoverflow.com/a/2334741/3850405

With this information I tried to generate a model using GPT-4. This worked out well because GPT-4 used a solution with DECLARE CURSOR.

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql?view=sql-server-ver16

With some modifications the final working T-SQL looked like this:

DECLARE @JiraIntegrationSettingsId INT;
DECLARE @JiraProjectKey NVARCHAR(255);
DECLARE @CreatedById NVARCHAR(255);
DECLARE @ProductId INT;

BEGIN TRANSACTION;

    -- Iterate through all rows in the Products table
    DECLARE products_cursor CURSOR FOR
    SELECT JiraProjectKey, Id, CreatedById
    FROM Products

    OPEN products_cursor;

    FETCH NEXT FROM products_cursor INTO @JiraProjectKey, @ProductId, @CreatedById;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Insert the JiraProjectKey into the JiraIntegrationSettings table
        INSERT INTO [JiraIntegrationSettings] ([Created], [Updated], [CreatedById], [UpdatedById], [JiraProjectKey]) 
        VALUES (GETUTCDATE(), GETUTCDATE(), @CreatedById, @CreatedById, @JiraProjectKey);

        SET @JiraIntegrationSettingsId = SCOPE_IDENTITY();

        -- Update the Products table with the JiraIntegrationSettingsId
        UPDATE Products
        SET JiraIntegrationSettingsId = @JiraIntegrationSettingsId
        WHERE Id = @ProductId;

        FETCH NEXT FROM products_cursor INTO @JiraProjectKey, @ProductId, @CreatedById;
    END;

    CLOSE products_cursor;
    DEALLOCATE products_cursor;

COMMIT TRANSACTION;

In Entity Framework Core 7 the UP migration then looked like this:

        protected override void Up(MigrationBuilder migrationBuilder)
        {
            var moveJiraProjectKeySql = @"
DECLARE @JiraIntegrationSettingsId INT;
DECLARE @JiraProjectKey NVARCHAR(255);
DECLARE @CreatedById NVARCHAR(255);
DECLARE @ProductId INT;

BEGIN TRANSACTION;

    -- Iterate through all rows in the Products table
    DECLARE products_cursor CURSOR FOR
    SELECT JiraProjectKey, Id, CreatedById
    FROM Products

    OPEN products_cursor;

    FETCH NEXT FROM products_cursor INTO @JiraProjectKey, @ProductId, @CreatedById;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Insert the JiraProjectKey into the JiraIntegrationSettings table
        INSERT INTO [JiraIntegrationSettings] ([Created], [Updated], [CreatedById], [UpdatedById], [JiraProjectKey]) 
        VALUES (GETUTCDATE(), GETUTCDATE(), @CreatedById, @CreatedById, @JiraProjectKey);

        SET @JiraIntegrationSettingsId = SCOPE_IDENTITY();

        -- Update the Products table with the JiraIntegrationSettingsId
        UPDATE Products
        SET JiraIntegrationSettingsId = @JiraIntegrationSettingsId
        WHERE Id = @ProductId;

        FETCH NEXT FROM products_cursor INTO @JiraProjectKey, @ProductId, @CreatedById;
    END;

    CLOSE products_cursor;
    DEALLOCATE products_cursor;

COMMIT TRANSACTION;
";

            migrationBuilder.Sql(moveJiraProjectKeySql);

            migrationBuilder.DropColumn(
                name: "JiraProjectKey",
                table: "Products")
                .Annotation("SqlServer:IsTemporal", true)
                .Annotation("SqlServer:TemporalHistoryTableName", "ProductsHistory")
                .Annotation("SqlServer:TemporalHistoryTableSchema", null)
                .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
                .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");
        }

Entity Framework and T-SQL - Migrate data from one column to a new table and map foreign key property - Create one row even if duplicate data exist

1 Answers1