4

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

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

  • Thanks for the reply. Even When we are creating a view we should create that view in a way it return only 5000 right? Is there a way to query a created view using CAML in CSOM? because in this scenario i need to manipulated the data in my own code instead of showing a view in the MVC app. – Pubudu Tennakoon Jun 15 '15 at 4:04
  • Yes you can query view and it should return less than 5000 items i have modified the code above to query View – Akshay Randive Jun 15 '15 at 4:18
  • Yes that approach is available. but still i need to figure out a way to create the view that will have less than 5000. lets say i created multiple views using modified date, created date as filters that will not have more than 5000 items. is it possible for me to query those views in CSOM using more filters and get the needed data. this is because i need to generate the CAML query dynamically according to user input. Thanks again for helping – Pubudu Tennakoon Jun 15 '15 at 4:43
  • Yes you can query custom created view with filters to get desired data, give it a try just create view and write query or try using U2U caml builder or SPcaml query helper to test quries on views created by you – Akshay Randive Jun 15 '15 at 5:22
  • Hi @AkshayRandive. I have tried to use your method but my ListItemCollection is being populated with all the items in the root of the library (over 3000) when the query actually should return 55 results. What could be the reason? Are you sure that your solution should work? – Jurgen Cuschieri Oct 3 '17 at 15:46
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.