CAML query not able to filter data retrieved from SharePoint

Question

I have been working on a SharePoint Online System which stores data related to various products in different document libraries and lists. In this SharePoint Online System there is a requirement of implementing an API which will allow users to download folders into a zip from the libraries present in the site. The API code works well with the libraries which have items less than 5000, but it gives us an error of "list threshold" when the number of item is higher than 5000.

On further investigation I was able to get past the error by traversing the whole list with the help of a CAML query then use LINKQ to filter out item. But as there are over 80,000 items in the list it takes a long time. To resolve this I made changes in the CAML Query so that I can get only the items that are required from SharePoint rather than applying the LINKQ. Even after adding condition in CAML system simply ignores the condition and returns all the list items as it was doing before.

This is the code I am working with,

class Program
{
static void Main(string[] args)
{
List<ListItem> items = new List<ListItem>();
string subFolders = "Test-Test_123";
using (ClientContext ctx = new ClientContext("https://domain.sharepoint.com/"))
{
string password = "********";
string account = "[email protected]";
var secret = new SecureString();
foreach (char c in password)
{
secret.AppendChar(c);
}
ctx.Credentials = new SharePointOnlineCredentials(account, secret);

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

List list = web.Lists.GetByTitle("product");
ctx.Load(list);
ctx.ExecuteQuery();

CamlQuery camlQuery = new CamlQuery();

camlQuery.ViewXml = "<View Scope='Recursive'><Where><BeginsWith><FieldRef Name='Test'/></BeginsWith></Where><RowLimit>5000</RowLimit></View>";
//Filter path or Name begins with Test
do
{
ListItemCollection listItems = list.GetItems(camlQuery);
ctx.Load(listItems);
ctx.ExecuteQuery();

items.AddRange(listItems);
camlQuery.ListItemCollectionPosition = listItems.ListItemCollectionPosition;

} while (camlQuery.ListItemCollectionPosition != null);
// Returns all records 
var filteritems = items.Where(tt => tt.FieldValues["FileRef"].ToString().StartsWith("path to the folder/file to filter out the required items"));
foreach (ListItem item in filteritems)
{
Console.WriteLine(item.FieldValues["FileRef"]);
}

}
}
}

Can anyone suggest any way we can overcome this problem or are there any work-arounds to get past this behavior.

Thank you in advance.

Answer 1

If I'm not mistaken, it looks like your CAML query is not formed quite right. You use the <FieldRef /> element to specify which field you want to look at, but you also need to specify a <Value /> element to specify what value you are looking for.

In your query you have <FieldRef Name="Test" />, which is saying to SharePoint: "look at the field whose internal name is Test". But your comment

// Filter path or Name begins with Test

seems to indicate that you are trying to look for something with the value of "Test".

I believe you really want to construct your query more like

<BeginsWith>
    <FieldRef Name="FieldInternalName" />
    <Value Type="Text">Test</Value>
</BeginsWith>

Here is the MS documentation on the <BeginsWith /> CAML element.

Answer 2

This is a known issue called "list view threshold error". Check below blog to see more details:

• https://natechamberlain.com/2018/05/21/how-to-resolve-sharepoint-list-view-threshold-error-the-view-cannot-be-displayed-because-it-exceeds-the-list-view-threshold/

For SharePoint online, we cannot change the threshold settings as what we can do in SP on-premise. The suggested option is to Index the filter column or fix the caml string as @Dylan Cristy suggested.

BR