Get-PNPListItem query parameter ignores Paged attribute in RowLimit

[reusto]

Reporting an Issue or Missing Feature

Issue

Expected behavior

Using Get-PnPListItem with the query parameter should only return the specified number of items, if in the query the Paged attribute for RowLimit is set to false or not set.

Actual behavior

The command returns all list items in batches of the size of the specified number in RowLimit.

When printing the output from Get-PnPListItem directly to the shell you can see that the items are requested in batches with the size of the specified RowLimit.

Steps to reproduce behavior

Get a list with an item count bigger than the RowLimit you want to test with. Run the Get-PnPListItem command with a caml query which includes RowLimit.

$list = Get-PnPList "TestList"
$query = "<View><RowLimit>5</RowLimit><View>"
Get-PnPListItem -List $list -Query $query

Which version of the PnP-PowerShell Cmdlets are you using?

• [x] PnP PowerShell for SharePoint 2013
• [ ] PnP PowerShell for SharePoint 2016
• [ ] PnP PowerShell for SharePoint Online

What is the version of the Cmdlet module you are running?

2.15.1705

(Additionally tested version 2.14.1704 which works fine)

How did you install the PnP-PowerShell Cmdlets?

• [x] MSI Installed downloaded from GitHub
• [ ] Installed through the PowerShell Gallery with Install-Module
• [ ] Other means

[Justin-Nash]

I am experiencing the same issue with SharePoint Online, 2.15.17, MSI Installed

[berndverhofstadt]

Same for me using PnP PowerShell for SharePoint Online 2.16.1706.0 MSI Installed downloaded from GitHub

[DavidLozzi]

I saw the same, created PR #888 :)

[reusto]

Any news regarding this issue? I saw that there was an alternative to the PR #888 was introduced with the PR #1059 and now we should be able to limit the items with

Get-PnPListItem -List 'yourlist' | select -first 5 or Get-PnPListItem -List 'yourlist' | Select -Wait -First 5

~~But the first one breaks my code execution and the second one waits till all items are loaded which can be quiet anoying if you have to many items.~~

~~Therefor is there an alternative which doesn't load all items and doesn't breaks the code execution?~~

Okay have to work with | Foreach {$array.Add($_)} to write the output to a variable. Nevertheless I personaly would prefere it if the command would use the querys as they were intended and we would have the ability to request paging if we want to.

[SergeiAtML]

I am running into this issue constantly. Any update on this? using above workaround is not practical for a huge list (50K items), so I ended up using invoke rest method with list services API; it's messy but works for now.

[SpeedmaxX]

I am also running in this issue. How to get this fixed?

[mann-david]

PageSize and RowLimit don't appear to work at all. See #2143

[ravick4u]

I am also facing the same issue. Definitely an issue when you have list having lots of items.

[James-Noble]

Just to provide some clarity here, because very similar issues are being linked to this bug and the original description no longer provides the full story: if you want to retrieve some items from a large list (over 5000 items for SharePoint Online), there is no possible way to use the -Query parameter to Get-PnPListItem. This is distressing because the main reason you're probably using -Query is because there are a lot of items and you want them filtered server-side to avoid excessive network bandwidth etc.

If you use -Query and also specify -PageSize, and/or put <RowLimit> in the CAML query, it seems to have no effect and the server responds with "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator".

The only work around for this while still using PnP-PowerShell is to remove the -Query, get every item with -PageSize, then process the items locally with foreach/select/where-object. This is extremely slow and consumes extra memory and network bandwidth, to the point where I've ended up being throttled by SharePoint Online.

[reusto]

@James-Noble In my opinion the original description provides the full story in the same way the moment as it did back then when I created the issue. The issue is about only getting a specific number of items from a bigger number of possible results. (My usecase that made me aware of the issue back then for example was to get the top item)

The issues you are describe are the limits of CAML query in general which existed back then and continue to exist. If you do work with CAML queries make sure that the items you are working with are less or equal 5000. And if you are working with more than 5000 items make sure to define indexed columns beforehand, which help you to reduce the returned items to less or equal 5000.

Therefor if you have to work with more then 5000 items: first filter with indexed columns until you have less or euqal than 5000 items and then do the the rest (filtering and sorting) with not indexed columns if you have to.

[AndersRask]

