3

I have a list that exceeds the 5000 threshold. total is 16013. Is it possible to query lists of this size. I can get all of contents with no query in batch using

query..ListItemCollectionPosition

But can I query just folders and then files, in the test I am running I have a folder that contains 16000 files and then a single folder. Can I query this list efficiently to find this folder. I've tried this:

query.ViewXml = @"<View><Query><Where><BeginsWith><FieldRef Name='ContentTypeId' /><Value Type='ContentTypeId'>0x0120</Value></BeginsWith></Where></Query><RowLimit>" + FileFoldeBatchSize + "</RowLimit></View>";

then when I want to query for files this :

query.ViewXml = @"<View><Query><Where><Eq><FieldRef Name='FSObjType' /><Value Type='Lookup'>0</Value></Eq></Where></Query><RowLimit>" + FileFoldeBatchSize + "</RowLimit></View>";

When trying to query for files, this will timeout with the following set:

clientContext.RequestTimeout = 1800000;

I've tried setting different row limits and still the same result

2

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.

1

You may try to add an OrderBy clause to your CAML query, to order by ID. ID is a naturally indexed column in SP, so ordering by ID should help overcome the 5000 threshold. To be done maybe in conjonction with a RowLimit to avoid the timeout...

<OrderBy><FieldRef Name='ID' Ascending='TRUE' /></OrderBy>

That would make:

query.ViewXml = @"<View><Query><Where><BeginsWith><FieldRef Name='ContentTypeId' /><Value Type='ContentTypeId'>0x0120</Value></BeginsWith></Where><OrderBy><FieldRef Name='ID' Ascending='TRUE' /></OrderBy></Query><RowLimit>" + FileFoldeBatchSize + "</RowLimit></View>";  

Update
As per this MSDN blog thread, we may need the Override='TRUE' attribute on the OrderByclause + Scope='Recursive' on the View. i.e.:

query.ViewXml = @"<View Scope='RecursiveAll'><Query><Where><BeginsWith><FieldRef Name='ContentTypeId' /><Value Type='ContentTypeId'>0x0120</Value></BeginsWith></Where><OrderBy Override='TRUE'><FieldRef Name='ID' Ascending='TRUE' /></OrderBy></Query><RowLimit>" + FileFoldeBatchSize + "</RowLimit></View>";  
  • Thanks for the reply, still the same. Just times out, however I have read that I could put an index on the content type. Of course this would have to be done at the start when the list is below the threshold limit – Webfort Apr 14 '15 at 10:04
  • We're maybe missing the Override attribute on the OrderBy clause + Scope='Recursive'`, as stated in social.technet.microsoft.com/Forums/office/en-US/…. Updated answer accordingly. – Evariste Apr 14 '15 at 11:08
  • Just updated my project and get the following exception Additional information: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator. – Webfort Apr 14 '15 at 11:15
  • Just being curious: what if you remove the Where clause? – Evariste Apr 14 '15 at 11:25
  • It works without the Where clause, I can download everything in batches without any problems, its just I wanted to know if you could query lists of this size. When I introduce the Where clause, this is when it times out. – Webfort Apr 14 '15 at 11:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.