ListView Threshold - Caml Query- Without using RecursiveAll scope

Puneeth, C 81 Reputation points
2022-04-14T05:44:57.643+00:00

I have a list whose root location has about 54K items. This list also has some folders in it and these folders do have their own items. All in all, the entire list has about 58K items.
Now, I have found the following query on the forums to get all the items in the list without getting the Threshold Limit error by using Pagination.

<View Scope=\"RecursiveAll\"><Query><OrderBy><FieldRef Name='Title' Ascending='TRUE'/></OrderBy></Query><RowLimit Paged=\"TRUE\">4000</RowLimit></View>

The problem with this is that because of RecursiveAll scope, it is going to fetch every last item in the list. Whereas I am only interested in the items that are in the root location of the list. I want to ignore all the items inside folders and subfolders.
I modified the above query to the following but it throws the error - The attempted operation is prohibited because it exceeds the list view threshold.
Object reference not set to an instance of an object.

<View Scope=\"FilesOnly\"><Query><OrderBy><FieldRef Name='Title' Ascending='TRUE'/></OrderBy></Query><RowLimit Paged=\"TRUE\">4000</RowLimit></View>

How to get around this problem?

Here's my code as it is for your reference:

                        ListItemCollectionPosition position = null;
                        int total = 0;
                        do
                        {
                            var connection = GetConnectionFromListRoot(context, listName, position);
                            Web targetWeb = connection.web;
                            List tempList = connection.list;
                            ListItemCollection listItems = connection.listItems;
                            total += listItems.Count;
                            position = listItems.ListItemCollectionPosition;
                        }
                        while (position != null);




public static ConnectionClass GetConnectionFromListRoot(ClientContext context, String listName, ListItemCollectionPosition position)
        {
            ConnectionClass cxn = new ConnectionClass();
            try
            {
                Web targetWeb = context.Web;
                context.Load(targetWeb);
                List tempList = targetWeb.Lists.GetByTitle(listName);

                context.Load(tempList);
                context.ExecuteQuery();

                //CAML query
                CamlQuery camlQuery = new CamlQuery();

                string queryText = "<View Scope=\"FilesOnly\"><Query><OrderBy><FieldRef Name='Title' Ascending='TRUE'/></OrderBy></Query><RowLimit Paged=\"TRUE\">4000</RowLimit></View>";

                camlQuery.ListItemCollectionPosition = position;
                camlQuery.ViewXml = queryText;

                // Executing the query
                ListItemCollection currentCollection = tempList.GetItems(camlQuery);               
                context.Load(currentCollection);
                context.ExecuteQuery();
                //Connection
                cxn.web = targetWeb;
                cxn.list = tempList;
                cxn.fieldCollection = fColl;
                cxn.listItems = currentCollection;

            }
            catch(Exception e)
            {
                Console.WriteLine("Exception occured: "+e.Message);
            }
            return cxn;
        }
SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
9,409 questions
SharePoint Development
SharePoint Development
SharePoint: A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.Development: The process of researching, productizing, and refining new or existing technologies.
2,615 questions
{count} votes

3 answers

Sort by: Most helpful
  1. RaytheonXie_MSFT 30,186 Reputation points Microsoft Vendor
    2022-04-15T09:13:02.253+00:00

    Hi @Puneeth, C ,
    We have 5000 as the hard limit for the list view threshold in SharePoint Online. You cannot fetch the List items more than threshold limit. You need to use RecursiveAll to avoid this.
    You can try following caml query.

    <View  Scope=\"RecursiveAll\"><ViewFields><FieldRef Name='Title' /><FieldRef Name='FileLeafRef' /><FieldRef Name='ID' /><FieldRef Name='ContentType' /></ViewFields><Query>   
                 <Where><IsNotNull><FieldRef Name='File_x0020_Type' /></IsNotNull></Where>   
                 </Query></View>  
    

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



  2. Puneeth, C 81 Reputation points
    2022-04-18T08:40:19.657+00:00

    Nothing worked for me. So, I leveraged views to achieve the same. I created views with < 5000 items in each and then ran the job using the following code. I just iterated using a do-while loop and it worked.

                            List list = web.Lists.GetByTitle(listName);
                             context.Load(list);
                             context.ExecuteQuery();
    
                             View view = list.DefaultView;
                             view.RowLimit = 4000;
                             context.Load(view);
                             context.ExecuteQuery();
                             CamlQuery query = new CamlQuery();
    
                             query.ViewXml = view.ListViewXml;
    

  3. RaytheonXie_MSFT 30,186 Reputation points Microsoft Vendor
    2022-04-26T07:54:09.627+00:00

    Hi @Puneeth, C ,
    Great to know that it works now and thanks for sharing the update here.

    By the way, since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others.". and according to the scenario introduced here: Answering your own questions on Microsoft Q&A, I would make a brief summary of this thread:

    [How do deal with ListView Threshold?]

    Issue Symptom:
    Caml Query throws the error - The attempted operation is prohibited because it exceeds the list view threshold. when the items count over 5k

    Current status:
    Create views with < 5000 items in each and then ran the job using the following code. Just iterated using a do-while loop and it worked.

     List list = web.Lists.GetByTitle(listName);  
      context.Load(list);  
      context.ExecuteQuery();  
      
      View view = list.DefaultView;  
      view.RowLimit = 4000;  
      context.Load(view);  
      context.ExecuteQuery();  
      CamlQuery query = new CamlQuery();  
      
      query.ViewXml = view.ListViewXml;  
    

    You could click the "Accept Answer" button for this summary to close this thread, and this can make it easier for other community member's to see the useful information when reading this thread. Thanks for your understanding!

    0 comments No comments