Share via

CAML query to get all the files and folders inside a sub-folder without getting threshold error

Question

Friday, March 13, 2020 4:53 PM

I have a document library which have 6,000 items, and i want to write a CAML query to get all the Files & Folders which are under specific folder. i tired those 2 CAMLs:-

camlQuery6.ViewXml ="<View Scope =\"Recursive\"><Query></Query></View>" ;
camlQuery6.FolderServerRelativeUrl = context.Web.ServerRelativeUrl + "/ArchDocs/" + currentFilingSystemItem["DealName"].ToString();
ListItemCollection collListItem6 = context.Web.GetList(context.Web.ServerRelativeUrl + "/ArchDocs").GetItems(camlQuery6);
context.Load(collListItem6, items => items.Include(
                                    item => item.Id,
                                    item => item["FileDirRef"],
                                    item => item["Title"],
                                    item => item["DealStage"],
                                    item => item["DealName"],
                                    item => item["Fund"]
                                    ));
                                        context.ExecuteQuery();

and this CAML query:-

camlQuery6.ViewXml ="<View Scope=\"RecursiveAll\"><Query><Where><BeginsWith><FieldRef Name=\"FileDirRef\" /><Value Type=\"Text\">" + context.Web.ServerRelativeUrl + "/ArchDocs/" + currentFilingSystemItem["DealName"].ToString() + "</Value></BeginsWith></Where></Query></View>";
                                    
ListItemCollection collListItem6 = context.Web.GetList(context.Web.ServerRelativeUrl + "/ArchDocs").GetItems(camlQuery6);
context.Load(collListItem6, items => items.Include(
                                    item => item.Id,
                                    item => item["FileDirRef"],
                                    item => item["Title"],
                                    item => item["DealStage"],
                                    item => item["DealName"],
                                    item => item["Fund"]
                                    ));
                                        context.ExecuteQuery();

but on both CAML queries i got this error:-

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

any advice?

All replies (5)

Monday, March 16, 2020 8:45 AM

Hi John,

Please use RowLimit in Caml Query and use ListItemCollectionPosition to handle List View Threshold issue:

            using (ClientContext ctx = new ClientContext("https://Tenant.sharepoint.com/sites/dev"))
            {
                List list = ctx.Web.Lists.GetByTitle("CamlList");
                ListItemCollectionPosition position = null;
                var page = 1;

                do
                {
                    CamlQuery camlQuery = new CamlQuery();
                    camlQuery.ViewXml = @"<View Scope='Recursive'><Query></Query><RowLimit>5000</RowLimit></View>";
                    camlQuery.ListItemCollectionPosition = position;
                    camlQuery.FolderServerRelativeUrl ="/sites/dev/CamlList/";
                    var collListItem = list.GetItems(camlQuery);
                    ctx.Load(collListItem, listitems => listitems.Include(
                        item => item.Id, item => item["Title"],item => item["FileDirRef"])
                        , listitems => listitems.ListItemCollectionPosition);
                    ctx.ExecuteQuery();

                    position = collListItem.ListItemCollectionPosition;
                    foreach (var listItem in collListItem)
                    {
                        Console.WriteLine(listItem["Title"]);
                    }

                    page++;
                }
                while (position != null);
            }

Here is a blog about details of this solution, please refer:

How To Execute CAML Query In Large SharePoint Lists Using CAML To Avoid The Exception – The Attempted Operation Is Prohibited Because It Exceeds The List View Threshold Enforced By The Administrator – SharePoint Office 365

Thanks

Best Regards

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

SharePoint Server 2019 has been released, you can click

here to download it.
Click

here to learn new features. Visit the dedicated

forum to share, explore and talk to experts about SharePoint Server 2019.

Thursday, March 19, 2020 2:34 PM

Hi John,

