Why is my CAML query failing when it contains <Query>?

Question

I have a list with a lot of documents (> 5000). I need to retrieve one particular document from it. I use this query:

@"<View>
    <Query>
        <Where>
            <Contains>
                <FieldRef Name=""FileLeafRef""/>
                <Value Type=""Text"">MyDocumentName</Value>
            </Contains>
        </Where>
    </Query>
    <RowLimit>1</RowLimit>
</View>"

And I get a ServerException stating that this operation is forbidden because it goes over the list treshold.

But if I remove the <Query> tag:

@"<View>
    <RowLimit>1</RowLimit>
</View>"

It works, and retrieve a single file, which sadly isn't the one I wanted, obviously.

So I'm puzzled here. Why is it behaving like that?

Answer 1

After eventually finding the right keywords to ask Google, I found this question.

The problem is that no field in my library is indexed, therefore when I try to add a query with conditions, all the library has to be read through in order to find the document(s) I need. So obviously, if there is too many documents, I get the exception.

<RowLimit> doesn't prevent this behavior, it only acts on the result of the query.

That being said, there is one field that is automatically indexed: ID. So we can change our query a bit to manually page the documents whose ID are between two values, and simply put the query in a while loop, incrementing the current ID paging each time, like so:

ListItemCollection items = null;
const int paging = 2000;
int currentPaging = paging;

while (items == null || items.Count == 0 || currentPaging < 100000)
{
    var query = new CamlQuery
    {
        ViewXml = $"<View><Query><Where><And><Contains><FieldRef Name='FileLeafRef' /><Value Type='File'>MyDocumentName</Value></Contains><And><Leq><FieldRef Name='ID' /><Value Type='Counter'>{currentPaging}</Value></Leq><Gt><FieldRef Name='ID' /><Value Type='Counter'>{currentPaging - paging}</Value></Gt></And></And></Where></Query></View>"
    };

    items = list.GetItems(query);
    ctx.Load(items);
    ctx.ExecuteQuery();
    currentPaging += paging;
}

Here's the formatted XML used in the query (query.ViewXml), for better readability:

    <View>
    <Query>
        <Where>
            <And>
                <Contains>
                    <FieldRef Name='FileLeafRef' />
                    <Value Type='File'>MyDocumentName</Value>
                </Contains>
                <And>
                    <Leq>
                        <FieldRef Name='ID' />
                        <Value Type='Counter'>{currentPaging}</Value>
                    </Leq>
                    <Gt>
                        <FieldRef Name='ID' />
                        <Value Type='Counter'>{currentPaging - paging}</Value>
                    </Gt>
                </And>
            </And>
        </Where>
    </Query>
</View>