1

I'm using .NET CSOM to copy multiple list content from one site to another - essentially I'm migrating it. Because of the different list schemas I can't use built-in facilities like PowerShell Import/Export, Excel importing or list templates with data. Tried ShareGate without success.

My plan is to migrate every object on the list, one by one, field by field. But I'm having a problem with Lookup fields, as they are based on ID of the other list (like foreign key reference in SQL). When I migrate the data on first level list - let's call it Employees, no lookup fields yet - everything is fine , except the IDs has changed. Then, when I migrate second-level list - eg. Tasks - that references the Employees in lookup, the lookup field is pointing to a wrong entity (because employees ID has changed).

What is the correct way of migrating the whole structure using CSOM? I have lookup references up to five levels deep.

I've heard that each object has it's own GUID attached - can I use it to somehow find what is the new ID of the referenced row and update lookup field accordingly?

I've got a backup plan, but it's painful - copy read only source ID into new editable column on target site (eg. OldId), and the use it to figure out what is the new ID of migrated entity. What do you think?

Thanks!

2

Just an idea:

If the item is deleted, its ID is not reused in the list. So, list data could looks like below as an example.

Index = 0 ID = 1
Index = 1 ID = 4
Index = 2 ID = 5
Index = 3 ID = 6
Index = 4 ID = 7

So, in your CSOM code select the items order by the list item ID and find out the deleted item IDs and process data by insert dummy data for these rows in the memory. make sure these dummy data rows are easily identifiable.

Then insert data to the new list and delete dummy list items. So, items will have the same list item id as it was in the previous list and lookup columns should refer the same item.

You have to do this only for lookup lists.

2
  • Interesting idea. I'll try that and post back my results here. Thanks!
    – komsky
    Sep 29 '16 at 8:13
  • It worked! I've created a solution with this idea and I've managed to copy everything preserving original IDs, Lookups etc. I'll post my answer with this code below.
    – komsky
    Sep 30 '16 at 12:25
1

Following Prasanna idea, I've written a windows console application for migrating list and preserve original IDs.

General algorithm is as follows:

  1. Get maximum ID from source list.
  2. Fill target list with dummy data (target list must be empty and unused prior this transfer)
  3. Update necessary columns from source to target
  4. Delete remaining dummy data from target list

Code is quite long, and I plan to put it into a GitHub repo, but for now I'll just past it below:

using Microsoft.SharePoint.Client;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;

namespace SharePointListCopier
{
public class SharePointListMigration
{
    readonly static Uri SourcePortalURI = new Uri(ConfigurationManager.AppSettings["your-app.config-source-url"]);
    readonly static Uri TargetPortalURI = new Uri(ConfigurationManager.AppSettings["your-app.config-target-url"]);

    ClientContext _sourceContext;
    ClientContext _targetContext;

    /// <summary>
    /// Basic initialization of objects
    /// </summary>
    public SharePointListMigration()
    {
        _sourceContext = new ClientContext(SourcePortalURI);
        _targetContext = new ClientContext(TargetPortalURI);
    }

    public void GenericMigration(SharePointListNames listName, List<string> fieldsToMigrate)
    {
        GenericMigration(listName, fieldsToMigrate, null);
    }
    public void GenericMigration(SharePointListNames listName, List<string> fieldsToMigrate, List<string> multiLookupFields)
    {
        //STEP 1: Get lists max id
        int maxListId = GetListMaxId(listName, _sourceContext);

        //STEP 2: Fill target list with fake dummy data
        FillListWithFakeDummyData(listName, maxListId, _targetContext);

        //STEP 3: Update target list with source data
        GenericMigrateData(listName, fieldsToMigrate, multiLookupFields);

        //STEP 4: Remove remaining dummy data
        RemoveDummyData(listName, _targetContext);
    }
    /// <summary>
    /// Step 01 - get maximum ID of source list
    /// </summary>
    /// <returns>Maximum ID used</returns>
    private int GetListMaxId(SharePointListNames listTitle, ClientContext context)
    {
        Console.WriteLine("Getting MAX ID for {0}", listTitle);
        var list = context.Web.Lists.GetByTitle(listTitle.ToString());
        list.LoadAndExecute(context);
        var caml = "<View><Query><Where>"
                 + "<Eq><FieldRef Name='FSObjType' /><Value Type='int'>0</Value></Eq>"
                 + "</Where>"
                 + "<OrderBy><FieldRef Name='ID' Ascending='False' /></OrderBy>"
                 + "</Query>"
                 + "<ViewFields><FieldRef Name='ID' /></ViewFields>"
                 + "<RowLimit>1</RowLimit>"
                 + "</View>";

        var query = new CamlQuery();
        query.ViewXml = caml;
        var items = list.GetItems(query);
        items.LoadAndExecute(context);

        var enumerator = items.GetEnumerator();
        enumerator.MoveNext();
        Console.WriteLine("Max ID for {0} list is {1}", listTitle, enumerator.Current.Id);
        return enumerator.Current.Id;
    }