Please use RowLimit in Caml Query and use ListItemCollectionPosition to handle List View Threshold issue:

            using (ClientContext ctx = new ClientContext("https://Tenant.sharepoint.com/sites/dev"))
            {
                List list = ctx.Web.Lists.GetByTitle("CamlList");
                ListItemCollectionPosition position = null;
                var page = 1;

                do
                {
                    CamlQuery camlQuery = new CamlQuery();
                    camlQuery.ViewXml = @"<View Scope='Recursive'><Query></Query><RowLimit>5000</RowLimit></View>";
                    camlQuery.ListItemCollectionPosition = position;
                    camlQuery.FolderServerRelativeUrl ="/sites/dev/CamlList/";
                    var collListItem = list.GetItems(camlQuery);
                    ctx.Load(collListItem, listitems => listitems.Include(
                        item => item.Id, item => item["Title"],item => item["FileDirRef"])
                        , listitems => listitems.ListItemCollectionPosition);
                    ctx.ExecuteQuery();

                    position = collListItem.ListItemCollectionPosition;
                    foreach (var listItem in collListItem)
                    {
                        Console.WriteLine(listItem["Title"]);
                    }

                    page++;
                }
                while (position != null);
            }

Here is a blog about details of this solution, please refer:

How To Execute CAML Query In Large SharePoint Lists Using CAML To Avoid The Exception – The Attempted Operation Is Prohibited Because It Exceeds The List View Threshold Enforced By The Administrator – SharePoint Office 365

Thanks

Best Regards

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

SharePoint Server 2019 has been released, you can click here to download it.
Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

in my case the "

camlQuery.FolderServerRelativeUrl ="***";

did not have any effect and all the records from the list were returned.. not sure why. any advice?

Friday, March 20, 2020 2:42 AM

Hi John,

Please use RowLimit in Caml Query and use ListItemCollectionPosition to handle List View Threshold issue:

            using (ClientContext ctx = new ClientContext("https://Tenant.sharepoint.com/sites/dev"))
            {
                List list = ctx.Web.Lists.GetByTitle("CamlList");
                ListItemCollectionPosition position = null;
                var page = 1;

                do
                {
                    CamlQuery camlQuery = new CamlQuery();
                    camlQuery.ViewXml = @"<View Scope='Recursive'><Query></Query><RowLimit>5000</RowLimit></View>";
                    camlQuery.ListItemCollectionPosition = position;
                    camlQuery.FolderServerRelativeUrl ="/sites/dev/CamlList/";
                    var collListItem = list.GetItems(camlQuery);
                    ctx.Load(collListItem, listitems => listitems.Include(
                        item => item.Id, item => item["Title"],item => item["FileDirRef"])
                        , listitems => listitems.ListItemCollectionPosition);
                    ctx.ExecuteQuery();

                    position = collListItem.ListItemCollectionPosition;
                    foreach (var listItem in collListItem)
                    {
                        Console.WriteLine(listItem["Title"]);
                    }

                    page++;
                }
                while (position != null);
            }

Here is a blog about details of this solution, please refer:

How To Execute CAML Query In Large SharePoint Lists Using CAML To Avoid The Exception – The Attempted Operation Is Prohibited Because It Exceeds The List View Threshold Enforced By The Administrator – SharePoint Office 365

Thanks

Best Regards

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

SharePoint Server 2019 has been released, you can click here to download it.
Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

in my case the "

camlQuery.FolderServerRelativeUrl ="***";

did not have any effect and all the records from the list were returned.. not sure why. any advice?

CamlQuery.FolderServerRelativeUrl needs to set with a sub folder relative url like this:

camlQuery.FolderServerRelativeUrl = "/sites/dev/Shared%20Documents/2019";

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

SharePoint Server 2019 has been released, you can click

here to download it.
Click

here to learn new features. Visit the dedicated

forum to share, explore and talk to experts about SharePoint Server 2019.

Saturday, March 21, 2020 2:10 AM

Hi John,

