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

Pablo Glomby 186 Reputation points
2023-05-08T17:29:24.78+00:00
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
OneDrive
OneDrive
A Microsoft file hosting and synchronization service.
914 questions
SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
10,073 questions
SharePoint Development
SharePoint Development
SharePoint: A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.Development: The process of researching, productizing, and refining new or existing technologies.
2,767 questions
0 comments No comments
{count} votes

Accepted answer
  1. ChengFeng - MSFT 5,015 Reputation points Microsoft Vendor
    2023-05-09T06:58:18.49+00:00

    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

    User's image

    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:User's image

    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


0 additional answers

Sort by: Most helpful