When trying to get the last item in a SharePoint list, I "The attempted operation is prohibited because it exceeds the list view threshold"

Question

Dear SharePoint Developer Community:

I am trying to make the following Sharepoint csom request:

using (ClientContext context = new ClientContext("https://my.sharepoint.com/sites/my_cool_site/")) {
    SecureString securePassword = new SecureString();
    foreach (char character in "")
    {
        securePassword.AppendChar(character);
    }
    context.Credentials = new SharePointOnlineCredentials("my_username", securePassword);
        var lib = context.Web.Lists.GetByTitle("MyList");
        CamlQuery query = new CamlQuery();
    query.ViewXml = "<View><Query><OrderBy><FieldRef Name = 'ID' Ascending = 'FALSE'/></OrderBy></Query><ViewFields><FieldRef Name = 'ID'/></ViewFields><RowLimit>1</RowLimit></View>";
        ListItemCollectionPosition itemPosition = null;
    do
    {
        ListItemCollection itemCollection = null;
        itemCollection = lib.GetItems(query);
        context.Load(itemCollection);
        try
        {
            context.ExecuteQuery();            
        }catch(Exception exec)                    {
            Console.WriteLine(exec.ToString());
        }
    }
    while (itemPosition != null);
}
Console.ReadLine();

But when I do this, I get an error:

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

Being that I requested only 1 record, why did I get this error? It does not happen on all lists, just one of them in particular.

Answer 1

Hi @nicholas dipiazza ,

I tested the same CAML in a custom list which have 5300 items and it's working:

    using (var context = new ClientContext("https://zheguo.sharepoint.com/sites/dev/"))  
    {  
        context.Credentials = new Microsoft.SharePoint.Client.SharePointOnlineCredentials(userName, securePassword);  
        Web web = context.Web;  
        context.Load(web);  
        context.ExecuteQuery();  

        var list = context.Web.Lists.GetByTitle("CamlList");  
        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>";  
        ListItemCollectionPosition itemPosition = null;  
        do  
        {  
            ListItemCollection itemCollection = null;  
            itemCollection = list.GetItems(camlQuery);  
            context.Load(itemCollection);  
            try  
            {  
                context.ExecuteQuery();  
                Console.WriteLine(itemCollection[0].FieldValues["ID"]);  
            }  
            catch (Exception exec)  
            {  
                Console.WriteLine(exec.ToString());  
            }  
        }  
        while (itemPosition != null);  

    }  
    Console.ReadLine();  

The ID column is indexed column by default, so the caml to order by ID column should be working.

I suggest you can try again to see if it works and as the error only happen for a specific list, I suggest you can check if there is some special for the list.

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Yeah removing the <ViewFields><FieldRef Name='ID' /></ViewFields> worked.

<View Scope='RecursiveAll'><Query><OrderBy><FieldRef Name ='ID' Ascending='FALSE'/></OrderBy></Query><RowLimit Paged ='TRUE'>1</RowLimit></View>

worked. Removing the id from the fields list causes this to work. This seems like a bug. How do you open a ticket with SP online?