Query list having more than 5K items in it

Question

I need to fetch items from a list which has around 20000 items in it. Using caml query i can only fetch 5000 items. What approach should i follow using a server side code.

Answer 1

Use content iterator and read my answer here

private void GetItems()
{
    SPList list = SPContext.Current.Web.Lists["My list"];

    SPQuery query = new SPQuery();
    query.Query = "<Where></Where>";
    query.ViewFields = "<FieldRef Name='Field1'/>";
    query.ViewFields = "<FieldRef Name='Field2'/>";

    ContentIterator iterator = new ContentIterator();
    iterator.ProcessListItems(list,
        query,
        ProcessItem,
        ProcessError
    );
}

public bool ProcessError(SPListItem item, Exception e)
{
    //process error
}

public void ProcessItem(SPListItem item)
{
    //process SPListItem
}

Answer 2

<Query>
   <Where>
      <And>
          <And>
            <Gt><FieldRef Name='ID'></FieldRef><Value Type='Number'>0</Value></Gt>
            <Lt><FieldRef Name='ID'></FieldRef><Value Type='Number'>5000</Value></Lt>
          </And>
          <Eq>
            <FieldRef Name='IndexedCol' />
            <Value Type='Text'>match6k</Value>
          </Eq>
        </And>
   </Where>
    <View>  
        <OrderBy>
            <FieldRef Name='ID' Ascending='True' />
        </OrderBy>
        <RowLimit>60</RowLimit>
    </View>
</Query>

In this code, the first part of the query narrows the result set down to items with ID between 0 and 5000. This prevents any possibility of exceeding the list view threshold. Then, it filters those into the items where IndexedCol = match6k. Finally, the RowLimit ensures that only 60 of the items are returned. You need to simply repeat the query until you have finished querying all the items and reached the list's Max ID.

For more information please follow this link,

https://www.codeproject.com/Articles/1076854/Overcoming-the-List-View-Threshold-in-SharePoint-C