CSOM returning "The attempted operation is prohibited because it exceeds the list view threshold." if more than a single result is returned -SP Online

Question

I have code that scans a document library holding a list of InfoPath forms. It uses CSOM with a caml query to get the specific forms it needs to work with. I have had this code running for years with no issues. Today, I started receiving the error "The attempted operation is prohibited because it exceeds the list view threshold."

This is SharePoint Online and I know the list view threshold to be 5000. We do have more than 5000 items in the list and have for some time. The results I'm trying to retrieve should be bringing back about 10 forms; well under the 5000 item threshold. Both of the fields I query with are indexed.

ListItemCollection spList = clientContext.Web.Lists
    ?.GetByTitle(libName)
    ?.GetItems(new CamlQuery
    {
       ViewXml = "<View><Query><Where><And><Eq><FieldRef Name=\"Field1\"/><Value Type=\"Text\">No</Value></Eq><Eq><FieldRef Name=\"Field2\"/><Value Type=\"Text\">No</Value></Eq></And></Where></Query></View>"
    });
clientContext.Load(spList);

clientContext.Load(spList, items => items.Include(
    item => item.ContentType,
    item => item.DisplayName,
    item => item.ContentType.Fields,
    item => item.Folder,
    item => item.Folder.Files,
    item => item.File.ListItemAllFields,
    item => item.FileSystemObjectType,
    item => item.File,
    item => item.File.Author,
    item => item.File.ServerRelativeUrl
));
clientContext.SendRequest();

When testing the issue, I found that I can retrieve an item by ID

"<View><Query><Where><Eq><FieldRef Name='ID' /><Value Type='Number'>16134</Value></Eq></Where></Query></View>"

However, when trying to use Or and include two IDs, I get the error again.

"<View><Query><Where><Or>" + 
"<Eq><FieldRef Name='ID' /><Value Type='Number'>6001</Value></Eq>" + 
"<Eq><FieldRef Name='ID' /><Value Type='Number'>6002</Value></Eq>" + 
"</Or></Where></Query></View>"

It seems the threshold is now behaving as if it is one. I'm not sure if that is due to how I'm querying the data and for some reason all results are being returned or if there is something internally wrong.

My first thought is that something is up on Microsoft's side, but I see no mention in the health center or anywhere else.

Is there anything known that could be causing this and any suggestions on how to correct the issue? Possible a recent change or depreciation from MS?

Answer 1

Try creating an index on any columns that are referenced in your view's where clause, in your case "Field1","Field2". You can do this on-prem or online in list-settings-> "indexed columns". Otherwise, sharepoint attempts to read the whole list trying to evaluate the where clause and consequently stumbles over the 5000 item threshold.