The attempted operation is prohibited because it exceeds the list view threshold

[Ankush_Daga]

I am using caml query as below:

"<View><Query><Where><And><Eq><FieldRef Name = 'NumericRecordId'/><Value Type = 'Integer'>xxxxxxx</Value></Eq><Eq><FieldRef Name = 'ContextId'/><Value Type = 'Integer'>xxxxxx</Value></Eq></And></Where><View><OrderBy><FieldRef Name='NumericRecordId' Ascending='False' /></View></Query><ViewFields><FieldRef Name='Title' /></ViewFields><RowLimit>100</RowLimit></View>"

This works fine for records less than 5000 in my document library. But once it exceeds 5000 i get threshold error.

ContextId and NumericRecordId are Indexed columns. I tried many different online solutions but not able to get through this. Any help will be much appreciated. Thanks

[#2]

Well, what I would recommend here is to use search API instead of doing a CAML Query

[#3]

@jcgonzalezmartin  Thanks for your suggestion . can you just provide an  example on how to call them with multiple parameters and integrate them?  

[#4]

@jcgonzalezmartin  I tried with the search api's as well and again ran into same issue after 5000 records as api returns 500 (Internal Server Error) while applying filters.

[#5]

@Ankush_Daga Have you tried using PnPJS with paged items method. Please refer the below link

https://pnp.github.io/pnpjs/sp/items/#get-paged-items

Please refer the below link using Search API using PnPJS

https://pnp.github.io/pnpjs/sp/search/#paging-with-searchresultsgetpage

Hope it helps, please like it or mark it as a solution if it resolves ur clarification or issue
-Sudharsan K...

[#6]

@Sudharsan K  Thank for your response but this seems to be through javascript. I am making a call to search api through c# desktop application. So it would not be a feasible solution for me.

[#7]

@Ankush_Daga Can you refer the below links and let me know if it suits you.

https://www.codeproject.com/Articles/281693/Paging-SharePoint-List-Items

https://keepitsimplekarthik.com/sharepoint/listitemcollectionposition-and-pageinfo/

[#8]

@Sudharsan K  I have already tried using 'ListItemCollectionPosition' . It works fine with records less than 5000 but once my document library exceeds files count then it throws the threshold error. So without removing 'Where' clause from the query or 'Filters' from the api's it throws an error (possibly due to threshold limit). If i remove this where clause or filter then it works fine with row limit as well as pagination. But this is again a perfromance hit as It will be bringing all the records rather than filtering them. So i am looking for some approach in which it works with  where (caml query) or filter (Rest api's).

[#9]

Hi,
Have you tried using query.QueryThrottleMode = SPQueryThrottleOption.Override;

Refer the below link too

https://www.codeproject.com/articles/1076854/overcoming-the-list-view-threshold-in-sharepoint-c