Cannot get associations part of my external OData entity

[Trinidad]

Hi experts,

I have followed the great blog CAP: Consume External Service Part 1 and Part 2 from jhodel18 to generate the CSN model corresponding to my SAP Business One OData v4 entities and access it from SCP via destinations.
The generation is successful and I can use the generated entities to generate a service catalog exposing the entities I want to work with.

My problem is how to retrieve the associations/Navigation properties that are part of my entities.
For example the Items entity has some fields like ItemCode of basic cds types, some other fields as a Collection of entity types like for example the ItemPrices of type SAPB1.ItemPrice and some others defined as NavigationProperties.

In the edmx file I can see for example SpecialPrices defined as a NavigationProperty

<EntityType Name="Item"><br>  <Key><PropertyRef Name="ItemCode"/></Key><br>  <Property Name="ItemCode" Type="Edm.String"/><br>  ...<br>  <Property Name="ItemPrices" Type="Collection(SAPB1.ItemPrice)"/><br>  ...<br>  <NavigationProperty Name="SpecialPrices" Partner="Item" Type="Collection(SAPB1.SpecialPrice)"/><br>  ...

The CSN has been generated and the association has been added:

"SAPB1.Items": {   <br>  "kind": "entity",   <br>  "@cds.persistence.skip": true,   <br>  "elements": {     <br>    "ItemCode": {       <br>      "key": true,       <br>      "type": "cds.LargeString"     <br>    },    <br>    ...   <br>    "ItemPrices": {     <br>      "type": "SAPB1.ItemPrice"   <br>    },   <br>    ...<br>    "SpecialPrices": {
      "type": "cds.Association",
      "target": "SAPB1.SpecialPrices",
      "cardinality": {
        "max": "*"
      }
    },
...

I have then exposed the Items entity in my catalog with just 2 basic properties and the SpecialPrices navigation property:

entity Itm as projection on SAPB1.Items {
  ItemCode,ItemName,SpecialPrices
};

But when I check the metadata all the direct fields of the Items I can see the basic cds types are correctly exposed but the fields of the SAPB1.SpecialPrices type are exposed with the name of the entity type plus "_" directly on the Items service.

<EntityType Name="Itm">
  <Key><PropertyRef Name="ItemCode"/></Key>
  <Property Name="ItemCode" Type="Edm.String" Nullable="false"/>
  <Property Name="ItemName" Type="Edm.String"/>
  <Property Name="SpecialPrices_ItemCode" Type="Edm.String"/>
  <Property Name="SpecialPrices_CardCode" Type="Edm.String"/>
</EntityType>

The query should look like this to be accepted by B1:

/Items?$select=ItemCode,ItemName,SpecialPrices

But in the logs I can see the following has been requested:

/Items?$select=ItemCode,ItemName,SpecialPrices_ItemCode,SpecialPrices_CardCode&$top=1000&$orderby=ItemCode%20asc&$format=json

SAP Business One backend does not accept the query sent having “_” to separate the name of the Entity and the specific property part of the entity.

Do you know what is the reason for this transformation and how to avoid it maybe with a different definition in my catalog service?

Could I specify the exact query that I want to be sent to my external OData service as part of my catalog-service.js?

Something like SELECT where I can specify the fields I want to retrieve?

Thanks,

Trinidad.

[Trinidad]

Same conclusions as for this one:
https://answers.sap.com/questions/13204230/cannot-get-subcollection-details-part-of-an-extern.html

[former_member5967]

Thanks for sharing & clarifying the doubts.