I've reached the maximum view threshold limit on a SharePoint List, with a little more than 5000 item in it.
When I raise the limit in the webapp's settings, everything is working fine, but I would like to avoid doing that, as it will ultimately affect performance.
In order to do that, I tried to set a RowLimit value (of 100 items) to my CAML Query. This limit is working, but the where clause of my query is being ignored, thus returning me the first 100 items of the list.
If I add an OrderBy clause, it gets ignored aswell.
Here is my code :
SPList list = web.Lists[new Guid("xxxx")];
SPQuery query = new SPQuery();
query.RowLimit = 100;
// My previous query, which is ignoring the RowLimit statement and is triggering the limit threshold error.
// query.Query = "<Where><Eq><FieldRef Name=\"Author\" /><Value Type=\"Integer\"><UserID /></Value></Eq></Where>";
// The current query, working with rowlimit, ignoring everything inside the view tag
query.Query = "<View Scope='RecursiveAll'><Where><Eq><FieldRef Name=\"Author\" /><Value Type=\"Integer\"><UserID /></Value></Eq></Where></View>";
SPListItemCollection listItemsCollection = list.GetItems(query);
I've tried a lot of things, for example putting the rowlimit statement as a tag inside the query, or adding a query tag inside the view tag, but nothing seems to work.
How am I supposed to use a RowLimit and a Where clause at the same time ? If that's not possible, what's the best approach with my threshold limit issue ?