0

I have the following code inside my remote event reciever, the code will get all the folders and files under a specific folder:-

CamlQuery camlQuery6 = new CamlQuery();
   camlQuery6 = new CamlQuery();
   camlQuery6.ViewXml = "<View Scope=\"RecursiveAll\"><Query><Where><BeginsWith><FieldRef Name=\"FileDirRef\" /><Value Type=\"Text\">" + context.Web.ServerRelativeUrl + "/Library3/" + currentFilingSystemItem["DealName"].ToString() + "</Value></BeginsWith></Where></Query></View>";

   ListItemCollection collListItem6 = context.Web.GetList(context.Web.ServerRelativeUrl + "/Library3").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();

now the returned items will be around 150, but the library3 i am querying against contain more than 5500 items. so the above code is returing the folloiwng error:-

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

so can anyone advice how i can overcome this issue? by modifying the query or adding indexed? Thanks

  • Recommendation is to create index to overcome 5K threshold.sharepointmaven.com/… – Matiur Rahman Mar 13 at 15:49
  • @MatiurRahman yes i know but i can not create an index on FileDirRef – SharePoint TestDev Mar 13 at 15:51
  • @willman i already read this article, but the approach mentioned will not work if i am querying against a non-indexed field. and in my case i am querying against the FileDirRe which can not be indexed.. – SharePoint TestDev Mar 13 at 15:53
  • You don't need an index field in your Query specifically for that technique to work. The whole point of adding a <RowLimit> parameter to your CAML is to stay under the list view threshold. Then, you use ListItemCollectionPosition to retrieve the next chunk of items in a loop. One thing not mentioned in that article is that you have to reference an indexed column at least somewhere in your query, so if nothing else, add <OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy> -- but this only works around the problem if you are also using <RowLimit> – willman Mar 13 at 16:15
  • 1
    No, your <BeginsWith> query will still only get items that meet your criteria. The problem is, without an indexed column, SharePoint is trying to scan the entire list of 5500 items to see which ones meet your criteria, then failing due to the threshold. If you set a RowLimit, it will only search the first 5000 items for items that meet your criteria, and return those 150 or so items, problem is there may be items that meet your criteria in those last 500 items it didn't search, so you execute in a loop advancing ListItemCollectionPosition, which tells it to search the next chunk of items. – willman Mar 13 at 17:31
0

So, if you google for "SharePoint CSOM Large Lists", you will find plenty of examples -- but the gist is that in order to query a large list while staying within the ListViewThreshold, you have to set a RowLimit, so that SharePoint doesn't try to scan the entire List in fell swoop, it will only scan the number of items specified by RowLimit. But of course, in order for you to get all the correct results, you Do want it to eventually scan the whole list against your criteria, but if the list size exceeds the List View Threshold, then you have to do it in "chunks". You set a RowLimit in your CAML, but then keep track of the returned ListItemCollectionPosition, and then execute the query again in a loop, telling SharePoint to scan the next RowLimit of items, starting from the previously left off ListItemCollectionPosition.

So, the two main things to keep in mind, is to modify your CAML to limit the number of rows scanned, but use a do..while loop to keep checking the next chunk of rows until you have checked the whole list. The other caveat is that if you have no Indexed columns referenced anywhere in your query, then SharePoint has no way of keeping track of that Position, so the easiest thing to do is add <OrderBy><FieldRef Name='ID' /></OrderBy>.

The following should get you want you are looking for:

ClientContext clientContext = new ClientContext("weburl");
List list = clientContext.Web.Lists.GetByTitle("ListTitle");

List list = context.Web.GetList(context.Web.ServerRelativeUrl + "/Library3");
var foundListItems = new System.Collections.Generic.List<ListItem>();

camlQuery6 = new CamlQuery();
// The RowLimit tells SharePoint to only scan the next 1000 items
camlQuery6.ViewXml = "<View Scope=\"RecursiveAll\"><OrderBy><FieldRef Name='ID' /></OrderBy><RowLimit>1000</RowLimit><Query><Where><BeginsWith><FieldRef Name=\"FileDirRef\" /><Value Type=\"Text\">" + context.Web.ServerRelativeUrl + "/Library3/" + currentFilingSystemItem["DealName"].ToString() + "</Value></BeginsWith></Where></Query></View>";

do
{
    ListItemCollection collListItem6 = list.GetItems(camlQuery);
    context.Load(collListItem6, items => items.Include(
          item => item.Id,
          item => item["FileDirRef"],
          item => item["Title"],
          item => item["DealStage"],
          item => item["DealName"],
          item => item["Fund"]
        ));
    clientContext.ExecuteQuery();
    // this line grabs the position from where SharePoint left off, 
    // so we can pass that in as the starting position the next time through the loop
    camlQuery6.ListItemCollectionPosition = collListItem6.ListItemCollectionPosition;
    foreach (ListItem listItem in collListItem6) // collect the items we found in this chuck of searched items
    {
        foundListItems.Add(listItem);
    }
}
while(camlQuery6.ListItemCollectionPosition != null) // collListItem6.ListItemCollectionPosition will be set back to null once we reached the end of the list

//Now we can do something with all the items we found
foreach (var listItem in foundListItems )
{
    // do something with the listItem
}
| improve this answer | |
  • i tried your code but i got the same error "the attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator" – SharePoint TestDev Mar 13 at 19:12
  • Lets try an experiment, I know that there are certain operators that will never work in a 5000+ item list, even with RowLimit -- I thought the only two that wouldn't work were <Lte> and <Gte>, but let's see if the problem is with <BeginsWith>. See what happens if you change the CAML to "<View Scope=\"RecursiveAll\"><OrderBy><FieldRef Name='ID' /></OrderBy><RowLimit>1000</RowLimit><Query><Where><Eq><FieldRef Name=\"FileDirRef\" /><Value Type=\"Text\">" + context.Web.ServerRelativeUrl + "/Library3/" + currentFilingSystemItem["DealName"].ToString() + "</Value></Eq></Where></Query></View>" – willman Mar 13 at 19:38
  • i do not think i will be able to filter based on non0index columns... – SharePoint TestDev Mar 13 at 19:47
  • Based on your other question, I think I was setting ListItemCollectionPosition incorrectly. I just edited this answer, see if this works any better. – willman Mar 13 at 19:51

Not the answer you're looking for? Browse other questions tagged or ask your own question.