CAML Query Fails on Indexed Item

Question

Hello all I have a C# Program using CSOM. I have a folder with over 5000 files in it and I have an Indexed field ("Modified"). I need to re-order the resulting documents in Modified Order (Both Descending and Ascending) . I am limiting the date from the beginning of the year to the current date. The folder contains two files created after 1/1/2023. The code I have written works perfectly except on this folder. Most of the folders have less than 1000 files in them. For the life of me I cannot figure this out. below is the CAML query. I would assume that the where clause would return the two records on the indexed field that exist and then they would be ordered. Again, this works on every other folder except the one with 5000+ files. The confusing part is, I can sort by FileLeafRef, for all files, in both ascending and descending order to my hearts content without the "Threshold" error. Thanks for any assistance and insight into this.

<View>
     <Query>
          <Where>
	            <Gt>
		             <FieldRef Name=""Modified""/>
                     <Value IncludeTimeValue=""TRUE"" Type=""DateTime"">2023-01-01T12:00:00Z</Value>                                       
              	</Gt>
          </Where>
          <OrderBy Override=""TRUE""  UseIndexForOrderBy=""TRUE"">
               <FieldRef Name=""Modified"" Ascending=""FALSE""/>
          </OrderyBy>
     </Query>
     <RowLimit Paged=""TRUE"">2000</RowLimit>
</View>

Answer 1

Hi @Thomas Tierney ,

We are unable to fetch the List items more than threshold limit 5000. For example 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. You can only retrieve all the items by the following caml query. Then keep it in collection object and query this collection object using Linq expression.

"<View Scope='RecursiveAll'><RowLimit>5000</RowLimit></View>"

You will need to query the list out and store the items in listCol, then you can filter the items created before 2023.1.1 and sort them by c#

Here is a similar issue with yours for reference

https://sharepoint.stackexchange.com/questions/277891/querying-document-library-which-contain-more-than-5-000-documents-using-folder

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.