I am following <a href="https://social.msdn.microsoft.com/Forums/sharepoint/en-US/db54d1c7-e19b-414d-84b9-d6c22ea3b676/how-can-i-overcome-limitations-of-list-view-threshold-when-querying-sharepoint-2013-online?referrer=https://social.msdn.microsoft.com/Forums/sharepoint/en-US/db54d1c7-e19b-414d-84b9-d6c22ea3b676/how-can-i-overcome-limitations-of-list-view-threshold-when-querying-sharepoint-2013-online?referrer=https://social.msdn.microsoft.com/Forums/sharepoint/en-US/db54d1c7-e19b-414d-84b9-d6c22ea3b676/how-can-i-overcome-limitations-of-list-view-threshold-when-querying-sharepoint-2013-online?referrer=https://social.msdn.microsoft.com/Forums/sharepoint/en-US/db54d1c7-e19b-414d-84b9-d6c22ea3b676/how-can-i-overcome-limitations-of-list-view-threshold-when-querying-sharepoint-2013-online?forum=appsforsharepoint" rel="ugc nofollow">this thred But I am getting "The attempted operation is prohibited because it exceeds the list view threshold". Basically, I want to get the latest 10 modified documents from specific folder of the document library. So I am using below CAML viewXML camlQuery.ViewXml = @"<View Scope='Recursive'><Query><OrderBy Override='TRUE'><FieldRef Name='Modified' Ascending='False'/></OrderBy></Query><ViewFields><FieldRef Name='Title' /><FieldRef Name='Modified' /><FieldRef Name='FileDirRef' /><FieldRef Name='FileLeafRef' /></ViewFields><RowLimit Paged='TRUE'>10</RowLimit></View>"; camlQuery.FolderServerRelativeUrl = "/clients/account/Account_Demo"; As soon as I specify "Modified" in Order by - this quey throws an error of list view threshold. Just to share Modified is indexed column If I remove <FieldRef Name='Modified' Ascending='False'/> from Order By then query works fine but does not serve the purpose of the latest 10.

Hi @Tarak Dave , Per my test, The ViewFields tag should be in Query tag. Try following CamlQuery: camlQuery.ViewXml = "<View Scope='Recursive'><Query><OrderBy Override='TRUE'><FieldRef Name='Modified' Ascending='False'/></OrderBy><ViewFields><FieldRef Name='Title' /><FieldRef Name='Modified' /><FieldRef Name='FileDirRef' /><FieldRef Name='FileLeafRef' /></ViewFields></Query><RowLimit Paged='TRUE'>10</RowLimit></View>"; If an Answer is helpful, please click " Accept Answer " and upvote it. Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

You can set the list view thresholds on Central Admin. Navigate to url: http://server-name:port/_admin/WebApplicationList.aspx Click on General Settings, select Resource Throttling.

SharePoint Online --- Document library with 10k items --- Get latest 10(recent 10 modified from specific folder)

Accepted answer

RaytheonXie_MSFT 31,526 Reputation points Microsoft Vendor

2021-08-24T07:28:59.337+00:00
Hi @Tarak Dave ,
Per my test, The ViewFields tag should be in Query tag. Try following CamlQuery:

camlQuery.ViewXml = "<View Scope='Recursive'><Query><OrderBy Override='TRUE'><FieldRef Name='Modified' Ascending='False'/></OrderBy><ViewFields><FieldRef Name='Title' /><FieldRef Name='Modified' /><FieldRef Name='FileDirRef' /><FieldRef Name='FileLeafRef' /></ViewFields></Query><RowLimit Paged='TRUE'>10</RowLimit></View>";

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
Tarak Dave 21 Reputation points

2021-08-24T13:07:10.207+00:00

hi @RaytheonXie_MSFT

I tried above, but no luck - the error did not change. The query works fine when less than 5k items.
My issue is I have more then 10k. If you execute above query with 10k, with camlQuery.FolderServerRelativeUrl provided, you might face issue :)

RaytheonXie_MSFT 31,526 Reputation points Microsoft Vendor

2021-08-25T06:34:05.5+00:00

Hi @Tarak Dave ,
Per my test, I found we can set folder via FileDirRef property and remove camlQuery.FolderServerRelativeUrl.

camlQuery.ViewXml = @"<View Scope='Recursive'><Query><OrderBy Override='TRUE'><FieldRef Name='Modified' Ascending='False'/></OrderBy><where><Eq><FieldRef Name='FileDirRef' /><Value Type='File'>/TestDoc/Test</Value></Eq></where></Query><RowLimit Paged='TRUE'>10</RowLimit></View>"; ListItemCollection listItems = oList.GetItems(camlQuery);

The similar issue for refer:
https://stackoverflow.com/questions/36137351/calmquery-rowlimit-not-working

Tarak Dave 21 Reputation points

2021-08-25T07:44:52.21+00:00

Hi @RaytheonXie_MSFT

Still no luck. I tried both options. As we discussed, these queries works fine when we have less than 5k items but the issue only occurs when we have more.

just to summarize issue, or replicating it,

Document library with more that 5k+ items(increased threshold limit)

Query folder specific URL - to get recent 10 documents(modified desc) within entire folder hierarchie - <View Scope='Recursive'> to get only files.

RaytheonXie_MSFT 31,526 Reputation points Microsoft Vendor

2021-08-25T07:57:47.27+00:00

