LoadItemsByCamlQueryAsync not working with FolderServerRelativeUrl

[mortenfa]

Category

• [x] Bug

Describe the bug

When querying with LoadItemsByCamlQueryAsync on a semi large list (10000 items) querying a specific folder throws REST exception

Steps to reproduce

This is working

var viewXml = $@"<View Scope='RecursiveAll'><RowLimit Paged='TRUE'>{rowLimit}</RowLimit><Where><Eq><FieldRef Name='FSObjType' /><Value Type='Integer'>1</Value></Eq></Where><ViewFields><FieldRef Name='ID' /><FieldRef Name='FileRef' /></ViewFields></View>";

await sourceList.LoadItemsByCamlQueryAsync(new CamlQueryOptions() { ViewXml = viewXml, PagingInfo = nextPage }

This throws the item limit exception with the same viewXml

await sourceList.LoadItemsByCamlQueryAsync(new CamlQueryOptions() { ViewXml = viewXml, FolderServerRelativeUrl = sourceList.RootFolder.ServerRelativeUrl + "/Testfolder", PagingInfo = nextPage });

Expected behavior

Results

Environment details (development & target environment)

Visual Studio latest version

Additional context

Thanks for your contribution! Sharing is caring.

[jansenbe]

@mortenfa : can you try to include the folder filtering in your CAML query, see https://github.com/pnp/pnpcore/issues/839 for some inspiration on how to do so. There's also a limitation with the used SharePoint API on special chars in the provided folder, so in my opinion it's anyhow better to implement this at CAML level. See also the notes of the related docs: https://pnp.github.io/pnpcore/using-the-sdk/listitems-intro.html#c-getting-list-items-via-the-loaditemsbycamlquery-approach

[jansenbe]

@mortenfa : did you manage to get unblocked?

[mortenfa]

not yet, I'll take a look in the weekend 😊👍

[mortenfa]

@jansenbe : Still not working. Getting this quite unusable error:

"SharePoint Rest service exception"

"Cannot complete this action. Please try again."

with this rowLimit:500 :

`$@"

{rowLimit}

                            <ViewFields><FieldRef Name='FileDirRef' /><FieldRef Name='FileRef' /></ViewFields>
                            <Query>
                                <Where>
                                    <Or>
                                        <BeginsWith>
                                            <FieldRef Name='FileDirRef' />
                                            <Value Type='Text'>{sourceList.RootFolder.ServerRelativeUrl}/_Documents</Value>
                                        </BeginsWith>
                                        <BeginsWith>
                                            <FieldRef Name='FileDirRef' />
                                            <Value Type='Text'>{sourceList.RootFolder.ServerRelativeUrl}/_Emails</Value>
                                        </BeginsWith>
                                        <BeginsWith>
                                            <FieldRef Name='FileDirRef' />
                                            <Value Type='Text'>{sourceList.RootFolder.ServerRelativeUrl}/Formalia</Value>
                                        </BeginsWith>
                                    </Or>
                                </Where>
                            </Query>
                        </View>"`

[bajce]

@mortenfa did you try using LoadListDataAsStreamAsync (see #1044).

[mortenfa]

@bajce : yes, tried that also

[jansenbe]

@mortenfa : I just tried this myself and using below code I could filter out the 5000+ files that lived in a sub folder (named 001) of a 100K+ library using good performance. Maybe this can work for your scenario as well? In case the issue was already resolved for you then feel free to close this issue.

FYI: @bajce

                string listTitle = "Large";
                var myList = context.Web.Lists.GetByTitle(listTitle);

                string viewXml = @"<View Scope='RecursiveAll'>
                        <ViewFields>
                          <FieldRef Name='Title' />
                          <FieldRef Name='FileLeafRef' />
                          <FieldRef Name='FSObjType'/>
                          <FieldRef Name='FileDirRef'/>
                        </ViewFields>
                        <Query>
                            <Where>
                                <Eq>
                                    <FieldRef Name='FileDirRef'/>
                                    <Value Type='text'>/sites/prov-1/Large/001</Value>
                                </Eq>
                            </Where>
                        </Query>
                        <OrderBy Override='TRUE'><FieldRef Name='ID' Ascending='FALSE' /></OrderBy>
                        <RowLimit Paged='TRUE'>500</RowLimit>
                       </View>";

                // Load all the needed data using paged requests
                bool paging = true;
                string nextPage = null;
                int pages = 0;
                while (paging)
                {
                    var output = await myList.LoadListDataAsStreamAsync(new RenderListDataOptions
                    {
                        ViewXml = viewXml,
                        Paging = nextPage ?? null,
                    });

                    pages++;

                    if (output.ContainsKey("NextHref"))
                    {
                        nextPage = output["NextHref"].ToString().Substring(1);
                    }
                    else
                    {
                        paging = false;
                    }
                }

[jansenbe]

@mortenfa : closing this one, but feel free to re-open if needed.

[mortenfa]

Quite OK, I haven't had the time to check. I ended up with another approach.

Thanks