Here’s a quick follow-up to my previous post on dealing with SharePoint list view thresholds. I just bumped up against another case where I had to change some code to deal with it.

To recap the project a bit, I am writing a console app that will import a bunch of data into a SharePoint site. Since I’m human, and I make mistakes, the first step of this importer is to delete any existing data in those lists (which would be leftover from the previous test run).

I had a simple solution for doing that, deleting records in batches of 100, based somewhat on this example. I assumed that would work OK, even with larger lists, but I didn’t take into account that the first step in my process was to get all items in the list. That, of course, fails for a very large list. So I had to change my code to initially get only the first 4000 records in the list. (That can be done with the RowLimit clause in CAML.) Then, I delete from that subset in batches of 100. Then, I just repeat that until there are no more records left.

As far as I can tell, there’s no SharePoint CSOM equivalent to SQL’s “truncate table”, which would have made this much easier. And I feel like I’m probably still not doing this in the most efficient way. If I was creating a process that needed to do this repeatedly, instead of just a few times, I’d dig into it some more. And if I was retrieving items instead of deleting them, I’d probably do something with ListItemCollectionPosition.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

private void deleteAllFromList(ClientContext cc, List myList)
{
    int queryLimit = 4000;
    int batchLimit = 100;
    bool moreItems = true;
 
    string viewXml = string.Format(@"
        <View>
            <Query><Where></Where></Query>
            <ViewFields>
                <FieldRef Name='ID' />
            </ViewFields>
            <RowLimit>{0}</RowLimit>
        </View>", queryLimit);
    var camlQuery = new CamlQuery();
    camlQuery.ViewXml = viewXml;
 
    while (moreItems)
    {
        ListItemCollection listItems = myList.GetItems(camlQuery); // CamlQuery.CreateAllItemsQuery());
        cc.Load(listItems,
            eachItem => eachItem.Include(
                item => item,
                item => item["ID"]));
        cc.ExecuteQuery();
 
        var totalListItems = listItems.Count;
        if (totalListItems > 0)
        {
            Console.WriteLine("Deleting {0} items from {1}...", totalListItems, myList.Title);
            for (var i = totalListItems - 1; i > -1; i--)
            {
                listItems[i].DeleteObject();
                if (i % batchLimit == 0)
                    cc.ExecuteQuery();
            }
            cc.ExecuteQuery();
        }
        else
        {
            moreItems = false;
        }
    }
    Console.WriteLine("Deletion complete.");
}