Caml Query RowFilter doesn't work with WHERE conditions - List view threshold

Question

I have some troubles with CAML Query on a 8000+ List on Sharepoint. I want to paginate the query in order to avoid this issue. I used this c# code:

List spList = clientContext.Web.Lists.GetByTitle(ConfigurationManager.AppSettings["ListaDocumenti"]);
clientContext.Load(spList);
clientContext.ExecuteQuery();

if (spList != null && spList.ItemCount > 0)
{
CamlQuery camlQuery = new CamlQuery();
string query = @"<View Scope='RecursiveAll'>
 <QueryOptions><ViewAttributes Scope='Recursive'/></QueryOptions>
 <RowLimit>1000</RowLimit>
 <Query>
  <Where>                                    
   <And>
    <And>                                                    
     <And>
      <Eq>
       <FieldRef Name='p_notifica' />
       <Value Type='Boolean'>0</Value>
      </Eq>
      <Leq>
       <FieldRef Name='p_DataPubblicazione' />
       <Value IncludeTimeValue='false' Type='DateTime'>{0}</Value>
      </Leq>
     </And>                                                      
     <IsNotNull>
      <FieldRef Name='p_CodiceCliente' />
     </IsNotNull>
    </And>
    <Eq>
     <FieldRef Name='FSObjType' />
     <Value Type='Integer'>0</Value>
    </Eq>
   </And>
  </Where>
 </Query>                                          
</View>";
camlQuery.ViewXml = String.Format(query, date.AddDays(offset).ToString("s") + "Z"/*"2018-10-12T00:00:00Z"*/);
bool morerecords = false;
List<SPDocument> list_documents = new List<SPDocument>();
do
{
 ListItemCollection listItems = spList.GetItems(camlQuery);
 clientContext.Load(listItems);
 clientContext.ExecuteQuery();

 morerecords = listItems.Count == 1000; 

 list_documents.Add(...)

 camlQuery.ListItemCollectionPosition = listItems.ListItemCollectionPosition;
} while (morerecords);

Lunching this query in production environment (8000+ items), it returns "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator" error, in test environment all works right (<5000 documents).

If I remove the "Where" conditions, it works right but the entire list is returned (with the limit of 1000 items). What can I try in order to paginate with the "Where" conditions enabled?

Thanks

5000 or 1000? Where's the C# code? Did you use the ListItemCollectionPosition property? — Panagiotis Kanavos
Try indexing the columns that you use in your where clause — Jack Le
Another option is to load the ListItemCollection without the <Where> clause and then filter them using Linq — Jack Le

Rafał Milewski Rafał Milewski · Accepted Answer · 2019-08-19T11:37:42

When working with large lists in SharePoint (5k+) it's better to avoid CAML queries. You can query specific lists or content types using SharePoint Search in JSOM/CSOM/Server, but you must set indexed columns on lists and re-index those lists.

Caml Query RowFilter doesn't work with WHERE conditions - List view threshold

1 Answers