I have a list whose root location has about 54K items. This list also has some folders in it and these folders do have their own items. All in all, the entire list has about 58K items.
Now, I have found the following query on the forums to get all the items in the list without getting the Threshold Limit error by using Pagination.
<View Scope=\"RecursiveAll\"><Query><OrderBy><FieldRef Name='Title' Ascending='TRUE'/></OrderBy></Query><RowLimit Paged=\"TRUE\">4000</RowLimit></View>
The problem with this is that because of RecursiveAll scope, it is going to fetch every last item in the list. Whereas I am only interested in the items that are in the root location of the list. I want to ignore all the items inside folders and subfolders.
I modified the above query to the following but it throws the error - The attempted operation is prohibited because it exceeds the list view threshold.
Object reference not set to an instance of an object.
<View Scope=\"FilesOnly\"><Query><OrderBy><FieldRef Name='Title' Ascending='TRUE'/></OrderBy></Query><RowLimit Paged=\"TRUE\">4000</RowLimit></View>
How to get around this problem?
Here's my code as it is for your reference:
ListItemCollectionPosition position = null;
int total = 0;
do
{
var connection = GetConnectionFromListRoot(context, listName, position);
Web targetWeb = connection.web;
List tempList = connection.list;
ListItemCollection listItems = connection.listItems;
total += listItems.Count;
position = listItems.ListItemCollectionPosition;
}
while (position != null);
public static ConnectionClass GetConnectionFromListRoot(ClientContext context, String listName, ListItemCollectionPosition position)
{
ConnectionClass cxn = new ConnectionClass();
try
{
Web targetWeb = context.Web;
context.Load(targetWeb);
List tempList = targetWeb.Lists.GetByTitle(listName);
context.Load(tempList);
context.ExecuteQuery();
//CAML query
CamlQuery camlQuery = new CamlQuery();
string queryText = "<View Scope=\"FilesOnly\"><Query><OrderBy><FieldRef Name='Title' Ascending='TRUE'/></OrderBy></Query><RowLimit Paged=\"TRUE\">4000</RowLimit></View>";
camlQuery.ListItemCollectionPosition = position;
camlQuery.ViewXml = queryText;
// Executing the query
ListItemCollection currentCollection = tempList.GetItems(camlQuery);
context.Load(currentCollection);
context.ExecuteQuery();
//Connection
cxn.web = targetWeb;
cxn.list = tempList;
cxn.fieldCollection = fColl;
cxn.listItems = currentCollection;
}
catch(Exception e)
{
Console.WriteLine("Exception occured: "+e.Message);
}
return cxn;
}