Case insensitive groupby ($groupby=tolower(column))

[hidegh]

Is there a way to support case-insensitive grouping, which translates to SQL: groupby lower(myColumn)?

[missing feature or documentation for extreme cases]

Assemblies affected

OData Web API - 7.3.0 (beta), resp. OData

Reproduce steps

Try to create a grouping $groupby=lower(surname)

Expected result

In case of surnames with casing difference, only 1 group should be returned for one surname (casing should not be considered). The generated SQL should be: group by lower(columnName).

Actual result

OData error at expression parsing.

Additional detail

Details to do extreme cases are hard to find.

My lists of those quiet frequent SQL queries which can be done with OData:

1. expand with filtering the inner collection (by def. just the any/all operaiton is mentioned): https://{{netAppApi_domain}}/Users/4/mealSummary/?$count=true&$filter=day gt 2019-12-08&$expand=meals($filter=time(cast(date, 'Edm.DateTimeOffset')) gt 12:00:00.00000)
2. filtering date-part only (with SQL datetime2): $filter=time(cast(date, 'Edm.DateTimeOffset')) gt 12:00:00.00000
3. filtering dates (without time-part) was quiet easy: $filter=date(day) gt 2019-12-09
4. group by by multiple columns, filter & getting special aggregates: $format=json&$count=true&$apply=filter(contains(tolower(VPJobID),tolower('1220'))/groupby((VPJobID, JobName), aggregate($count as count, UseInTotal with sum as totalCount))&$top=25&$orderby=VPJobID,JobName
5. case-insensitive group-by - ???

[hidegh]

@robward-ms ^ $groupby=tolower(column) as feature?

[KanishManuja-MS]

The correct syntax should be something like: $apply=compute(lcname as tolower(surname))/groupby((lcname))

The compute is required in order to project the result of the expression as a dynamic property before applying the groupby since groupby groups by properties and not expressions.

We will be adding support for compute in the next release, you can feel free to take a look the PR that adds that support.

More info on the groupby if you need to do more complex things.
http://docs.oasis-open.org/odata/odata-data-aggregation-ext/v4.0/cs02/odata-data-aggregation-ext-v4.0-cs02.html#_Toc435016583

[KenitoInc]

@hidegh Support for $compute was added in v8.0.5 Can you try it? https://github.com/OData/AspNetCoreOData/releases/tag/8.0.5

[KenitoInc]

Closing this issue due to inactivity. If this issue still persists, feel free to create a new issue.