CSOM: Microsoft.SharePoint.Client.ServerException: The attempted operation is prohibited because it exceeds the list view threshold.

Question

Hello,
I have a problem with CSOM or the REST API.
I have a standard SharePoint site with a standard Shared Document library.
Inside this library I have a subfolder with more than 5000 files.

When I try to use a Caml Query to get the files or folders I get:
Microsoft.SharePoint.Client.ServerException: The attempted operation is prohibited because it exceeds the list view threshold. 
The code is something like:
   Microsoft.SharePoint.Client.List list = context.Web.GetListUsingPath(ResourcePath.FromDecodedUrl(szListURL));
   context.Load(list);
   context.ExecuteQuery();
...
...
   CamlQuery camlQuerySubfolder = new CamlQuery();
   camlQuerySubfolder.ListItemCollectionPosition = null;
   camlQuerySubfolder.FolderServerRelativePath = ResourcePath.FromDecodedUrl(szFolderRelativeURL); // This folder has more than 5000 files
   camlQuerySubfolder.ViewXml = "<View Scope='RecursiveAll'><Query><OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy></Query><RowLimit Paged='TRUE'>500</RowLimit></View>";
   ListItemCollection listItemsSubfolder = list.GetItems(camlQuerySubfolder);
   context.Load(listItemsSubfolder);
   context.ExecuteQuery(); //The exception is triggered

Is there any other way to get the files and the folders in a paged way (I know the above code must be "paged" but for the first page of 500 items it fails....

Thanks

Answer 1

Hi @Pablo Glomby,

Judging from the scenario and code you provided, the cause of the problem is:

1. SharePoint limits the size of the query result set by default.

To solve this question, please try using pagination.

I made a test for your reference:

Folders containing more than five thousand files

Here is code:

static void Main(string[] args)
        {
       
            string libraryName = "Documents";
            string folderUrl = "/sites/Fctest/Shared Documents/Fctest";


            using (ClientContext context = new ClientContext(SiteURL))
            {
               ....
             
                List list = context.Web.Lists.GetByTitle(libraryName);
                CamlQuery camlQuery = new CamlQuery();
                camlQuery.ViewXml = "<View Scope='RecursiveAll'><Query><OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy></Query><RowLimit Paged='TRUE'>500</RowLimit></View>";
                context.Load(context.Web);
                context.ExecuteQuery();

                camlQuery.FolderServerRelativeUrl = $"{context.Web.ServerRelativeUrl}{folderUrl}";
                ListItemCollectionPosition position = null;

                //The code then uses a loop to iterate through the items in the library.
                //Each iteration of the loop retrieves the next batch of items using the method with the object and the object, which indicates the position in the list or library where the previous batch of items ended.

                do
                {
                    camlQuery.ListItemCollectionPosition = position;
                    ListItemCollection items = list.GetItems(camlQuery);
                    context.Load(items);
                    context.ExecuteQuery();
                    position = items.ListItemCollectionPosition;
                    //The loop then iterates through the items in the current batch and performs any required operations, such as printing the file path.
                    foreach (var item in items)
                    {
                        Console.WriteLine(item["FileRef"]);
                    }

                } while (position != null);
            }
}

Here is test result:

Here is a link about how to paginate for your reference：

https://sharepoint.stackexchange.com/questions/128373/large-list-issue-with-csom

I hope this helps you solve your problem!

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.

Best Regards

Cheng Feng