SharePoint REST API ID Manipulation BUG

I was working for a customer’s SharePoint Migration Project of lists and libraries from 2007 (wss 3.0) to 2016 using Content Migration Tools. The customer declined the idea of migrating the entire Content DB and this forced us to use tools to get the job done. 

But, the requirement was a type no other Migration Tool could do.The customer told us to move lists from 2007 to 2016 without changing the ID of the items. We tried all the migration tools but most of them didn't support “maintaining the ID of the items” which they re-index the IDs randomly.

Our two options to get the job done were: Convince the customer for ContentDB Migration or Develop Custom Migration Tool. Because of the customer declining the first option, we had to write our custom migration tool to carry list/libraries items while maintaining the IDs. 

CUSTOM MIGRATION TOOL

I’ve developed this tool as a Console application in C#.The tool was supposed to run in the SP 2007 environment, access its contents using server side methods, and transport the items to SP 2016 environment using REST API.
I’ve finished developing the tool, did a few testings. The results were satisfying, or we can say successful by carrying 90,000 items of the list in 18 hours (depending on the old version of Windows Server which uses 32-bit).

Before that, we did test the migration tools for the big lists. Some couldn't even open the list, some loaded the list but advised to carry the list in 90 days (1000 items per day), and some crashed along the way; which resulted in failures.

By using our custom migration tool, we’ve successfully transported the items from SP 2007 to 2016 without any error. Then, we used the tool on big lists. The lists were carried with all the contents. According to the plan, this tool was going to get the job done by maintaining the IDs whereas most tools couldn't manage to do so.

Sample code for payload

RestHelper.RestPost("{'__metadata': {'type': 'SP.Data.TestUsersListItem'},
                                           'Title': '" + item.Title + "',
                                           'ID':'" + item.ID + "',
                                           'Role':'" + item["Role"] + "'
                                          }");

THE PROBLEM UNLEASHES

Now, let's see the real world rather than pink dreams... After we carried some list items, if users manually posted items using Lists/Libraries, SharePoint rejected the input and threw exceptions.

Well that made me somewhat sad and angry.

Sample List

ID	Title	Rol
1	Ibrahim	Developer
3	Jane	Tester
5	Ali	Manager

The list above is displaying the items after we carried the list by manipulating the IDs.

What If I wanted to add a new item to this list, what will it assign as ID to the new item? You think it will be “2”, right?

Wrong answer!

Because the first manual input will try to set ID as “1” ignoring that there is already an item in the list resulting in a warning message starting with “0x0...”.

So, after experiencing this warning, we immediately realized there is a connectivity issue between SharePoint’s ID Assign Mechanism and REST Post ID.

When we post an ID using REST, SharePoint gets mad and says, “To assign ID is my job, now you’ve confused me”.This looks normal though. Assigning IDs is SharePoint’s job to deal with but there is no rule that says we cannot POST ID in our payload and thus, there is no preventing the input resulting in accepting the ID. According to my research, this was a bug for REST in SharePoint. So, we let the people know from local Microsoft Office.

Don't know for sure if it will be fixed in the future or not.

ID Manipulation is a dangerous process. When you get careless, it may result in disastrous functionality in lists and libraries. If possible, try not to assign ID in the real world. Leave it to SharePoint’s ID Assign Mechanism.

“What's done is done. How will I solve this? Is there no solution? If this is a bug, why it is not fixed yet?”

I feel like I hear you asking these questions.

Maybe they simply ignored. No crazy man on earth could post IDs and manipulate it. If you have read Microsoft’s official MSDN documentation for REST Endpoints, none of them use posting IDs.

How will I solve this? 

REST API ID Manipulation BugFix

The only solution we’ve thought of is to write an EventReceiver. The event you need to catch with event receiver is  “ItemAdding” which is before SharePoint assigns ID for specific list/library item.

What we will be doing in this event receiver is simply that before the item ID is assigned, we will access SQL Content DB and update the AllListsAux table’s NextAvailableId column, and set it as +1 to the last ID from the list we want to add the item to.

While doing this, we need to specify ListID, the AllListsAux depends on it.

Before getting ahead and writing some code, you need to know some tips about this.

