SAP Cloud Platform Backend service: Tutorial [3]: CDS : how to define associations (1)

In this blog, you’ll find more information about CDS

Why?

Because this tutorial is part of a series which aims to support you in creating many many APIs using Backend service.

If you’ve reached this blog purely by accident, you should go to the starting tutorial first – you’ll end up loving the Backend service.

In this tutorial, we’re going through the model definition required to define a relation between entities. This is called association in terms of CDS and association in terms of OData.
Same name?
Yes, but in OData usually we call it navigation, because this is the advantage: I have a link to the entity to which I want to navigate (can also be a list of entities).
And this allows the UI to have e.g. a Button for navigating to the related information

We’ll need 2 tutorials to cover the association topic, because CDS supports at least 3 types of associations.
We’ll focus on 3 of them
– unmanaged one-to-one
– managed one-to-one (next blog)
– managed one-to-many (next blog)

In this tutorial, we’re going to discuss in detail the first type: unmanaged to-one association

Why unmanaged?

Please be patient…. For now you can think of it as just a normal association

The model

First of all, I’d like to present the model to you. Afterwards, I’ll explain everything in detail.
Here it is:

service ProductService {

    entity CustomerEntity{
        key customerID : Integer;
        companyName : String;
        contactProperty: Integer; 
        linkToContact :  Association to ContactEntity 
                         on linkToContact.contactID = contactProperty; 
    }

    entity ContactEntity{
        key contactID : Integer;
        contactName : String;
        contactPhone : String;
    }
}

Please note:
I’ve given unusual names to the model elements.
Names like “ContactEntity” or linkToContact are silly, however, you’ll see how much this helps in understanding the concept.
Please stick to the usual naming in your future models
Or, at least don’t tell anybody who had guided you to use such unusual naming…;-)

The model contains 1 example for defining associations: unmanaged one-to-one
Unmanaged means, CDS doesn’t manage anything. You’ll understand it better, once you see an example for managed associations in the next tutorial.

There’s also an example for nothing-to-nothing-association (aka “no association”), but I think we don’t need to discuss that ? (In the sample model, we don’t have any navigation from contact to customer, to keep it simple)

The scenario

In our application, we want to maintain information about our customers.
A customer is a company.
As such, we model an entity and call it CustomerEntity.
(the usual name would be “Customers”)
It keeps information about the company: name, website, location, tax, whatever, but no person data

Instead, there’s another entity, ContactEntity, which holds information about the person, who is responsible for that company

As such, the company itself only needs to keep the identifier of the contact person who is responsible for this company.
This is the property “contactProperty” in the CustomerEntity (usually we would call it “contact”)

BTW, this is the concept of Foreign Key.

Summarizing:
The user of our application can see a list of customers with customer-details, and if he wants to see the details of a contact person, takes the number of contactProperty and searches for it in the list of contacts.

Still a little uncomfortable…

Yes, it is….but…….

The explanation

…but since we’re on the lucky side of the cloud… we don’t need to care about databases and tables…. we have the Backend service which generates an OData service for us…

so we only have to follow a navigation link if we want to view the contact person of a company.

sounds much better…

OK. This is why we have associations:
We define an association between CustomerEntity and ContactEntity

    . . .
    linkToContact : Association to ContactEntity 
    . . .

Now there’s a question which we haven’t asked above:
We assume that the value of “contactProperty” is an identifier of an existing contact person.
This has been implicit knowledge
Now we need to make it explicit.

Steps:

In “CustomerEntity”, add a property with name linkToContact
This property is an association to the desired target entity “ContactEntity”

