EF 6 Merge records and update all dependant FKs programmatically

Question

I want to be able to merge duplicate records in 1 table, and update all child entities whose FK was the originalRecordId to the destinyRecordId. This could be done manually, but it would be a daunting task as the parent table could have 40+ relationships, and this has to be done in multiple parent tables. I am using EF 6 with code first.

I have been able to get all navigation properties using the following (credit goes to @zev-spits in this question):

public List<PropertyInfo> GetNavigationProperties(T entity)
{
    var t = entity.GetType();
    var elementType = ((IObjectContextAdapter)context).ObjectContext.CreateObjectSet<T>().EntitySet.ElementType;
    return elementType.NavigationProperties.Select(np => entityType.GetProperty(np.Name)).ToList();
}

I have no idea where to go from here.

Is there a way to programmatically do this task? I would like to pass in a originalRecord and a destinyRecord, obtain all related entities of the originalRecord and update the FK with the destinyRecordId.

Have you considered doing this using SQL, directly against the database, instead? — mjwills, Nov 01 '18 at 12:57
Thnaks for the suggestion @mjwills. Any pointers on how to achieve it directly in SQL? — aplon, Nov 01 '18 at 13:07

score 0 · Answer 1 · answered Nov 05 '18 at 02:07

I finally took @mjwillis suggestion and resolved this directly in SQL Server with the following:

DECLARE @sql nvarchar (255);
DECLARE @refTableName varchar(255) = 'Opportunities'
DECLARE @refColName varchar(255) = 'Id'
DECLARE @refValue uniqueidentifier = '2a8a0f61-fe5c-4dd7-8e6d-00c8340333c4'

 Deleted User Key
DECLARE @refNewValue uniqueidentifier = '99999999-9999-9999-9999-999999999999'

DECLARE RelatedTableCursor CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT
    result.TableName,
    result.ColumnName
FROM
(
    SELECT
        OBJECT_NAME(f.parent_object_id) AS TableName,
        COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
        SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
        OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
        COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
    INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
) result
WHERE
    ReferenceTableName = @refTableName AND ReferenceColumnName = @refColName

OPEN RelatedTableCursor
WHILE 1 = 1
BEGIN 
    DECLARE @tableName varchar(255)
    DECLARE @columnName varchar(255)

    FETCH NEXT FROM RelatedTableCursor into @tableName, @columnName

    IF @@fetch_status <> 0
    BEGIN
        BREAK
    END

    DECLARE @sql nvarchar (255);
    SET @sql = 
        N'UPDATE [' + @tableName + '] ' +
        'SET ' +
        '   [' + @columnName + '] = ''' + CONVERT(NVARCHAR(255), @refNewValue) + ''' ' +
        'WHERE [' + @columnName + '] = ''' + CONVERT(NVARCHAR(255), @refValue) + ''' ';

    -- Prevent some error (Duplicate record) that occurs when replace data in mapping table.
    BEGIN TRY
        -- exec sp_executesql @sql;
        select @sql
    END TRY
    BEGIN CATCH
    END CATCH
END
CLOSE RelatedTableCursor
DEALLOCATE RelatedTableCursor

SET @sql = 'DELETE FROM [' + @refTableName + '] WHERE ' + @refColName + ' = ''' + CONVERT(NVARCHAR(255), @refValue) + '''';
EXEC sp_executesql @sql;

EF 6 Merge records and update all dependant FKs programmatically

1 Answers1