When you open Elements.xml for editing, look at the Receivers tag.

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <Receivers ListTemplateId="100">
      <Receiver>
        <Name>TestEventReceiverItemAdding</Name>
        <Type>ItemAdding</Type>
        <Assembly>$SharePoint.Project.AssemblyFullName$</Assembly>
        <Class>TestProject.TestEventReceiver.TestEventReceiver</Class>
        <SequenceNumber>10000</SequenceNumber>
      </Receiver>
  </Receivers>
</Elements>

Here, you can set it as another number for a specific list or simply remove it so that it can work on all lists/libraries. The feature is activated on the site. 

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <Receivers>
      <Receiver>
        <Name>TestEventReceiverItemAdding</Name>
        <Type>ItemAdding</Type>
        <Assembly>$SharePoint.Project.AssemblyFullName$</Assembly>
        <Class>TestProject.TestEventReceiver.TestEventReceiver</Class>
        <SequenceNumber>10000</SequenceNumber>
      </Receiver>
  </Receivers>
</Elements>

EVENT RECEIVER DEVELOPMENT

As you know, when you select an event for event receiver, it immediately creates it in code.

public override void ItemAdding(SPItemEventProperties properties)
{
            base.ItemAdding(properties);
}

First of all, we need to get List Information. So, let's update the code in ItemAdding event as seen below.

public override void ItemAdding(SPItemEventProperties properties)
{
            base.ItemAdding(properties);
            SPList list = properties.List;
}

In event receivers, you can access list, item, and web specific properties using SpxxxEventProperties.

Now, let's create a new method that takes the list as a parameter and the list ID to define it in ItemAdding.

public void UpdateNextAvailable(SPList list, Guid listId)
{
}

Let's write the code required to connect to the Site Collection and write the CAML code to get the last items ID in the list.

public void UpdateNextAvailable(SPList list, Guid listId)
{
            SPSite site = new SPSite("SiteURL");
            SPQuery query = new SPQuery();
            query.RowLimit = 1;
            query.Query = "<OrderBy><FieldRef Name='ID' Ascending='FALSE' /></OrderBy>";
            SPListItem item = list.GetItems(query).Cast<SPListItem>().FirstOrDefault();
}

This code will result in us getting the last ID of the items in a list. Then creating an integer variable named “newid” which increases the last item in the list by 1.

public void UpdateNextAvailable(SPList list, Guid listId)
{
            SPSite site = new SPSite("SiteURL");
            SPQuery query = new SPQuery();
            query.RowLimit = 1;
            query.Query = "<OrderBy><FieldRef Name='ID' Ascending='FALSE' /></OrderBy>";
            SPListItem item = list.GetItems(query).Cast<SPListItem>().FirstOrDefault();
      int newid = item.ID + 1;
}

Now, we need to check if there’s a ContentDB assigned for specific web application or not and then get the first index and write its connection string.

public void UpdateNextAvailable(SPList list, Guid listId)
{
   SPSite site = new SPSite("SiteURL");
   SPQuery query = new SPQuery();
   query.RowLimit = 1;
   query.Query = "<OrderBy><FieldRef Name='ID' Ascending='FALSE' /></OrderBy>";
   SPListItem item = list.GetItems(query).Cast<SPListItem>().FirstOrDefault();
  int newid = item.ID + 1;

   if (site.WebApplication.ContentDatabases.Count > 0)
   {
       var DBConnString = site.WebApplication.ContentDatabases[0].DatabaseConnectionString;
   }
}

After setting the connection string, we write code to connect it.

