I have code that scans a document library holding a list of InfoPath forms. It uses CSOM with a caml query to get the specific forms it needs to work with. I have had this code running for years with no issues. Today, I started receiving the error "The attempted operation is prohibited because it exceeds the list view threshold."
This is SharePoint Online and I know the list view threshold to be 5000. We do have more than 5000 items in the list and have for some time. The results I'm trying to retrieve should be bringing back about 10 forms; well under the 5000 item threshold. Both of the fields I query with are indexed.
ListItemCollection spList = clientContext.Web.Lists ?.GetByTitle(libName) ?.GetItems(new CamlQuery { ViewXml = "<View><Query><Where><And><Eq><FieldRef Name=\"Field1\"/><Value Type=\"Text\">No</Value></Eq><Eq><FieldRef Name=\"Field2\"/><Value Type=\"Text\">No</Value></Eq></And></Where></Query></View>" }); clientContext.Load(spList); clientContext.Load(spList, items => items.Include( item => item.ContentType, item => item.DisplayName, item => item.ContentType.Fields, item => item.Folder, item => item.Folder.Files, item => item.File.ListItemAllFields, item => item.FileSystemObjectType, item => item.File, item => item.File.Author, item => item.File.ServerRelativeUrl )); clientContext.SendRequest();When testing the issue, I found that I can retrieve an item by ID
"<View><Query><Where><Eq><FieldRef Name='ID' /><Value Type='Number'>16134</Value></Eq></Where></Query></View>"However, when trying to use Or and include two IDs, I get the error again.
"<View><Query><Where><Or>" + "<Eq><FieldRef Name='ID' /><Value Type='Number'>6001</Value></Eq>" + "<Eq><FieldRef Name='ID' /><Value Type='Number'>6002</Value></Eq>" + "</Or></Where></Query></View>"It seems the threshold is now behaving as if it is one. I'm not sure if that is due to how I'm querying the data and for some reason all results are being returned or if there is something internally wrong.
My first thought is that something is up on Microsoft's side, but I see no mention in the health center or anywhere else.
Is there anything known that could be causing this and any suggestions on how to correct the issue? Possible a recent change or depreciation from MS?
This post will show you one of the ways to loop the folder with hitting the error “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator”.
The pre-requisite for this article is that 1. You need to know SharePoint. @. You need to know C#. #. You need to know CSOM.
In the Internet many posts showing to loop through the SharePoint folder you do:”
Ctx.Load(folderItems, icol => icol.include(I => i.Folder).
The below is the code that facing the error while getting all the folders in a Document Library.
Error: “Microsoft.SharePoint.Client.ServerException
HResult=0x80131500
Message=The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.”
To loop files and folders one by one you might need to use the code below. You need more details on SharePoint SignIn goes here //chanmingman.wordpress.com/2019/12/07/ssis-sharepoint-data-destination/.
static void Main(string[] args)
{
DateTime startTime = DateTime.Now;
string siteUrl = “//yoursharepoint.sharepoint.com/sites/yoursite/”;
ClientContext clientContext = new ClientContext(siteUrl);
SecureString securePassword = Login.GetPassword();
clientContext.Credentials = new SharePointOnlineCredentials(“”, securePassword);
Web rootWeb = clientContext.Web;
SP.List oList = clientContext.Web.Lists.GetByTitle(@”Documents”);
FolderCollection folders = oList.RootFolder.Folders;
clientContext.Load(folders);
clientContext.ExecuteQuery();
FileCollection fileCol = oList.RootFolder.Files;
clientContext.Load(fileCol);
clientContext.ExecuteQuery();
foreach (SP.File file in fileCol)
{
Console.WriteLine(“Serverd url”);
Console.WriteLine(file.ServerRelativeUrl);
}
foreach (Folder folderr in folders)
{
Console.WriteLine(folderr.Name);
GetFolder(folderr, clientContext);
}
DateTime stopTime = DateTime.Now;
TimeSpan elapsed = stopTime.Subtract(startTime);
Console.WriteLine(elapsed.TotalSeconds.ToString(“0.000000”));
}
public static void GetFolder(Folder folder, ClientContext clientContext)
{
Console.WriteLine(“GetFolder Folder”);
Console.WriteLine(folder.Name);
if (folder.Name == “TopFolder”)
{
int int1 = 0;
}
if (folder.Name == “general”)
{
int int1 = 0;
}
FolderCollection foldersL1 = folder.Folders;
clientContext.Load(foldersL1);
clientContext.ExecuteQuery();
FileCollection fileCol = folder.Files;
clientContext.Load(fileCol);
clientContext.ExecuteQuery();
foreach (SP.File file in fileCol)
{
Console.WriteLine(“Serverd url”);
Console.WriteLine(file.ServerRelativeUrl);
}
foreach (Folder folderr1 in foldersL1)
{
if (folderr1.Name == “general”)
{
int int1 = 0;
}
Console.WriteLine(folderr1.Name);
if (foldersL1.Count() > 0)
{
Console.WriteLine(“folderr1 name”);
Console.WriteLine(folderr1.Name);
GetFolderL2(folderr1, clientContext);
}
}
}
The code above does have limitation. I am able to run a Document Library more less than 30000 items. If the items are more then you might hit the 429 error from SharePoint.