Retrieving all items from a list with CSOM

Retrieving all items from a list with CSOM

When working with large lists in Office 365 you are most likely to be stuck with the List View Threshold. This threshold allows you to retrieve a maximum number of items. By default this limit is set to 5.000, and OneDrive for Business has a 20.000 limit. So any list with more then 5.000 items can cause some problems in your apps.

Luckily CSOM allows you to retrieve all items by using the ListItemCollectionPostion. Every time you execute a query for list items, you will be presented with a ListItemCollection that contains the ListItemCollectionPosition. If that ListItemCollectionPosition is not null you can use that position to execute the same query again, however with a different starting point. This way you can ‘loop’ through all items in a list and construct an object that contains all your items. By putting everything in a while loop you are making sure that you will retrieve all items.

In the following sample the rowlimit is set to 100 so that any list with more then 100 items will be queried multiple times until all items are retrieved.

private async Task<List<ListItem>> GetListItems(string filterField) {
    List<ListItem> items = new List<ListItem>();

    using (ClientContext context = SharePointContext.GetSharePointContext()) {
        List list = context.Web.Lists.GetByTitle("ListToRetrieveItems");
        int rowLimit = 100;
        ListItemCollectionPosition position = null;

        string viewXml = string.Format(@"
                <View>
                    <Query>
                        <Where>
                            <Eq>
                                <FieldRef Name='FilterField' />
                                <Value Type='Text'>{0}</Value>
                            </Eq>
                        </Where>
                    </Query>
                    <ViewFields>
                        <FieldRef Name='Title' />
                    </ViewFields>
                    <RowLimit>{1}</RowLimit>
                </View>", filterField, rowLimit);

        var camlQuery = new CamlQuery();
        camlQuery.ViewXml = viewXml;

        do {
            ListItemCollection listItems = null;
            if (listItems != null && listItems.ListItemCollectionPosition != null) {
                camlQuery.ListItemCollectionPosition = listItems.ListItemCollectionPosition;
            }

            listItems = list.GetItems(CamlQuery.CreateAllItemsQuery());
            context.Load(listItems);
            Task contextTask = context.ExecuteQueryAsync();

            await Task.WhenAll(contextTask);

            position = listItems.ListItemCollectionPosition;

            items.AddRange(listItems.ToList());
        }
        while (position != null);
    }
    return items;
}
There are 4 comments for this article
  1. shawn at 06:05

    thanks for this, it gave me some clues – what i'm looking for is samples similar to the above in (limiting the list view threshold) with allowing me to iterate folders and files in the same manner. do you have any samples you could point me to?

  2. Slava Gorbunov at 05:17

    hello Albert-Jan, I am trying to figure out the way you query items in the example provided in the post. The camlQuery defined at line 25: var camlQuery = new CamlQuery(); doesn't seem to be used to actually query the items. What I can see when querying is listItems = list.GetItems(CamlQuery.CreateAllItemsQuery()); @ line 34.
    How the position is being moved through the collection in this case?
    Just can't get it.

    I am trying to query the large list using this approach, but still get the "Microsoft.SharePoint.Client.ServerException: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator." when trying query the items with the rowlimit set to something small

Leave a Reply