Index queries not working with DateTime and ID on SXA and PAAS

Question

I had some custom code performing queries on a Sitecore index that worked perfectly on my local environment with Solr. But when deploying the code to Azure PAAS with Azure search they don't work.

The queries are rather simple and contain (amongst others, just listing the troubled predicates here):

• check a date field to be not older than 30 days
• check if the item is of a certain template (comparing the templateID)
• check if the item is a a certain base template (using the inheritance index field)

We are seeing several issues in the logs:

• the inheritance field apparently cannot be used in a filter on Azure
• our queries get "This_Is_Equal_ConstNode_Return_Nothing" in the search part
• filter on ID is not working (seems that the ID is in uppercase, should be lowercase)
• filter on date is not working ("Specified cast is not valid" error)

I did/tried:

• patch the inheritance field to be filterable (this fixed that issue)
• verify if the same code works on the same Sitecore version (9.0.2) without SXA - it does..
• verify if changing my DateTime to DateTimeOffset would help - it doesn't

Any idea what's wrong?

Answer 1

After some research I found out that:

• to work with the inheritance field in the index, I should indeed patch that on Azure as it is filterable in Solr but not in the Cloud
• the problems with ID's were indeed related to the cast: they get converted to uppercase guids instead of lowercase ones which does make a difference on Azure Search
• the problems with Dates were also related to conversion issues...
• and the conversion issues are related to SXA (1.7.1)

The problem is located in:

<indexFieldStorageValueFormatter patch:instead="*[@type='Sitecore.ContentSearch.Azure.Converters.CloudIndexFieldStorageValueFormatter, Sitecore.ContentSearch.Azure']" type="Sitecore.XA.Foundation.Search.Providers.Azure.CloudIndexFieldStorageValueFormatter, Sitecore.XA.Foundation.Search.Providers.Azure">

in Sitecore.XA.Foundation.Search.Azure.config. Apparently SXA is overriding some converting stuff - when I disabled their patch my queries starting working (and my search controls from SXA itself also still worked). So that was a temporary fix.

With this information I contacted Sitecore Support and they managed to get me a patch that fixes the issues. So, if you encounter these issues with your queries in a SXA setup with Azure Search you can contact support and mention bug number 250035.