Please use RowLimit in Caml Query and use ListItemCollectionPosition to handle List View Threshold issue:

            using (ClientContext ctx = new ClientContext("https://Tenant.sharepoint.com/sites/dev"))
            {
                List list = ctx.Web.Lists.GetByTitle("CamlList");
                ListItemCollectionPosition position = null;
                var page = 1;

                do
                {
                    CamlQuery camlQuery = new CamlQuery();
                    camlQuery.ViewXml = @"<View Scope='Recursive'><Query></Query><RowLimit>5000</RowLimit></View>";
                    camlQuery.ListItemCollectionPosition = position;
                    camlQuery.FolderServerRelativeUrl ="/sites/dev/CamlList/";
                    var collListItem = list.GetItems(camlQuery);
                    ctx.Load(collListItem, listitems => listitems.Include(
                        item => item.Id, item => item["Title"],item => item["FileDirRef"])
                        , listitems => listitems.ListItemCollectionPosition);
                    ctx.ExecuteQuery();

                    position = collListItem.ListItemCollectionPosition;
                    foreach (var listItem in collListItem)
                    {
                        Console.WriteLine(listItem["Title"]);
                    }

                    page++;
                }
                while (position != null);
            }

Here is a blog about details of this solution, please refer:

How To Execute CAML Query In Large SharePoint Lists Using CAML To Avoid The Exception – The Attempted Operation Is Prohibited Because It Exceeds The List View Threshold Enforced By The Administrator – SharePoint Office 365

Thanks

Best Regards

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

SharePoint Server 2019 has been released, you can click here to download it.
Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

in my case the "

camlQuery.FolderServerRelativeUrl ="***";

did not have any effect and all the records from the list were returned.. not sure why. any advice?

CamlQuery.FolderServerRelativeUrl needs to set with a sub folder relative url like this:

camlQuery.FolderServerRelativeUrl = "/sites/dev/Shared%20Documents/2019";

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

SharePoint Server 2019 has been released, you can click here to download it.
Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

now i tried the following code:-

 ListItemCollectionPosition position = null;
 do
{
      CamlQuery camlQuery6 = new CamlQuery();
      camlQuery6.ViewXml = @"<View Scope='Recursive'><Query></Query><RowLimit>5000</RowLimit></View>";//string.Format("<View Scope=\"RecursiveAll\"><Query><Where><Neq><FieldRef Name='ID' /><Value Type='Text'>{0}</Value></Neq></Where></Query><RowLimit>{1}</RowLimit></View>", "0", "3000");
      camlQuery6.ListItemCollectionPosition = position;
      camlQuery6.FolderServerRelativeUrl = context.Web.ServerRelativeUrl + "/ArchDocs/" + currentFilingSystemItem["DealName"].ToString();
                                            
      ListItemCollection collListItem6 = context.Web.GetList(context.Web.ServerRelativeUrl + "/ArchDocs").GetItems(camlQuery6);
      context.Load(collListItem6, items => items.Include(
                                                  item => item.Id,
                                                  item => item["FileDirRef"],
                                                  item => item["Title"],
                                                   ), items => items.ListItemCollectionPosition);
      context.ExecuteQuery();
      position = collListItem6.ListItemCollectionPosition;

              foreach (ListItem listItem in collListItem6) // collect the items we found in this chuck of searched items
                  {
                       foundListItems.Add(listItem);
                  }
}
while (position != null);
foreach (ListItem item in foundListItems)
                  {
                                           
                      //code goes here.,,;
                       item.Update();
                       context.ExecuteQuery();

                  }

but if the list contain more than 5,000 items i will get this error:-

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

so seems your code will still raise an error if the list has more than 5,000 items

Monday, March 23, 2020 12:54 AM

Hi John,

