Return all files from DocumentLibrary (more than allowed admin limit - 5000)

Question

I have followed the msdn sample of how to use paging while retrieving items from a list. Here is my code:

    internal static void ReturnAllFiles(ClientContext context)
    {
        List list = context.Web.Lists.GetByTitle(Globals.DocLibName);
        context.Load(list);

        ListItemCollectionPosition itemPosition = null;

        while (true)
        {
            CamlQuery camlQuery = new CamlQuery();

            camlQuery.ListItemCollectionPosition = itemPosition;

            camlQuery.ViewXml = "<View><ViewFields>" +
                "<FieldRef Name='Title'/>" +
                "</ViewFields><RowLimit>5</RowLimit></View>";

            ListItemCollection collListItem = list.GetItems(camlQuery);

            context.Load(collListItem);

            context.ExecuteQuery();

            itemPosition = collListItem.ListItemCollectionPosition;

            foreach (ListItem oListItem in collListItem)
            {
                Console.WriteLine("Title: {0}: \nBody: {1}", oListItem["Title"], oListItem["Body"]);
            }

            if (itemPosition == null)
            {
                break;
            }

            Console.WriteLine("\n" + itemPosition.PagingInfo + "\n");
        }

However even though the <RowLimit> attribute is present in my CamlQuery, I still get the following error exception:

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

What am I doing wrong here?

Answer 1

After consulting with colleagues I have noticed that my CAMLQuery was incorrect. Here is the full working sample for those who might need it. Just adjust the fields you are interested in.

Final Code:

    static void LoopThroughFiles(ClientContext context)
    {
        List list = context.Web.Lists.GetByTitle(ConfigurationManager.AppSettings["DocLibName"]);

        ListItemCollectionPosition itemPosition = null;
        while (true)
        {
            CamlQuery camlQuery = new CamlQuery();

            camlQuery.ListItemCollectionPosition = itemPosition;

            camlQuery.ViewXml =
                "<View Scope='RecursiveAll'>" +
                    "<Query>" +
                        "<OrderBy Override='TRUE'>" +
                            "<FieldRef Name='ID' />" +
                        "</OrderBy>" +
                    "</Query>" +
                    "<ViewFields>" +
                        "<FieldRef Name='Title' />" +
                        "<FieldRef Name='Body' />" +
                    "</ViewFields>" +
                    "<QueryOptions>" +
                        "<QueryThrottleMode>Override</QueryThrottleMode>" +
                    "</QueryOptions>" +
                    "<RowLimit>3000</RowLimit>" +
                "</View>";

            ListItemCollection collListItem = list.GetItems(camlQuery);
            context.Load(collListItem);
            context.ExecuteQuery();

            itemPosition = collListItem.ListItemCollectionPosition;
            foreach (ListItem oListItem in collListItem)
            {
                Console.WriteLine("Title: {0}: \nBody: {1}", oListItem["Title"], oListItem["FileLeafRef"]);
            }

            if (itemPosition == null)
                break;

            Console.WriteLine("\n" + itemPosition.PagingInfo + "\n");
        }
    }

Answer 2

Try this:

camlQuery.ViewXml = "<View><Query></Query><ViewFields><FieldRef Name='Title' /></ViewFields><RowLimit>5</RowLimit></View>";

Answer 3

You could take two approaches:

1. Changing the threshold limit in the Central administration -> Application management -> Manage Web Application --> List View Threshold

(Not Recommended - But test to see if that works)

2. Add indexes to columns: List settings -> Indexed Columns -> Create a new index -> select column under Primary Column, Done!

Answer 4

Please note that, specifying the row limit does not affect list item threshold value. Your query has to be modified so that it only retrieves the items which are less than 5000.

Indexing has to be done on column on which you are filtering data, say suppose you are filtering your data based on Column1, column 1 has to be indexed prior to the list items reaches 5000.

Other alternative way is to by pass/set threshold value from central admin temporary, create required indexes and change the threshold value again to old one.

Hope this helps!!!