Querying document library (which contain more than 5,000 documents) using FolderServerRelativeUrl will raise error "exceeds the list view threshold"

Question

I have a document library which contain 6,000 ++ items, and i wrote the following code inside a remote event receiver to get the documents which are inside certain folder using FolderServerRelativeUrl, as follow:

ListItemCollectionPosition position = null;
do
{
    CamlQuery camlQuery6 = new CamlQuery();
    camlQuery6.ViewXml = @"<View Scope='Recursive'><Query></Query><RowLimit>5000</RowLimit></View>";
    //string.Format("<View Scope=\"RecursiveAll\"><Query><Where><Neq><FieldRef Name='ID' /><Value Type='Text'>{0}</Value></Neq></Where></Query><RowLimit>{1}</RowLimit></View>", "0", "3000");

    camlQuery6.ListItemCollectionPosition = position;
    camlQuery6.FolderServerRelativeUrl = context.Web.ServerRelativeUrl + "/ArchDocs/" + currentFilingSystemItem["DealName"].ToString();

    ListItemCollection collListItem6 = context.Web.GetList(context.Web.ServerRelativeUrl + "/ArchDocs").GetItems(camlQuery6);
    context.Load(collListItem6, items => items.Include(
                                                item => item.Id,
                                                item => item["FileDirRef"],
                                                item => item["Title"],
                                                 ), items => items.ListItemCollectionPosition);
    context.ExecuteQuery();
    position = collListItem6.ListItemCollectionPosition;

    foreach (ListItem listItem in collListItem6) // collect the items we found in this chuck of searched items
    {
         foundListItems.Add(listItem);
    }
}
while (position != null);

foreach (ListItem item in foundListItems)
{

}

but my above code will raise this error, although i am using paging which should allow me to query large document libraries :-

The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.

any advice on this please? Thanks

Answer 1

At any circumstances we cannot retrieve more than 5000 items from SharePoint list, so we must use the rowlimit parameter in the camel query, as I see you have already used it.

You may optimize your by the below example:

What you can do is. You fetch all the items/documents 10,000 and keep it in collection object and query this collection object using Linq expression. so that your job will be easy and it will filter data perfectly. see below sample code:

var web = clientContext.Web;
var list = web.Lists.GetByTitle("listName");
clientContext.Load(list);
clientContext.ExecuteQuery();

CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = "<View Scope='RecursiveAll'><RowLimit>5000</RowLimit></View>";
List<ListItem> items = new List<ListItem>();

do
{
    ListItemCollection listItemCollection = list.GetItems(camlQuery);
    clientContext.Load(listItemCollection);
    clientContext.ExecuteQuery(); //Adding the current set of ListItems in our single buffer
    items.AddRange(listItemCollection); //Reset the current pagination info
    camlQuery.ListItemCollectionPosition = listItemCollection.ListItemCollectionPosition;
}
while (camlQuery.ListItemCollectionPosition != null);

var filteritems = items.Where(tt =>tt.FieldValues[""]=="") 

Few notable checkpoints to optimize the query :

• Try to query the data using the indexed columns.
• Try to query the specific view which has less than 5000 items.
• Check the default view in SharePoint whether it is able to load or not.

Similar question is discussed here: CAML Query - Going around the 5000 List Item Threshold