CAML Query - Going around the 5000 List Item Threshold

Question

I have developed a SharePoint system for a client and I am planning forward with regards to CSOM code that runs on a specific library. This will eventually expand to a large set of data. Experience taught me that CAML queries do not like querying more than 5000 items at a go (because of the 5000 thresholds) and I am trying to create means of avoiding disasters now that I am still within chance :)

Most of the CAML queries within the system run on document sets. These will almost surely never cross the 5000 mark, considering that I will also be implementing a retention policy to get rid of and archive old document sets. The document sets are always at the Root of the document library. I am seeking ways of running a CAML query on just the root items (disregarding subfolders and documents within).

<View Scope = 'RecursiveAll'>

The above runs the CAML query on all items inside the document library, including all document sets and documents within.

I noticed that doing the query without the view, returns all the root folders (document sets in my case) within the library, but the 'Where' clause is not considered, i.e. all the root folders are returned irrespective of the Where condition.

My first question, is there some way to specify that the CAML query should only be executed on the root document sets? I.e. if there are 1000 document sets and 10,000 documents, the CAML query won't crash because of the 5000 thresholds, as it will only query the 1000 document sets.

The second question is there some way to only query a subset of the 10,000 documents. There are some cases within the system, that the CSOM code needs to query documents rather than root document sets. The scenario I am thinking would be a View (instead of the RecursiveAll), that first filters all the list items, say by Date or by a specific checkbox within the content type and get only the list items of type Document, and then the CAML query would be executed on the filtered view (subset of documents) rather on the whole RecursiveAll view.

Answer 1

You cannot fetch the List items more than threshold limit. Ex: you have 10,000 items, there are 3000 item which meets your caml expression. you may think this is less than threshold limit, so it will retrieve. but here it won't retrieve rather you will get exceed threshold limit exception.

1. What you can do is. You fetch all the items/documents 10,000 and keep it in collection object and query this collection object using Linq expression. so that your job will be easy and it will filter data perfectly. see below sample code

              var web = clientContext.Web;
               var list = web.Lists.GetByTitle("listName");
               clientContext.Load(list);
               clientContext.ExecuteQuery();
   
               CamlQuery camlQuery = new CamlQuery();
               camlQuery.ViewXml = "<View Scope='RecursiveAll'><RowLimit>5000</RowLimit></View>";
   
               List<ListItem> items = new List<ListItem>();
               do
               {
                   ListItemCollection listItemCollection = list.GetItems(camlQuery);
                   clientContext.Load(listItemCollection);
                   clientContext.ExecuteQuery();
   
                   //Adding the current set of ListItems in our single buffer
                   items.AddRange(listItemCollection);
                   //Reset the current pagination info
                   camlQuery.ListItemCollectionPosition = listItemCollection.ListItemCollectionPosition;
   
               } while (camlQuery.ListItemCollectionPosition != null);
   
   
   
                  var filteritems = items.Where(tt =>tt.FieldValues[""]=="") 
   

2. You can also try by keeping indexed columns with filter views.

   see this article http://sharepointmaven.com/how-to-overcome-sharepoint-5000-item-limit-threshold/