Threshold error inside our CSOM code

Question

I have the following code inside a .net console application, to get 4999 items from a custom list and add them inside a list named customerLiveOrderAllItems to implement paging, as follow:

var omsWeb = context.Site.OpenWeb("HRManagement");
context.Load(omsWeb);
context.ExecuteQuery();
var customerLiveOrderAllItems = new System.Collections.Generic.List<ListItem>();

List customerliveorderlist = omsWeb.GetList(omsWeb.ServerRelativeUrl + "/lists/CustomerLiveOrder/");
CamlQuery query1 = new CamlQuery();
string.Format("<View Scope=\"RecursiveAll\"><Query><Where><Eq><FieldRef Name ='CustomerOrderAlert'/><Value Type='Choice'>No</Value></Eq></Where></Query><RowLimit>{1}</RowLimit></View>", "0", "4999");
ListItemCollectionPosition position = null;
                do
                {
                    context.Load(context.Web, item => item.ServerRelativeUrl);
                    context.ExecuteQuery();
                    ListItemCollection collListItem10 = omsWeb.GetList(omsWeb.ServerRelativeUrl + "/lists/CustomerLiveOrder").GetItems(query1);
                    context.Load(collListItem10,
                            items => items.Include(
                            item => item.Id,
                            item => item["OrderLiveDeliveredDate"],
                            item => item["CustomerOrderAlert"]
                 ),items => items.ListItemCollectionPosition);
                    context.ExecuteQuery();/// this will raise the exception

                    query1.ListItemCollectionPosition = collListItem10.ListItemCollectionPosition;

                    foreach (ListItem listItem in collListItem10) /
                    {
                        customerLiveOrderAllItems.Add(listItem);
                    }

                }

                while (query1.ListItemCollectionPosition != null);

Currently the list have around 11,000 records. but when the run the code, I will get this error:

List exceeds threshold limit

on context.ExecuteQuery();. So why is this happening even though I define the row limit to be 4999. Any advice?

The only thing which worked for me is to remove any filtering inside the CAML, as follow:-

string.Format("<View Scope=\"RecursiveAll\"><RowLimit>{1}</RowLimit></View>", "0", "4999");

although in my case the CustomerOrderAlert column is indexed.. so what is going on ?

Thanks

Answer 1

Hi @john john ,

According to my research and testing, please try to change the CAML code and check if the error exists:

       var camlQuery = new CamlQuery();  
        camlQuery.ViewXml = @"<View Scope='RecursiveAll'>  
            <RowLimit Paged='TRUE'>4999</RowLimit></View>";  

As a workaround, you can use the following code to get list items from more than 5000 Items:

public static List<ListItem> GetAllListItemsInaList1()  
{  
    List<ListItem> items = new List<ListItem>();  
    string sitrUrl = "https://spotenant.sharepoint.com/sites/yoursite";  
    using (var ctx = new ClientContext(sitrUrl))  
    {  
        //ctx.Credentials = Your Credentials  
        ctx.Load(ctx.Web, a => a.Lists);  
        ctx.ExecuteQuery();  
   
        List list = ctx.Web.Lists.GetByTitle("Documents");  
        ListItemCollectionPosition position = null;  
        // Page Size: 50  
        int rowLimit = 50;  
        var camlQuery = new CamlQuery();  
        camlQuery.ViewXml = @"<View Scope='RecursiveAll'>  
            <Query>  
                <OrderBy Override='TRUE'><FieldRef Name='ID'/></OrderBy>  
            </Query>  
            <ViewFields>  
                <FieldRef Name='Title'/><FieldRef Name='Modified' /><FieldRef Name='Editor' />  
            </ViewFields>  
            <RowLimit Paged='TRUE'>" + rowLimit + "</RowLimit></View>";  
        do  
        {  
            ListItemCollection listItems = null;  
            camlQuery.ListItemCollectionPosition = position;  
            listItems = list.GetItems(camlQuery);  
            ctx.Load(listItems);  
            ctx.ExecuteQuery();  
            position = listItems.ListItemCollectionPosition;  
            items.AddRange(listItems.ToList());  
        }  
        while (position != null);  
    }             
    return items;  
}  

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.

---