3

I am using SharePoint online and SharePoint 2013+ onprem.

I have written a CSOM rest program that fetches all the items in a list and fetches list items in 5000 item batches, the maximum allowed by the list item view threshold.

Here is an example of the XML payload of the CSOM request to get one of the pages.

<Request AddExpandoFieldTypeSuffix="true" SchemaVersion="14.0.0.0" LibraryVersion="16.0.0.0"
         ApplicationName=".NET Library" xmlns="http://schemas.microsoft.com/sharepoint/clientquery/2009">
    <Actions>
        <ObjectPath Id="10019" ObjectPathId="10018"/>
        <Query Id="10020" ObjectPathId="10018">
            <Query SelectAllProperties="true">
                <Properties/>
            </Query>
            <ChildItemQuery SelectAllProperties="true">
                <Properties>
                    <Property Name="Id" ScalarProperty="true"/>
                    <Property Name="DisplayName" ScalarProperty="true"/>
                    <Property Name="HasUniqueRoleAssignments" ScalarProperty="true"/>
                    <Property Name="File" SelectAll="true">
                        <Query SelectAllProperties="false">
                            <Properties/>
                        </Query>
                    </Property>
                    <Property Name="ContentType" SelectAll="false">
                        <Query SelectAllProperties="false">
                            <Properties>
                                <Property Name="Name" ScalarProperty="true"/>
                            </Properties>
                        </Query>
                    </Property>
                </Properties>
            </ChildItemQuery>
        </Query>
    </Actions>
    <ObjectPaths>
        <Method Id="10018" ParentId="9" Name="GetItems">
            <Parameters>
                <Parameter TypeId="{3d248d7b-fc86-40a3-aa97-02a75d69fb8a}">
                    <Property Name="AllowIncrementalResults" Type="Boolean">false</Property>
                    <Property Name="DatesInUtc" Type="Boolean">true</Property>
                    <Property Name="FolderServerRelativePath" Type="Null"/>
                    <Property Name="FolderServerRelativeUrl" Type="Null"/>
                    <Property Name="ListItemCollectionPosition" TypeId="{922354eb-c56a-4d88-ad59-67496854efe1}">
                        <Property Name="PagingInfo" Type="String">Paged=TRUE&amp;p_ID=10000</Property>
                    </Property>
                    <Property Name="ViewXml" Type="String">&lt;View Scope="RecursiveAll"&gt;&lt;RowLimit&gt;5000&lt;/RowLimit&gt;&lt;/View&gt;</Property>
                </Parameter>
            </Parameters>
        </Method>
        <Identity Id="9"
                  Name="740c6a0b-85e2-48a0-a494-e0f1759d4aa7:site:2386a403-8d76-4737-b774-dabad52201e3:web:7a2f544f-e3ed-444e-8de3-178c2c9b5848:list:63029d5e-0588-4406-89f4-9b5a69ae5d81"/>
    </ObjectPaths>
</Request>

Notice the Paged=TRUE&amp;p_ID=10000 is specified which means get me the next page of results starting at p_ID 10000.

So the simple algorithm to fetch all the items is this:

next_page_id = 0
while (next_page_id is not null)
   get next page of list items starting at next_page_id.
   if there is another page
      set next_page_id to that page id.
   else
      set next_page_id to null.

But the main flaw with this is that now a list with 1,000,000 items can only be fetched with a single thread. Which means that this can potentially fetch for hours and hours if a single list has millions of records in it.

To get past this, I tried to use the List.ItemCount to create a blocking collection of pages that we need to fetch. For example, if you have 150000 records in a list, create Paged=TRUE&amp;p_ID=0, Paged=TRUE&amp;p_ID=5000, and Paged=TRUE&amp;p_ID=10000 and a thread pool can grab pages to fetch until there is no more work to do.

But this isn't correct. You will end up with incomplete lists, because the p_ID is not the "index of result." It has some other meaning that I do fully understand.

Is there a way to do what I am trying to do?

1 Answer 1

2
+50

I had same requirement and here is what i did,

  1. First, get ID of last created item from the list.

    ClientContext clientContext = new ClientContext("your site"); 
    Microsoft.SharePoint.Client.List spList = 
    clientContext.Web.Lists.GetByTitle("TestList"); 
    clientContext.Load(spList); 
    clientContext.ExecuteQuery(); 
    
    if (spList != null && spList.ItemCount > 0) 
    {
     Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery(); 
    camlQuery.ViewXml = 
      @"<View>  
            <Query> 
               <OrderBy><FieldRef Name='ID' Ascending='FALSE' /></OrderBy> 
            </Query> 
             <ViewFields><FieldRef Name='ID' /></ViewFields> 
            <RowLimit>1</RowLimit> 
      </View>";  
    
    ListItemCollection listItems = spList.GetItems(camlQuery); 
    clientContext.Load(listItems); 
    clientContext.ExecuteQuery(); 
    }
    

    Add SharePointOnlineCredentials in above code for SharePoint Online.

  2. Now divide this ID into chunks or batches (in your case batch of 5000).

for e.g. if you get last ID=13000 then there will be 3 batches,

Batch 1:1 to 5000.
Batch 2: 5001 to 10000
Batch 3: 10001 to 13000.
  1. Use multi-threading or Parallel.For to execute all batches parallely at same time.

https://docs.microsoft.com/en-us/dotnet/api/system.threading.tasks.parallel.for?view=netframework-4.8 Parallel.ForEach is also there. so you can use according to your convenience.

5
  • Brilliant. This sounds correct. I'll implement it. Commented Jul 18, 2019 at 14:28
  • Confirmed. this works great. Commented Jul 24, 2019 at 20:38
  • 1
    Note that your code is doing the right thing but your description is not correct: you say you’re getting the ID of the last modified item; in fact you’re getting the ID of the last created item.
    – sasfrog
    Commented Jul 26, 2019 at 22:04
  • So when you request for 0-5000 it basically fetches 5000 entries and the last item id returned could be 6800. Now on the next request you are making is from item Id 5000. Then it will fetch those 1800+ additional items. This is undesirable because it causes you to re-download list item metadata for 1800 items twice. are there any better approaches to this? Commented Oct 27, 2020 at 13:12
  • this approach pretty much works, but it's got some short comings. hoping for a better answer. so i cranked up the bounty for anyone who knows a better way Commented Mar 25, 2022 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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