SharePoint Online List View Thresholds

Running CAML queries against List Views with a large number of items (> 5000) can fail with the following error.

The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.

To overcome this problem we have to ensure our query meets these requirements when running against SharePoint Online.

  • Run the query in a loop a number of times using the ListItemCollectionPosition to keep track of the looping process.
  • Ensure that the columns specified in any Query Where clause and the OrderBy are already indexed on the list view. If you include columns in either of these parts of the query that are not indexed then you will still get the same error.
  • Ensure the Query AllowIncrementalResults is set to true
  • If you want to restrict the Query to a particular folder in the List View set the FolderServerRelativeUrl property.

I used PowerShell to test out my CAML query.

#Load SharePoint CSOM Assemblies
$folder = "C:\Nuget\packages\Microsoft.SharePointOnline.CSOM.16.1.8412.1200\lib\net45"
Add-Type -Path "$folder\Microsoft.SharePoint.Client.dll"
Add-Type -Path "$folder\Microsoft.SharePoint.Client.Runtime.dll"
   
#Variables for Processing
$SiteUrl = "https://mysharepoint.sharepoint.com/sites/mysite"
$ListName="MyList"
 
$UserName="me@mysharepoint.onmicrosoft.com"
$Password ="nottellingyou"
  
#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))
  
#Set up the context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl) 
$Context.Credentials = $credentials

try {
   
    #Get the List
    $List = $Context.web.Lists.GetByTitle($ListName)

    $Context.Load($List)
    $Context.ExecuteQuery()

    $position = $null

    $allItems = @()

    Do {
        $Query = New-Object Microsoft.SharePoint.Client.CamlQuery;
        # Define the starting position
        $Query.ListItemCollectionPosition = $position

        $Query.ViewXml = "<View Scope='RecursiveAll'>  
                            <Query> 
                                <Where>
                                    <And>
                                        <And>
                                            <And>
                                                <Eq><FieldRef Name='ReportName' /><Value Type='Text'>Monthly Report</Value></Eq>
                                                <Eq><FieldRef Name='Category' /><Value Type='TaxonomyFieldType'>Statement</Value></Eq>
                                            </And>
                                            <Eq><FieldRef Name='ProductLine' /><Value Type='TaxonomyFieldType'>Credits</Value></Eq>
                                        </And>
                                        <Eq><FieldRef Name='ContentType' /><Value Type='Computed'>Report</Value></Eq>
                                    </And>
                                </Where>
                            </Query> 
                        </View>"

        # Define a folder for the starting point of the recursive search
        $Query.FolderServerRelativeUrl = "/sites/MySite/MyList/Folder1"
        $Query.AllowIncrementalResults = $true

        $ListItems = $List.GetItems($Query) 
        $Context.Load($ListItems)

        $Context.ExecuteQuery()    
    
        # Increment the next position for the search
        $position = $ListItems.ListItemCollectionPosition
    
        $allItems += $ListItems
    }
    Until($position -eq $null)
}
Catch {
    Write-Error $_.Exception.Message
    Break
} 


write-host "Total Number of List Items found:"$allItems.Count
 
#Loop through each item
$allItems | ForEach-Object {
    #Get the Title field value

    $Context.Load( $_.File)
    $Context.Load( $_.File.ListItemAllFields)
    $Context.ExecuteQuery()
    write-host $_.File.Name
}  
Advertisements

CSOM – ContentType and CAML Queries

To retrieve the ContentType of a ListItem from the ListItemCollection using the GetItems method, there are two approaches that work.

  1. If you are not specifying ViewFields in your CAML query then in your Load method you need to Include the ContentType. e.g.

clientContext.Load(listItems, items => items.Include(item => item.ContentType));

  1. If you CAML query includes ViewFields then two modifications are required. The ViewFields needs to include the ContentTypeId field and the Load method needs to include the ContentType. e.g.
<ViewFields><FieldRef Name='ContentTypeId' /></ViewFields>

clientContext.Load(listItems, items => items.Include(item => item.ContentType));