Just to provide some clarity here, because very similar issues are being linked to this bug and the original description no longer provides the full story: if you want to retrieve some items from a large list (over 5000 items for SharePoint Online), there is no possible way to use the -Query parameter to Get-PnPListItem. This is distressing because the main reason you're probably using -Query is because there are a lot of items and you want them filtered server-side to avoid excessive network bandwidth etc.

If you use -Query and also specify -PageSize, and/or put <RowLimit> in the CAML query, it seems to have no effect and the server responds with "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator".

The only work around for this while still using PnP-PowerShell is to remove the -Query, get every item with -PageSize, then process the items locally with foreach/select/where-object. This is extremely slow and consumes extra memory and network bandwidth, to the point where I've ended up being throttled by SharePoint Online.

This is not correct! You can get more than 5000 items if you make sure that the first parameter in your CAML query returns less than 5000 items, so for example if you have a list of 25.000 items, and 2500 have an indexed property of say "Country" that is set to "Denmark"

[itsorch]

So its Dec 2020, its been 3 years since this was reported and at least 7 years since MS promised to lift the 5000 item nonsense from SharePoint. Honestly I really do not care what self imposed limitations there are in Sharepoint, this is ridiculous. you cannot expect any large enterprise to work with that kind of limit. I'm in a position where I cannot work around this at all, I have explored every option including loading all the data into pages and working it out in Powershell. The issue is then that in Azure runbooks, this completely falls over and dies. In my case I just wanted to lookup the first row item (i.e. with the lowest ID) in a list. But no because of this limit, and because page size and row limit are just broken. I have to run code that takes over an hour to complete. Just to return one integer! By the way row limit, even on a library with 50 items in it, will return everything, even if I set it to 1, I still get 50 items returned. If it doesnt work, why include it at all?

So anyway, long story short, I now have to go back to my project board and tell them the app we are working on cannot be done in the Microsoft Cloud.

[LasseChristensen]

@itsorch : This is a limitation in the PNP framework there are other options to solve this.

1. Use the SharePoint REST service to get the first item in a list (by default the items are sorted by ID, which is already an indexed column): /_api/web/lists/GetByTitle('LISTNAME')/items?$select=Id&$top=1'

2. Use CSOM to get the item with the lowest ID in a list: $caml = "<View Scope='RecursiveAll'><Query><OrderBy><FieldRef Name='ID' Ascending='TRUE' /></OrderBy></Query><RowLimit Paged='False'>1</RowLimit></View>"; $camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery $camlQuery.ViewXml = $caml $firstItem = $list.GetItems($camlQuery) $ctx.Load($firstItem ); $ctx.ExecuteQuery() return $firstItem[0].Id;

[itsorch]

Hi @LasseChristensen Unfortunately, I did try both of those and I got the same results, either it returns too many items, or it doesn't go looking in the folder specified, works from the document library, just not from a folder in one. I have put this project on hold for now as there are many other issues I have with it. I think as well the biggest issue I have is that the rowlimit field simply doesn't work I did quite a lot of personal testing and I couldn't get it to respond correctly, at all!

[simon-stealthbits]

Regarding the lists with 5000 items issue.

I ran into the issue with threshold errors when querying SPO lists which contain more than 5000 items with regular CSOM code and resolved it by tweaking the content of the view XML of the CAML query and setting additional properties on the CamlQuery object.

The following are the tweaks which were required to get a query which worked against SharePoint On-Prem to work for SharePoint Online:

1. The view should include an OrderBy clause which sorts by the ID column with Ascending set to TRUE
2. The RowLimit element should have a Paged property set to TRUE,
3. Note: The booleans in the view XML should be in all caps, SPO seemed pickier than on-prem,
4. The AllowIncrementalResults property of the CamlQuery object should be set to true.

e.g. (assuming a variable PreviousListItemCollection is the ListItemCollection object from the previous page, or null)

Microsoft.SharePoint.Client.CamlQuery query = new Microsoft.SharePoint.Client.CamlQuery()
{
    ViewXml = @"<View>
            <Query><OrderBy><FieldRef Name="ID" Ascending="TRUE"/></OrderBy></Query>
            <RowLimit Paged='TRUE'>1000</RowLimit>
        </View>",
    ListItemCollectionPosition = PreviousListItemCollection?.ListItemCollectionPosition,
    AllowIncrementalResults = true
};