Please use RowLimit in Caml Query and use ListItemCollectionPosition to handle List View Threshold issue:

            using (ClientContext ctx = new ClientContext("https://Tenant.sharepoint.com/sites/dev"))
            {
                List list = ctx.Web.Lists.GetByTitle("CamlList");
                ListItemCollectionPosition position = null;
                var page = 1;

                do
                {
                    CamlQuery camlQuery = new CamlQuery();
                    camlQuery.ViewXml = @"<View Scope='Recursive'><Query></Query><RowLimit>5000</RowLimit></View>";
                    camlQuery.ListItemCollectionPosition = position;
                    camlQuery.FolderServerRelativeUrl ="/sites/dev/CamlList/";
                    var collListItem = list.GetItems(camlQuery);
                    ctx.Load(collListItem, listitems => listitems.Include(
                        item => item.Id, item => item["Title"],item => item["FileDirRef"])
                        , listitems => listitems.ListItemCollectionPosition);
                    ctx.ExecuteQuery();

                    position = collListItem.ListItemCollectionPosition;
                    foreach (var listItem in collListItem)
                    {
                        Console.WriteLine(listItem["Title"]);
                    }

                    page++;
                }
                while (position != null);
            }

Here is a blog about details of this solution, please refer:

How To Execute CAML Query In Large SharePoint Lists Using CAML To Avoid The Exception – The Attempted Operation Is Prohibited Because It Exceeds The List View Threshold Enforced By The Administrator – SharePoint Office 365

Thanks

Best Regards

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

SharePoint Server 2019 has been released, you can click here to download it.
Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

in my case the "

camlQuery.FolderServerRelativeUrl ="***";

did not have any effect and all the records from the list were returned.. not sure why. any advice?

CamlQuery.FolderServerRelativeUrl needs to set with a sub folder relative url like this:

camlQuery.FolderServerRelativeUrl = "/sites/dev/Shared%20Documents/2019";

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

SharePoint Server 2019 has been released, you can click here to download it.
Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

now i tried the following code:-

 ListItemCollectionPosition position = null;
 do
{
      CamlQuery camlQuery6 = new CamlQuery();
      camlQuery6.ViewXml = @"<View Scope='Recursive'><Query></Query><RowLimit>5000</RowLimit></View>";//string.Format("<View Scope=\"RecursiveAll\"><Query><Where><Neq><FieldRef Name='ID' /><Value Type='Text'>{0}</Value></Neq></Where></Query><RowLimit>{1}</RowLimit></View>", "0", "3000");
      camlQuery6.ListItemCollectionPosition = position;
      camlQuery6.FolderServerRelativeUrl = context.Web.ServerRelativeUrl + "/ArchDocs/" + currentFilingSystemItem["DealName"].ToString();
                                            
      ListItemCollection collListItem6 = context.Web.GetList(context.Web.ServerRelativeUrl + "/ArchDocs").GetItems(camlQuery6);
      context.Load(collListItem6, items => items.Include(
                                                  item => item.Id,
                                                  item => item["FileDirRef"],
                                                  item => item["Title"],
                                                   ), items => items.ListItemCollectionPosition);
      context.ExecuteQuery();
      position = collListItem6.ListItemCollectionPosition;

              foreach (ListItem listItem in collListItem6) // collect the items we found in this chuck of searched items
                  {
                       foundListItems.Add(listItem);
                  }
}
while (position != null);
foreach (ListItem item in foundListItems)
                  {
                                           
                      //code goes here.,,;
                       item.Update();
                       context.ExecuteQuery();

                  }

but if the list contain more than 5,000 items i will get this error:-

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

so seems your code will still raise an error if the list has more than 5,000 items

Hi John,

I will test with a list more than 5000 items with the code snippet in my post, with ListItemCollectionPosition object.

It will fix the List View Threshold issue. 

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

SharePoint Server 2019 has been released, you can click

here to download it.
Click

here to learn new features. Visit the dedicated

forum to share, explore and talk to experts about SharePoint Server 2019.

  • Last updated on