Retrieving list Items programmatically from CAML via Client Side object Model when a Document Library has more than 5000 items

Question

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.

1. Indexing the Columns that CAML query apply filters

2. 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

Answer 1

This is one big disadvantage, well there is no way to increase that threshold in SharePoint Online, all you can do is create different views and query them or split the list into multiple.

ClientContext context = new ClientContext("http://sp2013sps/sites/test");
        List list = context.Web.Lists.GetByTitle("List66688");
        context.Load(list);
        context.ExecuteQuery();
        View view = list.Views.GetByTitle("All Items");

        context.Load(view);
        context.ExecuteQuery();
        CamlQuery query = new CamlQuery();
        query.ViewXml = view.ViewQuery;

        ListItemCollection items = list.GetItems(query);
        context.Load(items);
        context.ExecuteQuery();
        Console.Write(items.Count);

Check this question

Answer 2

If the list items are getting created by custom forms then create a column that can have a concatenated value of first two level of filters(in your case Technology and app_status). Index the column, now you should be able to use that column in your CAML Query.