RenderListDataAsStream SharePoint REST API
Microsoft.SharePoint.SPQueryThrottledException: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator. Every SharePoint dev faced this issue at least once or multiple times. It’s a well-known ‘issue/limitation’ when working with larger data sets in SharePoint lists. Some customers might be asking for that ‘contains search behaviour’ which is not supported in a SharePoint search query. In this blog post I’m going to try and find a solution for most filtering issues / scenarios in large SharePoint lists. I’ll compare different methods to check which are limited in which cases. You will also find a few tips & tricks, for whenever you want to filter large lists, to avoid the threshold limitations. In one of my next blog posts, we’ll also check how SharePoint is handling this limitation in its modern list UI and what will work / what will break whenever we hit the list threshold of 5000 items. On a side note: this does not mean that my methods / tips & tricks are the best solution for you. These are just a few solutions that I thought of and used in the past to solve different large list query scenarios. If you have any suggestions or want to add some corrections to my blog, please leave them in the comments. I will discuss 3 possible methods of filtering large lists in SharePoint Online. For both CSOM and REST there are options to query all items. Which is of course always an option if you are sure that there will be enough time to do this. By using the ‘RowLimit’ property in a CAML Query we can limit our results. This option makes use of a ‘paging’ principle which is adjusting the ListItemCollectionPosition to return the next set of 5000 items until we reach the ‘end’ of the list. When querying a document library, make sure you use the Scope=’RecursiveAll’ attribute in the View element. This way you will query all folders, subfolders and items that are nested in your document library. This attribute has no impact on a normal list without folders. Always make use of the ViewFields element to limit your return values. This will significantly improve query performance. var list = ctx.Web.Lists.GetByTitle("listName"); CamlQuery camlQuery = new CamlQuery(); camlQuery.ViewXml = @"We can do the same with REST using the $top query parameter in our request URI. We’ll get all items first and then do the filtering in memory. Once you add $top=5000&paged=true to your REST request. You’ll get the first 5000 items with a ‘link’ element that has an href attribute. This attribute contains the URL to get the next page of results. There is a very simple way to implement this thanks to the PnPjs library. Click here to see a sample on how you could implement this option with just 1 line of code using the getAll() function. Make sure you define your $select parameter when doing this… there is a huge performance improvement and is almost necessary for such large queries. Looking at the browsers network inspector, we’ll see these kind of requests passing by when using the getAll() function of PnPjs. After completing this getAll() function we can of course just filter in memory, just as we did in C#. No limitations to column types for filtering. But might be a slow solution again if you are talking about a list with for example 50000+ items. Method 1 – Pros
Method 1 – Cons
I guess the methods below in CSOM and REST API are the most used methods, but this will point out that they are clearly not the ‘best’ methods in all scenarios. Especially not for big lists. We have a lot of different ways of filtering our data with the CAML Query. Using all the operators available in CAML, we can do a lot of stuff… Contains, In, Eq, Geq, Leq, … Things might get pretty frustrating once we are using these on large lists. Once a list reaches 5000+ items it will always return the following error when doing any CAML query. I’ll just throw in an example here which is not working for me at the moment. var list = ctx.Web.Lists.GetByTitle("Big data list"); var camlQuery = new CamlQuery(); camlQuery.ViewXml = @"Here we run into our favourite exception again… Microsoft.SharePoint.Client.ServerException: ‘The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.’ So why does this still happen now, even if we have set the RowLimit to 5000? It did work in method 1 for the CSOM CAML Query. After applying a filter (Where element) in our CAML query, it will look at this filter first before applying the RowLimit. Which is causing our error here, because our list does contain more than 5000 items. So how could we fix this? We could apply an index to the field(s) which we are trying to filter. In case you don’t know how to do this, check the 2 screenshots below where you can find the indexed fields of a list in the list settings page. Now you’ve entered the page where all the indexed columns of your list are listed. Notice that there is a maximum of 20 indexes / list. After applying an index to the ‘Title’ field which I was filtering on in my sample, I did get the result I was looking for. My query ran successfully now. Note: Only the following column types can be indexed.
Any other column types than the ones mentioned above (most of the time these are columns which can contain a lot of data) cannot be indexed at this time. We are not finished yet with this method, because there might still raise an error, even when indexing is applied. If there are still more than 5000 results after applying your first filter in your Where element. It will keep crashing on the ‘list view threshold‘ error. Even if our filtered column is properly indexed. So there is no way to filter if you are not a 100% sure that your first filter is not going to limit your result set to 5000 or less items. We’ll have a solution for this issue in method 3. We can do the same as above in REST with or without the use of a CAML query. Every SharePoint developer probably knows the {sitecollectionURL}/_api/web/lists/GetByTitle(‘listname’)/Items endpoint We can easily leverage this one through PnPjs also. You can check some samples here on the official documentation, explaining how to filter in the REST API with PnPjs. import { sp } from "@pnp/sp/presets/all"; export default class TestSPRestCall extends React.ComponentAs for limitations to large list querying we are somewhat at the same level as in CSOM for this method. But this one has one more limiting factor, namely the fact that it can not filter by CAML query. This means that for example filtering on a managed metadata field etc. is not possible. Although there is a getItemsByCAMLQuery (‘getItems’ API endpoint) function in PnPjs which also enables the exact same functionality for filtering in CAML as Option 2 in CSOM does. With the exact same limitations of your first filter parameter having to limit the result set to 5000 items or less, else it will throw the same list view threshold exceeded error. import { sp } from "@pnp/sp/presets/all"; export default class TestSPRestCall extends React.ComponentSo indexing is again important if you are using this one in large lists. Check the part above, to get more info on field indexing. Method 2 – Pros
Method 2 – Cons
Method 2 – Tip(s)
In my opinion this is the most flexible way to query/filter your large SharePoint lists. But how is it different from the previous option that we just covered? This API endpoint simply removes the requirement of your first filter parameter in your Where element or in your $filter parameter to limit the result set to 5000 items or less. For some reason this API is not limited to the view threshold. This means that after applying my first filter (if indexed) the result might contain more than 5000 results. It doesn’t matter for this endpoint, it will return your results just fine. Let’s show some samples in SPFx and CSOM. I’ll include paging in the sample, so you know you could also page through the records, just as SharePoint does whenever you are scrolling down in modern list views. RESTBelow a sample in SPFx with the use of PnPjs. import { sp } from "@pnp/sp/presets/all"; export default class TestSPRestCall extends React.ComponentMake sure you are using an OrderBy element whenever you use this RenderListDataAsStream endpoint. As a default OrderBy value, I always use ID in Ascending order. This is also what SharePoint is doing, whenever using the RenderListDataAsStream endpoint if no other Order field is specified by the user. If you want to OrderBy any other column, this is possible, but the column should be indexed before the OrderBy will work on lists with more than 5000 items. It’s important that we also understand the limitations of this API endpoint, because SharePoint is using this endpoint a lot in it’s own modern UI. Almost all list data shown in SharePoint itself is queried through this endpoint. There is actually only one thing still missing here and that’s the fact that it still needs indexing to filter / order properly in large lists. So this means that we can not filter / order properly on multi person fields, multi lookup fields, multi taxonomy fields, … . This might still be a blocker for your scenario. You could be creative with a Note field (multi line text field) and convert those multi user/taxonomy/lookup fields into JSON. After that start using the SharePoint Search API to query and filter these fields also. But the conclusion is that it requires a pretty big workaround and involves some custom work to get those fields, that can’t be indexed, filtered properly. Method 3 – Pros
Method 3 – Cons
I think we can conclude that the RenderListDataAsStream (method 3) endpoint is the most flexible one, not only when speaking about filtering capabilities. But also when it comes to returning more details for certain field types without the need of expanding. You might need to consider this if you really need the flexibility of this endpoint. Because in CSOM it’s not always the most ‘handy’ endpoint to work with as it returns raw JSON. Instead of a typed C# object as you are used to with the normal list GetItems function. In one of my next blogs I’ll go into detail on the standard modern list UI in SharePoint Online. This blog will go more in depth on 2 specific points:
For example creating a ListView Command Set which displays the total amount of list items after a user has applied some filters on a view. |