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.