How to query large sharepoint list

Question

I have a list that contains tens of thousands of items.

I have tried to limit the query using a where clause, like this:

var list = Context.Web.Lists.GetByTitle(title);

string query = $@"<Query>
<Where>
    <Lt>
        <FieldRef Name='ID' /><Value Type='Integer'>100</Value>
    </Lt>
</Where>
<OrderBy><FieldRef Name='ID' Ascending='true' /></OrderBy>
</Query>";

var camlQuery = new CamlQuery();
camlQuery.ViewXml = query;

var items = list.GetItems(camlQuery);
Context.Load(items);
Context.ExecuteQuery();

But I'm getting SPQueryThrottledException anyway:

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

How do I query large sp list?

Answer 1

You need to modify your CAML query and use pagination using ListItemCollectionPosition as well as set the RowLimit value.

Try and modify the below sample code:

var list = Context.Web.Lists.GetByTitle(title);
ListItemCollectionPosition position = null;

try
{
    do
    {
        string query = $@"<View> 
            <ViewFields>
                <FieldRef Name='Title'/>
            </ViewFields>       
            <Query>
                <Where>
                    <Lt>
                        <FieldRef Name='ID' /><Value Type='Integer'>100</Value>
                    </Lt>
                </Where>
            <OrderBy><FieldRef Name='ID' /></OrderBy>
            </Query><RowLimit>100</RowLimit></View>";

        var camlQuery = new CamlQuery();
        camlQuery.ViewXml = query;


        var items = list.GetItems(camlQuery);
        Context.Load(items);
        Context.ExecuteQuery();
        position = items.ListItemCollectionPosition;

        foreach (ListItem listItem in items)
            Console.WriteLine("Title: {0}", listItem["Title"]);
    }
    while (position != null);

}
catch(Exception ex)
{

}

Updated CAML query as per comments:

string query = $@"<View>                            
<Query>
    <Where>
        <And>
            <Geq>
                <FieldRef Name='ID' /><Value Type='Integer'>1</Value>
            </Geq>
            <Leq>
                <FieldRef Name='ID' /><Value Type='Integer'>1000</Value>
            </Leq>                                        
        </And>
    </Where>
<OrderBy><FieldRef Name='Created' /></OrderBy>
</Query><RowLimit>100</RowLimit></View>";