CSOM C# CAML Query results different based on Count of List?

Question

Hello all,

I have a caml query I am using to return a list of files from SharePoint online...

if the list is over 5000 files, I get the correct results. If it is under 5000, I get files and folders...

<View><Query><OrderBy Override ="TRUE"><FieldRef Name="FileDirRef" /><FieldRef Name="FileLeafRef" /></OrderBy></Query><RowLimit Paged="TRUE">{0}</RowLimit></View>

if this list is under 5000 documents I get the correct results, if It is over 5000 I get "Exceeded Threshold".

<View><Query><Where><IsNotNull><FieldRef Name='File_x0020_Type' /></IsNotNull></Where><OrderBy Override ="TRUE"><FieldRef Name="FileDirRef" /><FieldRef Name="FileLeafRef" /></OrderBy></Query><RowLimit Paged="TRUE">{0}</RowLimit></View>

this is what I run once the CQ is loaded

            Web web = ccx.Web;
            ccx.Load(web);
            ccx.Load(web.Lists);
            ccx.Load(web, wb => wb.ServerRelativeUrl);
            ccx.ExecuteQuery();

            List list = web.Lists.GetByTitle(this.DocumentList);
            ccx.Load(list);
            ccx.ExecuteQuery();

            Folder folder = web.GetFolderByServerRelativeUrl(web.ServerRelativeUrl + Path);
            ccx.Load(folder);
            ccx.ExecuteQuery();

            camlQuery.FolderServerRelativeUrl = folder.ServerRelativeUrl;
            listCol = list.GetItems(camlQuery);
            
            ccx.Load(listCol, files => files.Include(file => file.Versions, file => file.FieldValuesAsHtml), files => files.ListItemCollectionPosition);
            ccx.ExecuteQuery();

Essentially, I just want a list of files and I want to use it in one method call.. any help would be appreciated...

Answer 1

Hi @Thomas Tierney

At any circumstances we cannot retrieve more than 5000 items from SharePoint list, so we must use the rowlimit parameter in the camel query, as I see you have already used it. If we have a filter in camlquery, the rowlimit parameter will not work and you will get exceed threshold limit exception. What we can do is to fetch all items iclude folders by first camlquery

<View><Query><OrderBy Override ="TRUE"><FieldRef Name="FileDirRef" /><FieldRef Name="FileLeafRef" /></OrderBy></Query><RowLimit Paged="TRUE">{0}</RowLimit></View>

Then keep it in collection object and filter files by following code

foreach (ListItem item in listCol){
   if (item.FileSystemObjectType == FileSystemObjectType.File){
         ListItems.Add(items);
      }
}

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.