Sitecore 9.2 Query not returning all matching items on Azure production

Question

We have multiple environments for a website including local, UAT (single Azure instance), CM (Azure production for authoring) and CD (content delivery).

The following code is on all instances and feeds an API.

Context.Database.SelectItems("/sitecore/content/global/articles/descendant::*[@@templatename = 'Sample']").ToList();

For some reason it works as expected on local and UAT, but does not pull back all anticipated items on CM and CD.

As far as I can tell, the only difference is Redis cache. Azure search is being used for indexing, but I think that's a moot point since I believe standard queries hit the database instead of an index right?

Anything I may be overlooking related to Azure and Sitecore standard queries?

Answer 1

If on production environment you have much more items than on other environments, your issue may be caused by setting:

<setting name="Query.MaxItems" ...>

By default it's 100.

If your query may return more, you should adapt the setting to make sure its value is enough for you.

Answer 2

You will have to update your below entry in Sitecore.config to a larger value (may be 1000) using a patch.

<!--  Query.MaxItems
            Specifies the max number of items in a query result set.
            If the number is 0, all items are returned. This may affect system performance, if a
            large query result is returned.
            This also controls the number of items in Lookup, Multilist and Valuelookup fields.
            Default value: 100
      -->
    <setting name="Query.MaxItems" value="100" />`

This happens more often than expected and developers end up blaming their logics. Sitecore should make this error obvious to developers by pointing to the limitation in the configuration.