2

I have a very large library in a SharePoint Online environment: > 3000 (document sets) in the root folder and more than 7000 (document sets, folders and documents) in total. The system is mainly based on custom code: C# CSOM.

Performance wise, the system would benefit greatly from querying on subsets of the whole data set, rather on the whole 7000 items every time (my present solution loads batches of 5000 items in a ListItemCollection: in my case first 5000 and then 2000, and then querying is done by LINQ) especially bearing in mind that certain operations are directly tied to specific views.

I am trying the below implementation as found in a comment on another thread.

Microsoft.SharePoint.Client.List list = ctx.Web.Lists.GetByTitle(ListName);
ctx.Load(list);
ctx.ExecuteQuery();

Microsoft.SharePoint.Client.View view = list.Views.GetByTitle(viewName);
ctx.Load(view);
ctx.ExecuteQuery();

CamlQuery query = new CamlQuery();
query.ViewXml = view.ViewQuery;

ListItemCollection items = list.GetItems(query);
ctx.Load(items);
ctx.ExecuteQuery();

My issue is that the ListItemCollection is being populated with all the items on the root collection (3000), when the chosen views tried consist of at most 60 items.

The view.ViewQuery is populated with this value:

"<GroupBy Collapse=\"FALSE\" GroupLimit=\"30\"><FieldRef Name=\"CurrentStatus\" /></GroupBy><OrderBy><FieldRef Name=\"CurrentStatus\" Ascending=\"FALSE\" /><FieldRef Name=\"Stage\" /></OrderBy><Where><And><Eq><FieldRef Name=\"Stage\" /><Value Type=\"Text\">Confirm Container</Value></Eq><Eq><FieldRef Name=\"PastTransition\" /><Value Type=\"Boolean\">0</Value></Eq></And></Where>"

Is the above code supposed to work? If yes, what's wrong? If not, what's another solution that achieves the same thing?

  • What does the ViewQuery look like? – Jim Barntish Oct 3 '17 at 17:43
1

Are you pulling CAML from the view because users might update the details of the filter? If not, teeing up the query manually is probably the better way to go.

Give this a try:

query.ViewXml ="<View Scope=\"Recursive\"><Query><Where><And><Eq><FieldRef Name=\"Stage\" /><Value Type=\"Text\">Confirm Container</Value></Eq><Eq><FieldRef Name=\"PastTransition\" /><Value Type=\"Boolean\">0</Value></Eq></And></Where></Query></View>"

Because your list is > 7k items, you would also need to make sure that one or both of the Stage and PastTransition columns are indexed. Might depend on which one returns the smaller subset of data and in what order they are evaluated.

You may also need to do "RecursiveAll" instead of "Recursive", depending on if you want/need to return folders. View scope enumeration: https://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spviewscope.aspx

This should get you the filtered list. From there, your code can do any grouping/ordering on the returned items.

Update

Since query details need to be puled dynamically from existing views (per your feedback below), you can pull the contents of the out of the existing view.ViewQuery and inject it into the view XML above. This will get rid of and also allow the query to run on lists > 5k.

See Jurgen's code for a fully baked solution.

  • It works like this. Does it mean that setting up a CAML query and filtering on indexed columns with a queryable data set of more than 5000 items, will not crash if the returned result is less than 5000? This is a work around but not actually a solution. Is there no way to get it from the View? I would like to have the query dynamically created from several views and not have to recreate the view configuration in CAML query each time, if that is possible, of course. This is a very good start already though! So thanks a lot :) – Jurgen Cuschieri Oct 4 '17 at 21:52
  • @JurgenCuschieri: It should work fine if there is more than 5K list items. Not sure I'm fully understanding your use case, but if you want to pull the filter details out of the view (or views) dynamically, you could probably do that by ripping the <Query> portion of the view's CAML and injecting it dynamically into <View Scrope="Recusive"></View>. – Jim Barntish Oct 4 '17 at 23:49
  • I'll try that and revert back to you. To make it a bit clearer, I have a system in place with a lot of different queries. CAML query is used to load batches of 5000 items into a List Item Collection. LINQ is used to query the resulting ListItemCollection. Needless to say, the operation takes a long time to return a result. Every (or almost every) scenario is theoretically tied to a view. meaning that the queryable data set can be minimised to the same view results. so if the caml query can be automatically populated from the view's query automatically - makes more sense, no? – Jurgen Cuschieri Oct 5 '17 at 0:03
  • the views define particular stages within a client company's business process. those CAML query operations that are used specifically within a stage, can therefore use the same query as the view. This will refine the queryable data set, rather than use all the items in the list every time – Jurgen Cuschieri Oct 5 '17 at 0:11
  • 1
    Great! Glad it worked out. Us SharePoint devs have to stick together ... :) – Jim Barntish Oct 5 '17 at 15:40
0

The below is my final solution, based on @Jim Barntish's answer.

Microsoft.SharePoint.Client.List list = ctx.Web.Lists.GetByTitle(ListName);
ctx.Load(list);
ctx.ExecuteQuery();

Microsoft.SharePoint.Client.View view = list.Views.GetByTitle(viewName);
ctx.Load(view);
ctx.ExecuteQuery();

CamlQuery query = new CamlQuery();
query.ViewXml = view.ViewQuery;

int pFrom = query.ViewXml.IndexOf("<Where>") + "<Where>".Length;
int pTo = query.ViewXml.LastIndexOf("</Where>");

string whereClause = query.ViewXml.Substring(pFrom, pTo - pFrom);

string queryStr = "<View Scope=\"Recursive\"><Query><Where>" + whereClause + "</Where></Query></View>";

query.ViewXml = queryStr;

ListItemCollection items = list.GetItems(query);
ctx.Load(items);
ctx.ExecuteQuery();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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