How to overcome SharePoint list view threshold with CAML query?

Question

I have a few large document libraries with more than 5000 documents. I'm using the following CAML query to get the document list

<View Scope='Recursive'> 	
	<ViewFields> 		
		<FieldRef Name='Column_A' /> 		
		<FieldRef Name='Column_B' /> 		
		<FieldRef Name='Column_C' />	
	</ViewFields> 	
	<Query> 		
		<Where> 			
			<And> 				
				<And> 					
					<IsNotNull> 						
						<FieldRef Name='Column_A' /> 					
					</IsNotNull>
 					<IsNotNull> 						
						<FieldRef Name='Column_B' />
					</IsNotNull>
 				</And>
 				<IsNull>
 					<FieldRef Name='Column_C' />
 				</IsNull>
 			</And>
 		</Where>
 	</Query>
 	<RowLimit>1000</RowLimit>
 </View>

Here is the calling code:

ListItemCollectionPosition pos = null;                             
CamlQuery camlQuery = new CamlQuery                             
{
	ViewXml = caml                             
};
do                             
{
	if (pos != null)                                 
	{
		camlQuery.ListItemCollectionPosition = pos;                                 
	}
	ListItemCollection listItemCollection = currentList.GetItems(camlQuery);
	ctx.Load(listItemCollection);
	ctx.ExecuteQueryRetry(); //fails right here
	pos = listItemCollection.ListItemCollectionPosition;
} while (pos != null);

The exception thrown is

Microsoft.SharePoint.Client.ServerException: The attempted operation is prohibited because it exceeds the list view threshold

AFAIK, the <RowLimit>1000</RowLimit> will limit the number of items retrieved from every query. Why does it throw the above exception? All 3 columns in the query are indexed in the library.

Answer 1

Hi @Jack Le

We are unable to fetch the List items more than threshold limit. For example: you have 10,000 items, there are 3000 item which meets your caml expression. But it won't retrieve rather you will get exceed threshold limit exception.

You can fetch all the items/documents 10,000 and keep it in collection object and query this collection object using Linq expression. Please refer to below 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["Column_A"] != null) &&
            (tt.FieldValues["Column_B"] != null) &&
            (tt.FieldValues["Column_C"] == "") 
           ); 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi.

First, about the Threshold - you will get an error if SP either calculates that the result is bugger than 5000 items, or if SP needs to query more than 5000 items to determine the result. So, for instance, if you have a query with unindexed rows that will return one item, you will still hit the Threshold error.

There are two different options that can work for you. The recommended one is to use RenderrListAsStream method that returns result with any threshold issues. It's more complicated than the simple API to load items but is well worth it.

Theo other option is to carefully edit the caml query. I did it with web-serices at the time & the goal is to create an empty query - withou any 'where' clause at all (if my memory serves me correctly).

Good luck!