issue while filtering on dynamic string property with tolower() function - Microsoft.AspNetCore.Odata -7.4.0

[sandeepagrawal921]

issue while filtering on dynamic string property with tolower() function

Assemblies affected

*Microsoft.AspNetCore.Odata -7.4.0

Steps to reproduce -

1. Add dictionary object in response.
2. Try to filter on the dictionary key with tolower() function as we want to filter data with case-insensitivity.

Expected result

Result data should filter as per expectation

Actual result

*throwing error as -
"error": {
"code": "",
"message": "The query specified in the URI is not valid. The 'tolower' function cannot be applied to an enumeration-typed argument.",
"details": [],
"innererror": {
"message": "The 'tolower' function cannot be applied to an enumeration-typed argument.",
"type": "Microsoft.OData.ODataException",
"stacktrace": " at Microsoft.AspNet.OData.Query.Expressions.ExpressionBinderBase.ValidateAllStringArguments(String functionName, Expression[] arguments)\r\n at Microsoft.AspNet.OData.Query.Expressions.ExpressionBinderBase.BindToLower(SingleValueFunctionCallNode node)\r\n at
*

Additional detail

Optional, details of the root cause if known. Delete this section if you have no additional details to add.

[gathogojr]

@sandeepagrawal921 Kindly share your $filter query/expression or something close enough to your actual scenario that can provide us a good view of what you're working with.

I'm not sure I understand what you mean by this statement: "filter on the dictionary key"
Do you mean filter by a dynamic property stored in the dynamic property container (dictionary of string and object)?

Kindly provide us with sufficient details in the Reproduce steps that we can use to recreate the issue you're facing

[sandeepagrawal921]

Hi @gathogojr ,
you are correct. I meant filtering by a dynamic property stored in the dynamic property container (dictionary of string and object). In the below-given class model screenshot, position data is the response returned from API where ESGScores is a dictionary object in position data model response. When we try to filter out the value by one of the EsgScores property, we want to do it by case-insensitive match and that's why I am trying to convert it into tolower(EsgScores/dynamicProperty) eq tolower(filterValue). this is where oData is breaking.

FYI - here is the sample query -
http://localhost:60955/entities/positions?AccountId=IC0002&BookId=ICMF&OrgId=ICM&AsOfDates=2016-09-30&InquiryBasisNumbers=1&$select=data($count=true;$select=Instrument/ASSET_CLASS_MNEM,INQ_BASIS_NUM,LOCAL_CURR_CDE,QUANTITY,Instrument/ISS_NME,CRVL_CMB_AMT,VALVAL_CMB_AMT,VALVAL_ALT_CMB_AMT,ESGScores/MSCI_ESG__Social__WeightedScore,ESGScores/MSCI_ESG__Social__ScoreAssessmentCategory;$count=true;$top=5;$filter=tolower(Instrument/ISS_TYP) eq 'cash' and ESGScores/MSCI_ESG__Social__WeightedScore le 1.54m and tolower(ESGScores/MSCI_ESG__Social__ScoreAssessmentCategory) eq 'average')

Here tolower() works on Instrument/ISS_TYP as this is not a dynamic property. even dynamic property filter on decimal field - ESGScores/MSCI_ESG__Social__WeightedScore also works fine.
But tolower() doesn't work on dynamic property - tolower(ESGScores/MSCI_ESG__Social__ScoreAssessmentCategory)

Hope I am clear with my query, please let me know if still any confusion.
Thanks

[gathogojr]

@sandeepagrawal921 Is MSCI_ESG__Social__ScoreAssessmentCategory an enum type?
If it is, you won't be able apply tolower function the way you're doing
The following could work if it is an enum type:

ESGScores/MSCI_ESG__Social__ScoreAssessmentCategory eq cast('average', #NS#.#EnumType#)

Otherwise for the case of a dynamic property of type string I was able to reproduce the issue. Seems that in that scenario tolower function throws an exception with the message: The query specified in the URI is not valid. The 'tolower' function cannot be applied to an enumeration-typed argument.

We'll need to investigate and fix the issue.
cc. @mikepizzo @xuzhg

[sandeepagrawal921]

Hi @gathogojr ,
This is not enum, this is the case of a dynamic property of type string which you have mentioned in later part of your last reply. Is it possible to look into this/fix this as this is bit urgent for us.

I appreciate your quick response. thank a lot.

[sandeepagrawal921]

@gathogojr
Hi John, do we have any idea by when can we expect this fix?

one more quick query is, to apply filter on decimal fields do we need to append M in decimal field filter value always?

Thanks

[pashokchakravarthi]

We are facing the same issue when applying tolower function on dynamic property in filter condition. Any ETA for this bug fix?

[cakmaksevgi]

same issue is still valid when filtering dynamic properties. Do you have any estimation time about fixing that issue?

[cdavernas]

Same issue here with a Dictionary<string, string> property. Any idea on a possible fix/workaround? Im using the Microsoft.AspNetCore.OData 8.x package.

[gathogojr]

Fixed in Microsoft.AspNetCore.OData 8.0.11 via update of Microsoft.OData.Core 7.12.2 dependency - PR OData/odata.net#2257