SAP Knowledge Base Article - Public

2751116 - How to Use Sorting on Expanded Collection With OData Services

Symptom

You are trying to use sorting with the $orderby option in an expanded navigation property from a collection, for example:

https://myXXXXXX.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/ServiceRequestCollection?$filter=ID eq 'ZZZZZZZ'&$expand=ServiceRequestTextCollection&$orderby=ServiceRequestTextCollection/CreatedOn desc

(XXXXXX represents the number after 'my' in the tenant URL / ZZZZZZZ represents the service request ID)

But you are receiving an error that may be similar to this:

In the context of Data Services an unknown internal server error occured|An exception with the type CX_SY_MOVE_CAST_ERROR occurred, but was neither handled locally, nor declared in a RAISING clause|Source type \CLASS=CL_DS_EDM_NAV_PROPERTY is not compatible, for the purposes of assignment, with target type \INTERFACE=IF_DS_EDM_PROPERTY

Environment

  •  SAP Cloud for Customer.

Cause

The OData 2.0 does not support sorting ($orderby) on a child entity's property for the expanded ($expand) entity collection. It only supports sorting ($orderby) based on the parent entity's property for the expanded entity collection. Using the $expand option, the result will have both the parent and the child entity collections but the sorting will be based on the parent entity's property.

Resolution

To use the navigation property 'ServiceRequestTextCollection', the parent entity's object ID should be passed as input in the url. The result will have only the child entity collection 'ServiceRequestTextCollection' sorted by the 'CreatedOn' option. As shown below:

https://myXXXXXX.crm.ondemand.com/sap/c4c/odata/v1/c4codataapi/ServiceRequestCollection('YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY')/ServiceRequestTextCollection?$orderby=CreatedOn desc

(YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY represents the ObjectID for the Service Request)

In the example above, the ServiceRequestTextCollection is fetched for the ServiceRequest ObjectID = 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY'. Then the navigation to the child entity is called using the navigation property 'ServiceRequestTextCollection', then it gets sorted based on the child entity's property 'CreatedOn'.

Keywords

sorting, orderby, text collection , KBA , AP-RC-ODF , OData framework (C4C Only) , Problem

Product

SAP Cloud for Customer add-ins all versions ; SAP Cloud for Customer core applications all versions