Migrate Ids to Udis for Umbraco.MultiNodeTreePicker

MultiNodeTreePickerIdToUdiMigrator.cs

using System;
using System.Linq;
using Umbraco.Core;
using Umbraco.Core.Logging;
using Umbraco.Web;

namespace Sniper.Umbraco
{
    public static class MultiNodeTreePickerIdToUdiMigrator
    {
        private class Row
        {
            public int id { get; set; }
            public int contentNodeId { get; set; }
            public string alias { get; set; }
            public string dataNvarchar { get; set; }
            public string dataNtext { get; set; }
            public int? dataInt { get; set; }
            public string preValue { get; set; }
        }

        public static void MigrateIdsToUdis(ApplicationContext applicationContext)
        {
            var database = applicationContext.DatabaseContext.Database;

            string sql = @"SELECT cmsPropertyData.id, cmsPropertyData.contentNodeId, cmsPropertyType.alias, dataNvarchar, dataNtext, dataInt, preValue=cmsDataTypePreValues.value, dbo.cmsDocument.*
FROM dbo.cmsPropertyData
JOIN dbo.cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid
JOIN dbo.cmsDataType ON cmsDataType.nodeId = cmsPropertyType.dataTypeId
JOIN dbo.cmsDataTypePreValues ON cmsDataTypePreValues.datatypeNodeId = cmsDataType.nodeId AND cmsDataTypePreValues.alias = 'startNode'
JOIN dbo.cmsContentVersion ON cmsContentVersion.VersionId = cmsPropertyData.versionId
JOIN dbo.umbracoNode ON umbracoNode.id = dbo.cmsContentVersion.ContentId
JOIN dbo.cmsDocument ON cmsDocument.nodeId = umbracoNode.id
WHERE cmsDataType.propertyEditorAlias IN ('Umbraco.MultiNodeTreePicker2')
AND (dataNvarchar IS NOT NULL OR dataInt IS NOT NULL)
AND (dbo.cmsDocument.published=1 OR dbo.cmsDocument.newest=1 OR dbo.cmsDocument.updateDate > (SELECT updateDate FROM dbo.cmsDocument innerDoc WHERE innerDoc.nodeId = dbo.cmsDocument.nodeId AND innerDoc.published=1 AND newest=1))
ORDER BY contentNodeId, dbo.cmsDataType.propertyEditorAlias";

            var treePickerDataToMigrate = database.Query<Row>(sql).ToList();
            if (treePickerDataToMigrate.Any())
            {
                foreach (var propertyData in treePickerDataToMigrate)
                {
                    int[] ids;

                    if (propertyData.dataInt != null)
                    {
                        ids = new[] { propertyData.dataInt.Value };
                    }
                    else if (propertyData.dataNvarchar != null)
                    {
                        ids = propertyData.dataNvarchar.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).Select(int.Parse).ToArray();
                    }
                    else
                    {
                        LogHelper.Info(typeof(MultiNodeTreePickerIdToUdiMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) skipping property {propertyData.alias} - null dataInt and null dataNvarchar");
                        continue;
                    }

                    string csv = string.Join(",", ids);
                    var type = propertyData.preValue.Contains("\"type\": \"content\"") ? "document" : "media";
                    Guid[] uniqueIds = null;
                    string uniqueIdsCsv = string.Empty;
                    if (ids.Any())
                    {
                        uniqueIds = database.Query<Guid>($"SELECT uniqueId FROM umbracoNode WHERE id IN ({csv})").ToArray();
                        uniqueIdsCsv = string.Join(",", uniqueIds.Select(id => $"umb://{type}/{id:N}"));
                    }

                    LogHelper.Info(typeof(MultiNodeTreePickerIdToUdiMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) converting property {propertyData.alias} from {csv} to {uniqueIdsCsv}");
                    database.Execute("UPDATE cmsPropertyData SET dataInt=NULL, dataNvarchar=NULL, dataNtext=@0 WHERE id=@1", uniqueIdsCsv, propertyData.id);
                }

                LogHelper.Info(typeof(MultiNodeTreePickerIdToUdiMigrator), () => $"MigrateIdsToUdis: republishing all nodes to update xml cache (equivalent to /umbraco/dialogs/republish.aspx?xml=true)");
                var contentService = ApplicationContext.Current.Services.ContentService;
                contentService.RePublishAll();
                umbraco.library.RefreshContent();
                LogHelper.Info(typeof(MultiNodeTreePickerIdToUdiMigrator), () => $"MigrateIdsToUdis: republishing complete");


            }
        }
    }

}

StartupHandler.cs

using System.Configuration;
using Umbraco.Core;

namespace Sniper.Umbraco
{
    internal class StartupHandler : ApplicationEventHandler
    {
        protected override void ApplicationStarted(UmbracoApplicationBase umbracoApplication, ApplicationContext applicationContext)
        {
              // migrate ids to udis
            if (!string.IsNullOrEmpty(ConfigurationManager.AppSettings["Sniper.Umbraco.EnableMultiNodeTreePickerIdToUdiMigrator"]) && bool.TryParse(ConfigurationManager.AppSettings["Sniper.Umbraco.EnableMultiNodeTreePickerIdToUdiMigrator"], out bool enabled) && enabled)
            {
                MultiNodeTreePickerIdToUdiMigrator.MigrateIdsToUdis(applicationContext);
            }
        }
    }
}