How to Execute CAML Query in Large SharePoint Lists using CAML To avoid the Exception – The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator – SharePoint Office 365

There are some scenarios that our List exceeds the threshold. Actually, the threshold of 5000 items for Lists and 20000 items for OneDrive is very less now a day. Almost all the transaction lists are growing in Lakhs.

Let us see, as a developer, how are we going to handle this. CSOM Gives a property called ListItemCollectionPosition by which, we can overcome our problem.

Whenever we are executing a CAML Query, there is a property called ListItemCollectionPosition. That we can compare it to the last retrieved value position. Say for example, if there are total 200 items in the list and we are setting a row limit of 100 on our Query, then the ListItemCollectionPosition will become 100 and we can compare that with the null, for our next execution. i.e., we are going to implement a while loop, until the whole list items were retrieved and stored in a separate object.

In my test scenario, I have created a list and added a 1,00,000 items on it.

Now, I am going to iterate through the items and get all the 1 Lakhs items in to a Generic List.

Before doing that, let us see, what happens, when I execute the normal way of querying.

We will end up with the below exception.

Microsoft.SharePoint.Client.ServerException: ‘The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.’

Now, let us write the logic to retrieve the information.

 using Microsoft.SharePoint.Client;
 using Microsoft.SharePoint.Client.RecordsRepository;
 using Microsoft.WindowsAzure.Storage;
 using Microsoft.WindowsAzure.Storage.Blob;
 using System;
 using System.Collections.Generic;
 using System.Linq;
 using System.Threading.Tasks;
 
 namespace Office365.Console
 {
     class Program
     {
         static void Main(string[] args)
         {
             QueryLargeList();
         }
 
         public static void QueryLargeList()
         {
             string siteUrl = "https://*******.sharepoint.com/sites/developerSite/";
             string userName = "sathish@**********.onmicrosoft.com";
             string password = "**************";
 
             OfficeDevPnP.Core.AuthenticationManager authMgr = new OfficeDevPnP.Core.AuthenticationManager();
 
             using (var ctx = authMgr.GetSharePointOnlineAuthenticatedContextTenant(siteUrl, userName, password))
             {
                 Web web = ctx.Web;
                 ctx.Load(web);
                 ctx.Load(web.Lists);
                 ctx.ExecuteQueryRetry();
 
                 List myList = web.Lists.GetByTitle("MyList");
                 ctx.Load(myList);
 
                 ctx.ExecuteQueryRetry();
 
 
                 List<string> strTempList = new List<string>();
 
                 ListItemCollectionPosition position = null;
                 var page = 1;
 
                 do
                 {
                     CamlQuery camlQuery = new CamlQuery();
                     camlQuery.ViewXml = @"<View Scope='Recursive'>
                                      <Query>
                                      </Query><RowLimit>5000</RowLimit>
                                  </View>";
                     camlQuery.ListItemCollectionPosition = position;
 
                     var listItems = myList.GetItems(camlQuery);
                     ctx.Load(listItems);
                     ctx.ExecuteQueryRetry();
 
                     position = listItems.ListItemCollectionPosition;
                     foreach (var listItem in listItems)
                     {
                         strTempList.Add(Convert.ToString(listItem["Title"]));
                     }
 
                     page++;
                 }
                 while (position != null);
 
                 
 
             }
         }
     }
 }
 

Happy Coding,

Sathish Nadarajan.