As far as I can tell, it seems like MS made an unannounced change related to this at some point in the last 2-4 weeks. You used to be able to prevent the threshold error by filtering based on indexed/built-in fields, (so long as the filtered results counted less than 5000) but that no longer seems to be the case.
That said, I have two suggestions that might work for you. The first is a generic solution that should work for both lists and libraries, while the second is specifically for libraries.
List/Library Solution
Since you already know about retrieving the list item in batches, this is more for other people looking for an answer to their threshold-related issue. The FilesOnly value in the Scope attribute might help your situation, though.
I ran into an issue a few weeks back where one of my applications started hitting the threshold error after running fine for 2 years. After trying all sorts of modifications to the query, I finally ended up with the following:
private static CamlQuery BuildFilesQuery(List spList, string queryFolder=null)
{
if(String.IsNullOrEmpty(queryFolder) && !spList.RootFolder.IsPropertyAvailable("ServerRelativeUrl")) {
spList.Context.Load(spList.RootFolder,
folder => folder.ServerRelativeUrl
);
spList.Context.ExecuteQuery();
queryFolder = spList.RootFolder.ServerRelativeUrl;
}
return new CamlQuery() {
ViewXml = @"
<View Scope='FilesOnly'>
<RowLimit>5000</RowLimit>
</View>",
FolderServerRelativeUrl = queryFolder
};
}
public static IList<ListItem> LoadRecordsQuery(List spList, string queryFolder=null, CamlQuery spQuery=null, ListItemCollectionPosition position=null)
{
spQuery = spQuery ?? BuildQuery(spList, queryFolder);
spQuery.ListItemCollectionPosition = position;
ListItemCollection spItems = spList.GetItems(spQuery);
spList.Context.Load(spItems, items => items.ListItemCollectionPosition);
spList.Context.ExecuteQuery();
List<ListItem> spItemsList = spItems.ToList();
if(spItems.ListItemCollectionPosition != null) {
spItemsList.AddRange(LoadRecordsQuery(spList, queryFolder, spQuery, spItems.ListItemCollectionPosition));
}
return spItemsList;
}
To use this, you'd call: LoadRecordsQuery(spList) This should give you all of the files (or list items) in the root folder of your list/library. If you want to query the files in a subfolder instead, pass the folder's server-relative url to the queryFolder parameter. Unfortunately you'll have to do any sorting/filtering on the client side, as the threshold behavior has become inconsistent when <Where> and <OrderBy> clauses are used. (at least in my experience)
If I understood correctly, you already have a working solution for querying folders. If not, let me know and I'll edit this section.
Library-Only Solution (WebDAV)
In your case, you're working on a document library, so you can use the WebDAV interface to list all files and folders in one request: (takes a little while, since it's such a big response)
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Net;
using System.Xml;
using Microsoft.SharePoint.Client.Utilities;
namespace Microsoft.SharePoint.Client
{
internal static class FolderExtensions
{
#region Constants
const int RequestTimeout = 60000;
const string DAVNS = @"DAV:";
const string UserAgent = @"Microsoft-WebDAV-MiniRedir/6.1.7601";
const string PropFindBody = @"<?xml version=""1.0"" encoding=""utf-8"" ?><propfind xmlns=""DAV:""><propname/></propfind>";
#endregion
#region DAV XML Parsing
internal static XmlElement GetDAVElement(this XmlDocument self, string localName)
{
foreach(XmlElement element in self.GetElementsByTagName(localName, DAVNS))
return element;
return null;
}
internal static XmlElement GetDAVElement(this XmlElement self, string localName)
{
foreach(XmlElement element in self.GetElementsByTagName(localName, DAVNS))
return element;
return null;
}
internal static XmlNodeList GetDAVElements(this XmlDocument self, string localName)
{
return self.GetElementsByTagName(localName, DAVNS);
}
internal static XmlNodeList GetDAVElements(this XmlElement self, string localName)
{
return self.GetElementsByTagName(localName, DAVNS);
}
internal static IEnumerable<XmlElement> GetDAVResponses(this XmlDocument self)
{
foreach(XmlElement davElement in self.GetDAVElements(@"response")) {
yield return davElement;
}
}
internal static string GetResponseHref(this XmlElement self)
{
// ReSharper disable once PossibleNullReferenceException
return HttpUtility.UrlKeyValueDecode(self.GetDAVElement(@"href").InnerText.TrimEnd('/'));
}
internal static string GetRelativeResponseHref(this XmlElement self, string folderUrl)
{
return self.GetResponseHref().Substring(folderUrl.Length);
}
internal static bool IsFolderResponse(this XmlElement self)
{
return self.GetDAVElement("isFolder") != null;
}
#endregion
#region Request Factories
public static HttpWebRequest CreateWebRequest(this ClientContext self, Uri requestUri, string requestMethod=WebRequestMethods.Http.Get)
{
// Grab a reference to the SPO credentials object.
SharePointOnlineCredentials spoCreds = self.Credentials as SharePointOnlineCredentials;
if(spoCreds == null) {
throw new InvalidOperationException("Request methods are only meant to be used with SharePoint Online.");
}
HttpWebRequest oRequest = (HttpWebRequest)WebRequest.Create(requestUri);
oRequest.Pipelined = true;
oRequest.UserAgent = UserAgent;
oRequest.Method = requestMethod;
oRequest.Timeout = RequestTimeout;
// Wasteful. This re-processes the authentication logic every time to call it in order
// to generate a new authentication cookie. This should definitely be cached somewhere in
// your real code.
oRequest.Headers[HttpRequestHeader.Cookie] = spoCreds.GetAuthenticationCookie(requestUri, true);
return oRequest;
}
public static HttpWebRequest CreateListRequest(this ClientContext clientContext, Uri folderUri, string requestBody = null)
{
char[] requestBodyChars = (requestBody ?? PropFindBody).ToCharArray();
HttpWebRequest oRequest = CreateWebRequest(clientContext, folderUri, "PROPFIND");
oRequest.Headers["Depth"] = "infinity,noroot";
oRequest.Headers["Translate"] = "f";
oRequest.ContentLength = requestBodyChars.LongLength;
using(Stream requestStream = oRequest.GetRequestStream()) {
using(StreamWriter writer = new StreamWriter(requestStream)) {
writer.Write(requestBodyChars);
writer.Close();
}
requestStream.Close();
}
return oRequest;
}
#endregion
#region List Request Processing
public static IEnumerable<String> ListRecursively(this Folder oFolder, bool filesOnly=true)
{
// Grab references to the ClientContext and SPO Credentials objects.
ClientContext clientContext = oFolder.Context as ClientContext;
Debug.Assert(clientContext != null);
SharePointOnlineCredentials spoCreds = clientContext.Credentials as SharePointOnlineCredentials;
if(spoCreds == null) {
throw new InvalidOperationException("ListFolder method is only meant to be used with SharePoint Online.");
}
// Load the ServerRelativeUrl property on our folder if necessary.
if(!oFolder.IsPropertyAvailable("ServerRelativeUrl")) {
clientContext.Load(oFolder, folder => folder.ServerRelativeUrl);
clientContext.ExecuteQuery();
}
// Build the folder's absolute url.
Uri folderUri = new Uri(
new Uri(clientContext.Url).GetLeftPart(UriPartial.Authority) +
oFolder.ServerRelativeUrl
);
// Read the request response as an xml document.
XmlDocument responseXml = new XmlDocument();
HttpWebRequest oRequest = CreateListRequest(clientContext, folderUri);
using(Stream responseStream = oRequest.GetResponse().GetResponseStream()) {
Debug.Assert(responseStream != null);
using(XmlReader reader = XmlReader.Create(responseStream)) {
responseXml.Load(reader);
}
}
// Process the response xml document
foreach(XmlElement element in responseXml.GetDAVResponses()) {
if(filesOnly && element.IsFolderResponse()) continue;
string href = element.GetResponseHref();
yield return href;
}
}
#endregion
}
}
The above code should be usable as:
// Files only
foreach(string fileUrl in spList.RootFolder.ListRecursively()) {
...
}
// Files & Folders
foreach(string pathUrl in spList.RootFolder.ListRecursively(false)) {
...
}
If necessary, you can get the Microsoft.SharePoint.Client.File & Microsoft.SharePoint.Client.Folder objects from the URLs. (context.Web.GetFileByServerRelativeUrl I think. If not, you'll have to look it up)
Note: Haven't actually tested this. Threw it together from the source code in a few of my previous projects, so sorry for any unused/unnecessary code that was left in.
