Retrieving all items from a list with CSOM

Retrieving all items from a list with CSOM header image

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;
}
Loading comments…