public void UpdateNextAvailable(SPList list, Guid listId)
{
            SPSite site = new SPSite("SiteURL");
            SPQuery query = new SPQuery();
            query.RowLimit = 1;
            query.Query = "<OrderBy><FieldRef Name='ID' Ascending='FALSE' /></OrderBy>";
            SPListItem item = list.GetItems(query).Cast<SPListItem>().FirstOrDefault();
            int newid = item.ID + 1;
           if (site.WebApplication.ContentDatabases.Count > 0)
           {
            var DBConnString =site.WebApplication.ContentDatabases[0].DatabaseConnectionString;
            SqlConnection con = new SqlConnection(DBConnString);
…
}

Finally, we write an UpdateCommand which connects to the WSS Content DB and inside this DB connects to a table named AllListsAux and then sets the NextAvailableId as newid which equals to the last ID in the list increased by 1. 

public void UpdateNextAvailable(SPList list, Guid listId)
{
            SPSite site = new SPSite("SiteURL");
            SPQuery query = new SPQuery();
            query.RowLimit = 1;
            query.Query = "<OrderBy><FieldRef Name='ID' Ascending='FALSE' /></OrderBy>";
            SPListItem item = list.GetItems(query).Cast<SPListItem>().FirstOrDefault();
            int newid = item.ID + 1;
            if (site.WebApplication.ContentDatabases.Count > 0)
            {
            var DBConnString =site.WebApplication.ContentDatabases[0].DatabaseConnectionString;
                SqlConnection con = new SqlConnection(DBConnString);
                try
                {
                    SPSecurity.RunWithElevatedPrivileges(delegate () {
                        con.Open();
                        SqlCommand com = con.CreateCommand();
                        com.CommandText = String.Format("UPDATE [WSS_Content].dbo.AllListsAux set NextAvailableId=" + newid + " where ListID = '{0}'", listId.ToString());
                        com.ExecuteNonQuery();
                    });

                }
                finally
                {
                    con.Close();
                }
            }
}

Don’t forget to set the ItemAdding as seen below by adding the UpdateNextAvailable method.

public override void ItemAdding(SPItemEventProperties properties)
{
            base.ItemAdding(properties);
            SPList list = properties.List;
            UpdateNextAvailable(properties.List, list.ID);
}

That’s It!

As explained before, we write code to update the ID which SharePoint needs to manage. By doing so, we simply “Simulated” SharePoint’s ID Assign Mechanism and fixed this little bug using our own method.

When you deploy this event receiver and activate its features, users will no longer receive the warning that denies them adding input to the list items thus resulting in getting right IDs for the list/library. 

ENTIRE CODE

TestEventReceiver.cs 

using System;
using Microsoft.SharePoint;
using System.Linq;
using System.Data.SqlClient;

namespace TestProject.TestEventReceiver
{
    public class TestEventReceiver : SPItemEventReceiver
    {
        public override void ItemAdding(SPItemEventProperties properties)
        {
            base.ItemAdding(properties);
            SPList list = properties.List;
            UpdateNextAvailable(properties.List, list.ID);
        }

        public void UpdateNextAvailable(SPList list, Guid listId)
        {
            SPSite site = new SPSite("SiteURL");
            SPQuery query = new SPQuery();
            query.RowLimit = 1;
            query.Query = "<OrderBy><FieldRef Name='ID' Ascending='FALSE' /></OrderBy>";
            SPListItem item = list.GetItems(query).Cast<SPListItem>().FirstOrDefault();
            int newid = item.ID + 1;
            if (site.WebApplication.ContentDatabases.Count > 0)
            {
                string DBConnString = site.WebApplication.ContentDatabases[0].DatabaseConnectionString;
                SqlConnection con = new SqlConnection(DBConnString);
                try
                {
                    SPSecurity.RunWithElevatedPrivileges(delegate () {
                        con.Open();
                        SqlCommand com = con.CreateCommand();
                        com.CommandText = String.Format("UPDATE [WSS_Content].dbo.AllListsAux set NextAvailableId=" + newid + " where ListID = '{0}'", listId.ToString());
                        com.ExecuteNonQuery();
                    });

                }
                finally
                {
                    con.Close();
                }
            }
        }
    }
}

Elements.xml

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <Receivers>
      <Receiver>
        <Name>TestEventReceiverItemAdding</Name>
        <Type>ItemAdding</Type>
        <Assembly>$SharePoint.Project.AssemblyFullName$</Assembly>
        <Class>TestProject.TestEventReceiver.TestEventReceiver</Class>
        <SequenceNumber>10000</SequenceNumber>
      </Receiver>
  </Receivers>
</Elements>

FINAL

ID Manipulation isn’t a suggested method.

As we fixed this little bug by updating the NextAvailableId, it doesn’t mean you should set ID on your code. This would be the fix but ID Manipulation isn’t a suggested method to get the job done.

If IDs on a list/library will not change under any circumstances and cannot be used in new environment, what I suggest is step by step Content DB Migration(SP2007>SP2010>SP2013>SP2016).

If there can be no migration or there can be just one and the customer declines migration yet you still you want to maintain IDs, then this article is written for you, my dear SharePoint Developer.

Use it with great care.