I have a SharePoint provider hosted app that stores documents with metadata in a Single Document Libraray programmatically via CSOM.
I have a requirement to display the list items in a MVC web app according to the filters that user specifies on the metadata in the list items.
I am using CAML Queries to retrieve the list items according the given filters. The queries works fine when the item count is below the list view threshold in SharePoint Online which is 5000. But after the 5000 limit is exceeded SharePoint returns a sever exception saying you have exceeded the limit.
After some research I took the following approaches to deal with the list with more than 5000 items.
Indexing the Columns that CAML query apply filters
Specifying a row limit
Even though the Columns are indexed if the the list have more than 5000 items that match a filter on a column in the query it still throws the sever exception. The specified row limit seems not have an effect.
I have mentioned a example scenario below.
camlQueryList.ViewXml = @"<View Scope='Recursive'>" + "<ViewFields>" + "<FieldRef Name='Title'/>" + "<FieldRef Name='app_Status'/>" + "<FieldRef Name='app_Organization'/>" + "<FieldRef Name='app_Type'/>" + "</ViewFields>" + "<Query>"+ "<Where>"+ "<And>"+ "<And>"+ "<Contains>"+ "<FieldRef Name="app_Type" />"+ "<Value Type="Text">Technology</Value> "+ "</Contains>"+ "<Eq>"+ "<FieldRef Name="app_Status" /> "+ "<Value Type="Text">Expired</Value> "+ "</Eq>"+ "</And>"+ "<Contains>"+ "<FieldRef Name="app_Organization" /> "+ "<Value Type="Text">Company10</Value> "+ "</Contains>"+ "</And>"+ "</Where>"+ "</Query>"+ "<RowLimit>25<RowLimit>"+ "</View>";
In the above will fail if I have more than 5000 items which contain "Technology" phrase in the column "Type".
I have tried the REST API as well. It gives the same Exception when the limit is reached.
The filtration needs to happen on every user requests, the CAML query will be generated dynamically so I wont be able to optimize the query to the point that 1st condition filter will reduce the items count below 5000.
Since this is SharePoint Online, we dont have the option to increase the list view threshold.
Does any one know any workaround to overcome this issue?
Edit : Im Using App Only Policy for authentication