    /// <summary>
    /// Fill list with fake data to have every ID present
    /// Warning: The list must be UNUSED and EMPTY before filling it, as ID are NOT reusable
    /// that is, if you have added and deleted entity, this ID can't be used any more!
    /// Do a snapshot save before testing, as list item id cannot be reused
    /// </summary>
    private void FillListWithFakeDummyData(SharePointListNames targetList, int maxListId, ClientContext context)
    {
        Console.WriteLine("Filling {0} list with dummy data on {1}", targetList, context.Url);
        string listTitle = targetList.ToString();
        var list = context.Web.Lists.GetByTitle(listTitle.ToString());
        list.LoadAndExecute(context);

        for (int i = 0; i <= maxListId; i++)
        {
            ListItemCreationInformation entityCreationInfo = new ListItemCreationInformation();
            ListItem newFakeDummyListItem = list.AddItem(entityCreationInfo);
            newFakeDummyListItem["Title"] = "FakeDummyListItem";
            newFakeDummyListItem.Update();
            context.ExecuteQuery();
            Console.WriteLine("ID {0} inserted", i);
        }
    }

    private void GenericMigrateData(SharePointListNames listNameToMigrate, List<string> fieldsToMigrate, List<string> multiLookupFields)
    {
        Console.WriteLine("Initializing data migration for {0} ", listNameToMigrate);

        //Initialization
        var locationsSourceList = _sourceContext.Web.Lists.GetByTitle(listNameToMigrate.ToString());
        locationsSourceList.LoadAndExecute(_sourceContext);
        //load list fields
        locationsSourceList.Fields.LoadAndExecute(_sourceContext);

        //init target list
        var locationsTargetList = _targetContext.Web.Lists.GetByTitle(listNameToMigrate.ToString());
        locationsTargetList.LoadAndExecute(_targetContext);
        //Execution
        CamlQuery sourceQuery = CamlQuery.CreateAllItemsQuery();
        ListItemCollection sourceItems = locationsSourceList.GetItems(sourceQuery);
        sourceItems.LoadAndExecute(_sourceContext);

        foreach (var item in sourceItems)
        {
            try
            {
                ListItem targetItem = locationsTargetList.GetItemById(item.Id);
                targetItem.LoadAndExecute(_targetContext);
                CopyListItem(targetItem, item, fieldsToMigrate);
                if (multiLookupFields != null && multiLookupFields.Any())
                {
                    foreach (var multiLookupField in multiLookupFields)
                    {
                        MigrateMultiLookupField(targetItem, item, multiLookupField);
                        targetItem.Update();
                        _targetContext.ExecuteQuery();
                        Console.WriteLine("MultiLookup {0} field updated for: {1}", multiLookupField, item["Title"]);

                    }
                }
                targetItem.Update();
                _targetContext.ExecuteQuery();
                Console.WriteLine("{0} entity updated for: {1}", listNameToMigrate, item["Title"]);

            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: {0}\t ID: {1}", ex.Message, item.Id);
                Console.WriteLine("Skipping entity");
            }
        }
    }

