SAP Gateway $filter on $expand Entity

Question

I've seen two post about the URL convention, but my question is specific to the SAP's Gateway implementation for OData web services assuming. When trying to use $filter in combination with $expand we get the error message:

Left hand expression of memberaccess operation has wrong cardinality

Assuming I have two simple entities:

Foo
 * Key
 - Value

Bar
 * Key
 * Id
 - Value

Foo has a 1:n association to Bar. The following URL works as intended.

/sap/opu/odata/sap/ZTEST_SRV/Foo?$expand=Bar

As does

/sap/opu/odata/sap/ZTEST_SRV/Foo?$filter=Key gt 10&$expand=Bar

When trying to using $filter on entity Bar property Id we get the error message.

/sap/opu/odata/sap/ZTEST_SRV/Foo?$filter=Key gt 10 and Bar/Id gt 2&$expand=Bar

Is it possible to use a $filter in this way with SAP? Related articles below.

ODATA / SAP Gateway: About Query with $filter and $expand simultaneously

Filter on Expanded entities in OData

Answer 1

I am also facing similar problems, there is this sap note: https://apps.support.sap.com/sap/support/knowledge/en/3008698

Copy of the note details:

---

BEGIN OF NOTE

---

Symptom

After adding a filter

$filter = To_Employees/Name eq 'Hugo' to an Odata service, there is an error:

"Left hand expression of memberaccess operator has wrong cardinality (to many not allowed)"

---

Environment

SAP_GWFND 750

---

Reproducing the Issue

Access the odata service: /sap/opu/odata/sap/API_XXX_SRV/Orgnization$select=Orgnization,to_Employees/Name&$expand=to_Employees&$filter=to_Employees/Name eq 'Hugo' 
There is an error: "Left hand expression of memberaccess operator has wrong cardinality (to many not allowed)"

---

Cause

"Left hand expression of memberaccess operator has wrong cardinality (to many not allowed)" indicates that "to_Employees"is a to-many navigation, and this is not supported in combination with a filter expression (e.g.

$filter = To_Employees/Name eq 'Hugo',

when "To_Employees" points to more than one employee ).

Therefore, the whole request is invalid.

---

Resolution

Remove the filter, do not combine to many results with filter eq

---

END OF NOTE

I don't like the solution presented :)

The workaround solution I have implemented was to create an entity which has cardinality 1:1 just for filtering. In your example, I assume you need the cardinaliy of the relationship from Foo to Bar to be 1:n in order to be able to receive multiple Bar records for each Foo found. If the relationship Foo to Bar is 1:1, you can simply change the cardinality and you are good to go. If you need cardinality 1:n, you may create an entity like BarFilter which is related to Foo with 1:1 cardinality. Then you would be able to execute this request without errors and can receive the filters to make the corresponding query on your backend system. The request would be something like:

New simple entity:

BarFilter

• Key
• Id

• Value

Foo has a 1:1 association to new entity BarFilter.

Request:

/sap/opu/odata/sap/ZTEST_SRV/Foo?$filter=Key gt 10 and BarFilter/Id gt 2&$expand=Bar

I hope this is clear and that it helps you. I am very interested in this topic so if you find something interesting, please share it.

Have a nice weekend.

Cheers