I have this application, in CSOM, which used to get the entire ListItems of a SharePoint List at one go to perform certain operations. All was well and good, until, I exported a brand new SharePoint List from Excel. The List had around 5400 items (>5000). When I tried to fetch all of its items, in the same way, I received the following error:

The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator

SharePoint has this threshold set in place where we can not query more than 5000 items in a single request. While this limit can be altered in its on-prem edition, in SharePoint Online, there’s no way that we can modify this value! The official reason given for this is:

To minimize database contention SQL Server, the back-end database for SharePoint, often uses row-level locking as a strategy to ensure accurate updates without adversely impacting other users who are accessing other rows. However, if a read or write database operation, such as a query, causes more than 5,000 rows to be locked at once, then it’s more efficient for SQL Server to temporarily lock the entire table until the database operation is completed.

NOTE: The actual number is not always 5,000, and can vary depending on your site, the amount of activity in the database, and your site’s configuration.

When the whole table is locked, it prevents other users from accessing the table. If this happens too often, then users will experience a degradation of system performance. Therefore, thresholds and limits are essential to help minimize the impact of resource-intensive database operations and balance the needs of all users.

The workaround for this, through code, is to query ListItems in batches. In our CAML query, we have this provision to define the

• RowLimit: no of items returned in a single request, and
• ListItemCollectionPosition: the page number.

Following is the code snippet:

using (ClientContext ctx = new ClientContext("siteUrl"))
{
   ctx.Credentials = new SharePointOnlineCredentials("piyush@something.onmicrosoft.com", GetSecureStringPassword());
   List list = ctx.Web.Lists.GetByTitle("ListName");

   CamlQuery camlQuery = new CamlQuery();
   camlQuery.ViewXml = "<View Scope=\"RecursiveAll\"><RowLimit>30</RowLimit></View>";

   int intIndex = 1;
   
   //Creating a single buffer for storing all the ListItems
   List<ListItem> lstListItemCollection = new List<ListItem>();

   do
   {
	   ListItemCollection listItemCollection = list.GetItems(camlQuery);
	   ctx.Load(listItemCollection);
	   ctx.ExecuteQuery();

	   //Adding the current set of ListItems in our single buffer
	   lstListItemCollection.AddRange(listItemCollection);
	   
	   //Reset the current pagination info
	   camlQuery.ListItemCollectionPosition = listItemCollection.ListItemCollectionPosition;

	   Console.WriteLine("Page:: " + intIndex++);
	   Console.WriteLine("ListItemCollectionPosition:: " + camlQuery.ListItemCollectionPosition);

   } while (camlQuery.ListItemCollectionPosition != null);
}

Key Takeaways

• Goes without saying that for the RowLimit property, the value has to be less than 5000.
• In my example here, I have set the RowLimit to 30. In my actual application, it is set to 1000. So I am not prescribing any limit here. Just try and see which limit suits best for you.
• Microsoft has said that they are actively working on to lift this limitation, but they don’t have as such any timeline for it.
• Once again, the limitation is only that we cannot view more than 5000 items. SharePoint can actually store a lot more items than that. The workaround, for now, is to leverage the functionalities like, filtering, grouping, & aggregation to reduce the number of items to less than 5000 for any given SharePoint View.
• Here’s my blog post for applying Grouping through CSOM, SharePoint ListItems Group By.
• And for applying aggregation only, without grouping, you can refer, Get Aggregate Value of a SharePoint Field using CSOM [Totals function].