entity CustomerEntity{
     . . .
     linkToContact :  Association to ContactEntity

With other words:
we can navigate from “CustomerEntity” to “ContactEntity” via the property “linkToContact”

There’s still an open point: the target is a table, a list of contact persons.
But only one person is the right contact person for the current customer/company
Which is the right one?

We know it: it is the person which has the same identifier number like the number which is stored in the company (contactProperty)
Right?
How can we express this condition on the modelling level?
Like this:

     linkToContact : Association to ContactEntity 
                     on linkToContact.contactID = contactProperty; 

We are expressing exactly the condition described above:

contactID = contactProperty

the person which has the same identifier number (contactID) like the number which is stored in the company (contactProperty)

The unique identifier of the existing person in the list, corresponds to the value which is stored in “contactProperty”

“contactID” is the target person to which we want to navigate
“contactProperty” keeps this number, it is a property of the current customer/company. The source.

I fear the order might be confusing…

Sorry, everything is confusing here…

I try to understand like this:
In our application, we have selected a customer and we want to navigate to the contact person.
We find the right contact person by its identifier (contactID)
Which is the right identifier?
The one which matches the selected property (contactProperty)
As such:
contactID = contactProperty

OK?

Here’s a colored attempt:

grmpfh…

OK, don’t worry: Get used to it.

Or come back to my blog, to remember

Next topic:
One more info is required:
We’re defining an association in our CustomerEntity:

contactID = contactPerson

We’re inside of CustomerEntity, so we have knowledge of the property “contactPerson”.
Because it is defined in the current entity. (CustomerEntity)
But how can the framework resolve the property “contactID” ?
It is in the target entity
How we find it?

The answer: via association

OK, but which association? There can be multiple.

As such, we have to write the fully qualified name of the target property:

      linkToContact.contactID = contactProperty

Means:
the target property name “contactID” can be found when following the association “linkToContact”

Yes, and the association “linkToContact” was defined above. It points to “ContactEntity”.
Remember?
linkToContact : Association to ContactEntity

Putting it all together:

     linkToContact : Association to ContactEntity 
                     on linkToContact.contactID = contactProperty; 

Want a summary?
In our CustomerEntity, we define an (association-)property in order to navigate to the contact.
As such, the property is an association to the contact.
The right contact is found by the contactID of the ContactEntity, which must have the same value like the contactProperty of our CustomerEntity

Now everything should be clear.

Still confusing….

Please please forgive if this blog has been too boring…
Yes, in fact, it…
I haven’t asked… I just understand, and I hope it will become more clear when we try it in an example.

Let’s see it in action.
And hope it will be less boring…

The Test

Enough text – now we want the test.

OK, go ahead and create the API using the above model.
If you forgot how to do that…. I recommend the tutorial [1]

The metadata

Once the OData service is up and running, we can have a quick view at the metadata, to see how the association is translated in the OData world:

<EntityType Name="CustomerEntity">  
   . . . 
   <Property Name="contactProperty" Type="Edm.Int32"/>
   <NavigationProperty Name="linkToContact" Type="ProductService.ContactEntity">
      <ReferentialConstraint Property="contactProperty" ReferencedProperty="contactID"/>
   </NavigationProperty>
</EntityType>

Everything looks as expected – which is always good

Note
The ”ReferentialConstraint” is of interest, because it expresses in terms of OData what we were talking above. And it is a bit more verbose:
The “Constraint” adds a condition to the corresponding navigation, as we said above.
The constraint makes clear that the target property “contactID” is “referenced” by the “contactProperty”
And the target property is found, because the target EntityType is mentioned in the “Type” attribute of the “NavigationProperty”

<NavigationProperty Name="linkToContact" Type="ContactEntity">

Note:
The default OData version of the generated OData service is V4.
As such, the above snippet is V4
In OData V2, the definition of navigation is different

The data

Once the OData service is up and running, we need to create some sample data.
So please follow Tutorial [1]

Ohhhhh….no explanation for creating data there…

Sorry, my mistake, please follow Tutorial [2]

Note:
Make sure that you create meaningful data, I mean your “contactProperty” has to refer to a property which really exists. Otherwise the navigation test won’t work

The test

After creating some data, we can try the navigation

First, let’s view the Customer collection (it has the strange name CustomerEntity – due to my silly naming):

https://…/odatav4/DEFAULT/PRODUCTSERVICE;v=1/CustomerEntity

You can see in my example that I’ve created 2 Customers which have the same contact person.
So we expect that when executing the navigation, we’ll reach the same target contact for both requests

Now we can pick an entry and navigate to the corresponding Contact person:

https://…/odatav4/DEFAULT/PRODUCTSERVICE;v=1/CustomerEntity(1)/linkToContact

https://…/odatav4/DEFAULT/PRODUCTSERVICE;v=1/CustomerEntity(2)/linkToContact

https://…/odatav4/DEFAULT/PRODUCTSERVICE;v=1/CustomerEntity(3)/linkToContact

And this is the result of our navigation:

BTW, the odata.context property tells us that the result is of type ContactEntity

OK, finally done.
That’s how it works and that’s how it works and that’s what we expected

I expect that this tutorial series continues…
Yes, it does: CDS associations part 2

The summary

We’ve gone the way from modelling an association to executing the navigation in the OData service
With other words, we started writing this text

linkToContact : Association to ContactEntity on linkToContact.contactID = contactProperty;

and ended up writing this URL

https://…/odatav4/DEFAULT/PRODUCTSERVICE;v=1/CustomerEntity(1)/linkToContact

The links

Preparation and configuration for using SAP Cloud Platform Backend Service

Tools for modelling (typing) CDS

First tutorial:
https://blogs.sap.com/2019/01/25/sap-cloud-platform-backend-service-tutorial-1-easy-api-creation/comment-page-1/#comment-451005

The tutorial overview page

CDS docu on associations:
https://help.sap.com/viewer/65de2977205c403bbc107264b8eccf4b/Cloud/en-US/9ead8e4701d04848a6fdc84356723a52.html

OData specification:
NavigationProperty section

The Appendix 1: CDS model file

service ProductService {

    // Unmanaged 1:1 relation without back link
    entity CustomerEntity{
        key customerID : Integer;
        companyName : String;
        contactProperty: Integer; // this is the foreign key. 
        linkToContact :  Association to ContactEntity 
                         on linkToContact.contactID = contactProperty; 
    }

    // no association example required here ;-)
    entity ContactEntity{
        key contactID : Integer;
        contactName : String;
        contactPhone : String;
    }
}

The Appendix 2: Rest Client request body

Sample payload for creating ContactEntity:

{
  "contactID": 1,
  "contactName": "Paul",
  "contactPhone": "123456789"
}

Sample payload for creating CustomerEntity:

{
  "customerID": 1,
  "companyName": "SAP",
  "contactProperty": 1
}

The Appendix 3: Note about proposals in Cockpit test tool

While creating entries for “CustomerEntity”, please note:
The test tool is very helpful for creating entries, because it proposes the required payload.
In case of “CustomerEntity” the proposed payload looks like this:

{
  "customerID": 0,
  "companyName": "string",
  "contactProperty": 0,
  "linkToContact": {
      "contactID": 0,
      "contactName": "string",
      "contactPhone": "string"
  }
}

However, as you might have already noticed, it is generic and derives the proposed payload from the schema of the service (called “metadata document” in OData)
As such, it attempts to interpret the navigation properties as well.
Such that the proposal contains not only the properties of the CustomerEntity which we want to create, but moreover, it contains the navigation property and, as value, the payload of “ContactEntity”.

You can delete the navigation link and value, or you can leave it, the result is the same.

Note:
In OData, it is possible to create Entity and ChildEntity at the same time. This kind of CREATE operation is called “DeepInsert”.