    private void RemoveDummyData(SharePointListNames targetListTitle, ClientContext context)
    {
        Console.WriteLine("Removing dummy data leftovers");
        var caml = "<View><Query><Where>"
                + "<Eq><FieldRef Name='Title' /><Value Type='text'>FakeDummyListItem</Value></Eq>"
                + "</Where>"
                + "</Query>"
                + "<ViewFields><FieldRef Name='ID' /><FieldRef Name='Title' /></ViewFields>"
                + "</View>";
        CamlQuery dummyDataQuery = CamlQuery.CreateAllItemsQuery();
        dummyDataQuery.ViewXml = caml;

        var targetList = context.Web.Lists.GetByTitle(targetListTitle.ToString());
        targetList.LoadAndExecute(context);

        var items = targetList.GetItems(dummyDataQuery);
        items.LoadAndExecute(context);

        //Workaround for manipulating list content while inside foreach
        List<int> idForDelete = new List<int>();
        foreach (var item in items)
        {
            idForDelete.Add(item.Id);

        }
        foreach (var id in idForDelete)
        {
            var dummyItem = targetList.GetItemById(id);
            dummyItem.LoadAndExecute(context);
            Console.WriteLine("Deleting ID: {0}, Title: {1}", dummyItem.Id, dummyItem["Title"]);
            dummyItem.DeleteObject();
            context.ExecuteQuery();
        }
    }


    #region Helper methods
    /// <summary>
    /// Copy entity fields
    /// This method can copy Lookup field excluding multilookup type
    /// For multilookup use MigrateMultiLookupField
    /// </summary>
    private void CopyListItem(ListItem destination, ListItem source, List<string> fields)
    {
        foreach (var field in fields)
        {
            try
            {
                destination[field] = source[field];
            }
            catch
            {
                Console.WriteLine("Error copying {0} property. Trying again with lookup initialization", field);
                try
                {
                    _sourceContext.Load<ListItem>(source, src => src[field]);
                    _sourceContext.ExecuteQuery();
                    var lf = (FieldLookupValue)source[field];
                    var newLookupField = new FieldLookupValue();
                    newLookupField.LookupId = lf.LookupId;
                    destination[field] = newLookupField;
                }
                catch (Exception inEx)
                {
                    Console.WriteLine("Initialization failed. Field was not copied!");
                    Console.WriteLine(inEx.Message);
                }
            }
        }
    }

    /// <summary>
    /// Strictly for copying multilookup column values
    /// </summary>
    private void MigrateMultiLookupField(ListItem targetItem, ListItem sourceItem, string lookupFieldName)
    {
        try
        {
            _sourceContext.Load<ListItem>(sourceItem, src => src[lookupFieldName]);
            _sourceContext.ExecuteQuery();
            sourceItem.LoadAndExecute(_sourceContext);

            var lookupFieldArray = (FieldLookupValue[])sourceItem[lookupFieldName];
            FieldLookupValue[] targetlookupFieldArray = new FieldLookupValue[lookupFieldArray.Length];
            List<int> lookups = new List<int>();
            for (int i = 0; i < lookupFieldArray.Length; i++)
            {
                targetlookupFieldArray[i] = new FieldLookupValue();
                targetlookupFieldArray[i].LookupId = lookupFieldArray[i].LookupId;
                lookups.Add(lookupFieldArray[i].LookupId);
            }
            targetItem[lookupFieldName] = targetlookupFieldArray;
        }
        catch (InvalidCastException castEx)
        {
            Console.WriteLine("Casting exception! Make sure you have choosen correct field name! Stopping program ...");
            Console.WriteLine(castEx.Message);
            throw;
        }
        catch (Exception inEx)
        {
            Console.WriteLine("Initialization failed. Field was not copied!");
            Console.WriteLine(inEx.Message);
        }
    } 
    #endregion

}

}

Some explanations for people starting his/her adventure with SharePoint.

  • You'll need SharePoint managed client DLLs for this to work. Install it from nuget:

    install-package Microsoft.SharePoint.Client

  • Add reference to System.Configuration DLL

  • Provide app.config settings for your source and target URLs
  • I've used an enum as a list names, that you'll have to create by yourself
1
  • Excellent! Glad to hear that. Yes please go ahead and share it in Github Cheers !
    – Prasanna
    Sep 30 '16 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.