Hi @Tarak Dave ,
I have tested following code in a folder with more than 10k txt files and it works.Please try following code if it doesn't work delete all index and create new index for Modified.

SP.List oList = clientContext.Web.Lists.GetByTitle("TestDoc"); CamlQuery camlQuery = new CamlQuery(); camlQuery.ViewXml = @"<View Scope='Recursive'><Query><OrderBy Override='TRUE'><FieldRef Name='Modified' Ascending='False'/></OrderBy><where><Eq><FieldRef Name='FileDirRef' /><Value Type='File'>/TestDoc/Test</Value></Eq></where></Query><RowLimit Paged='TRUE'>10</RowLimit></View>"; //camlQuery.FolderServerRelativeUrl = folder.ServerRelativeUrl; ListItemCollection listItems = oList.GetItems(camlQuery); clientContext.Load(listItems); clientContext.ExecuteQuery(); foreach (ListItem oListItem in listItems) { Console.WriteLine("ID: {0} \nTitle: {1} \nBody: {2}", oListItem.Id, oListItem["Title"], oListItem["Modified"]); } Console.ReadLine();

RaytheonXie_MSFT 31,526 Reputation points Microsoft Vendor

2021-08-31T02:46:12.057+00:00

Hi @Tarak Dave ,
I am checking to see how things are going there on this issue.

Tarak Dave 21 Reputation points

2021-08-31T11:44:15.187+00:00

Hi @RaytheonXie_MSFT

Sorry for replying late.

I tried all odds but it seems <Eq><FieldRef Name='FileDirRef' /><Value Type='File'>/TestDoc/Test</Value></Eq> - does not seem to considered.

CAML seems to ignore folder specific path and returns the latest 10 documents from the entire document library and not from the folder we specified.

I triedl all options like <Value Type='File'> or <Value Type='Folder'> or <Value Type='Text'> along with value relative to document library or server relative url too, like /client/doclib/folder1 or /doclib/folder1 or even /folder1 or just folder1, but neither worked.

Tarak Dave 21 Reputation points

2021-08-31T14:38:02.38+00:00

Ok at least I see why folder specific is not considered, reason is <where> is in small case letters so I changed it to <Where></Where>to make my CAML like below.
camlQuery.ViewXml = @"<View Scope='Recursive'><Query><OrderBy Override='TRUE'><FieldRef Name='Modified' Ascending='False'/></OrderBy><Where><Eq><FieldRef Name='FileDirRef' /><Value Type='Text'>/sites/accounts/Account Demo</Value></Eq></Where></Query><RowLimit Paged='TRUE'>10</RowLimit></View>";

So by this, I again started getting The attempted operation is prohibited because it exceeds the list view threshold.'

RaytheonXie_MSFT 31,526 Reputation points Microsoft Vendor

2021-09-02T05:00:47.88+00:00

Hi @Tarak Dave ,
Per my test,"Scope='RecursiveAll'" can't work with camlQuery.FolderServerRelativeUrl.Since the quantity is above 5000. We can only query all files in the Library to a list.Then filter the top 10 files which FileDirRef = "/sites/accounts/Account Demo"

Tarak Dave 21 Reputation points

2021-09-02T07:02:08.55+00:00

Hi @RaytheonXie_MSFT

I understand to get all files from library then apply a filter, this is what we have at the moment but this takes more than 30 seconds to 1 minute to retrieve all files(10k+) then apply filter and then sort - All these takes time as you believe.

We have structure as below.
sites/accounts/Account Demo[x....] e.g Account 1, Account 2..... Account X
and we need to get the latest 10 files from specific accounts e.g Account 568. All these Account[x] are folders at the root level.

so finally now we can conclude that we can't use folder-specific URL with recursive and orderby filters.

I am now thinking one of the 2 ways

Split all accounts to separate document library

Creating a custom list to store the latest 10 files per "Account", indexed with "AccountTitle", run a MS flow event when "Accounts" document library modified to get the latest document information from each account, store in custom list. This custom list holds all other information about actual documents e.g Item ID.

RaytheonXie_MSFT 31,526 Reputation points Microsoft Vendor

2021-09-02T07:15:05.033+00:00

Hi @Tarak Dave ,
I will suggest you to split all accounts to separate document library if it is feasible. I have tested

camlQuery.ViewXml = "<View Scope='Recursive'><Query><OrderBy Override='TRUE'><FieldRef Name='Modified' Ascending='False'/></OrderBy><ViewFields><FieldRef Name='Title' /><FieldRef Name='Modified' /><FieldRef Name='FileDirRef' /><FieldRef Name='FileLeafRef' /></ViewFields></Query><RowLimit Paged='TRUE'>10</RowLimit></View>";

And it works well in the Document library with 10k files.

Tarak Dave 21 Reputation points

2021-09-02T07:23:43.167+00:00

hi @RaytheonXie_MSFT

Yes, that will work because we don't have a folder-specific URL but not serving our purpose.
But that's fine, we can conclude this discussion here with one of the options from the 2 we are taking about. :)

RaytheonXie_MSFT 31,526 Reputation points Microsoft Vendor

2021-09-03T09:27:57.537+00:00

Hi @Tarak Dave ,
Glad that you found the solution to this problem. Thanks for your sharing.

You could accept your answer via the "Accept Answer" button, it would be helpful to others who have similar issue in the future.
Sign in to comment

SharePoint Online --- Document library with 10k items --- Get latest 10(recent 10 modified from specific folder)

1 additional answer