Following Prasanna idea, I've written a windows console application for migrating list and preserve original IDs.
General algorithm is as follows:
- Get maximum ID from source list.
- Fill target list with dummy data (target list must be empty and unused prior this transfer)
- Update necessary columns from source to target
- 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
