Sharepoint Online Csom: Getting around 5000 view limit in document library in a paginated response

Question

Let me preface this with, I begged them to let me re-organize the files a lot of different ways but they like looking at all files in one place.

I am working on cleaning out a large Document Library for my company. The Library in question has near 1 million items in total. At just the Root View level we have over 6000 Client Folders and that is slated to grow. I am attempting to ingest these Client Folders into my CSOM Console Application to simply see if the folders contain any files and if they don't we delete them. Simple enough right? It works fine on a small test library of 20 clients. The issue is I cannot even view our actual production library. When pointing the following code at the production library I just get the dreaded "Exceeds 5000 limit" I have learned that there is no way to get around this on the admin side like you could on sharepoint server. I am attempting to retrieve 1 client folder at a time using a Caml Query but even that causes issues. Is what I am doing even possible or no? Code below:

 using (ClientContext clientContext = new ClientContext(webUri))
            {

                int rowLimit = 1;
                var camlQuery = new CamlQuery();
                camlQuery.ViewXml = @"<View><Query></Query><RowLimit Paged='TRUE'>" + rowLimit + "</RowLimit></View>";


                clientContext.Credentials = new SharePointOnlineCredentials(userName, password);
                ListItemCollectionPosition position = null;

                // This value is NOT List internal name

                List targetList = clientContext.Web.Lists.GetByTitle("Clients");
                   // FolderCollection oFolderCollection = targetList.RootFolder.Folders;
                   // clientContext.Load(oFolderCollection);
                    //clientContext.ExecuteQuery();
                do
                {
                    ListItemCollection listItems = null;
                    camlQuery.ListItemCollectionPosition = position;
                    listItems = targetList.GetItems(camlQuery);
                    clientContext.Load(listItems);
                    clientContext.ExecuteQuery();
                    position = listItems.ListItemCollectionPosition;
                    items.AddRange(listItems.ToList());

                    foreach (ListItem item in listItems)
                    {
                        clientContext.Load(item);
                        clientContext.Load(item.Folder);
                        clientContext.ExecuteQuery();
                        if(item.Folder.GetType() == typeof(Folder))
                        {
                            Console.WriteLine(item.Folder.GetType());
                            Console.WriteLine(item.Folder.Name);
                            Console.ReadKey();
                        }

                    }

                }
                while (position != null);
}

When pointed to our production library I hit the threshold error on the clientContext.Load(listItems);

Answer 1

Try to correct your caml, here is my test code.

List targetList = clientContext.Web.Lists.GetByTitle("LargeList");
                int rowLimit = 1000;
                var camlQuery = new CamlQuery();
                camlQuery.ViewXml = @"<View Scope='RecursiveAll'><RowLimit Paged='TRUE'>" + rowLimit + "</RowLimit></View>";

                List<ListItem> items = new List<ListItem>();
                ListItemCollectionPosition position = null;                
                do
                {
                    ListItemCollection listItems = null;
                    camlQuery.ListItemCollectionPosition = position;
                    listItems = targetList.GetItems(camlQuery);
                    clientContext.Load(listItems);
                    clientContext.ExecuteQuery();
                    position = listItems.ListItemCollectionPosition;
                    items.AddRange(listItems.ToList());

                    //foreach (ListItem item in listItems)
                    //{
                    //    clientContext.Load(item);
                    //    clientContext.Load(item.Folder);
                    //    clientContext.ExecuteQuery();
                    //    if (item.Folder.GetType() == typeof(Folder))
                    //    {
                    //        Console.WriteLine(item.Folder.GetType());
                    //        Console.WriteLine(item.Folder.Name);
                    //        Console.ReadKey();
                    //    }

                    //}

                }
                while (position != null);

Answer 2

Please use SharePoint Search API to build the Folder hierarchy of your Library. You might have to query it in a loop to get through all your records, results are paged with 500 items. I believe you can use it find out empty folders as well, if not, then just use the direct folder path (like below) to query the folder for your purpose.

https://{site_url}/_api/web/GetFolderByServerRelativeUrl('/Shared Documents')