Tutorial 02 11 Expanding Relations
The ability to expose and follow relations between entities when querying can be an extremely powerful feature of OData services--if your repository metadata supports it by including information about relations between structures.
For example, the Harmony Core sample repository and data has customers, and each customer may have associated orders. Each order has one or more order line items, and each of those items refers to an inventory item, which is associated with a specific vendor.
If enabled, the Harmony Core services allow you to traverse these relationships when constructing queries, the result being the return of hierarchical information from multiple tables.
The mechanism for traversing a data relationship is via navigation properties, which are properties that are added to each data model class to represent the relationship to some other data file.
To avoid naming collisions between the actual field properties and navigation properties, the name of each navigation property is prefixed with REL_, signifying a relation.
Some navigation properties represent one-to-one relationships (e.g., each ITEM is related to a single VENDOR). Other navigation properties represent one-to-many relationships (e.g., each VENDOR may be related to any number of ITEMS).
For example, the CUSTOMERS structure has a one-to-many relationship to the ORDERS structure, with one customer having zero or more orders in the orders file. And there is one-to-one relationship from CUSTOMERS to ITEMS: a customer record contains the item code of the customer's favorite item. In this scenario, you will see two navigation properties added to the customer data object, like this:
;;; <summary>
;;; Relationship (Type D)
;;; CUSTOMER.CUSTOMER_NUMBER (one) <-> (many) ORDER.CUSTOMER_NUMBER
;;; </summary>
public readwrite property REL_Orders, @ICollection<Order>
;;; <summary>
;;; Relationship (Type C)
;;; CUSTOMER.FAVORITE_ITEM (one) --> (one) ITEM.ITEM_NUMBER
;;; </summary>
public readwrite property REL_Item, @Item
To traverse data between two related data files, you use the navigation property that represents the relationship in an OData $expand expression, like this:
https://localhost:8086/odata/v1/customers(CustomerNumber=1)?$expand=REL_Orders
This URL would be used to return all the information for customer 1. And in addition to the properties that represent the data fields in the customer record, you will see an additional property named REL_Orders that will contain all the information for all of the orders associated with that company, like this:
{
"@odata.context": "https://harmonycoreapp.azurewebsites.net/odata/v1/$metadata#Customers(REL_Orders())/$entity",
"@odata.etag": "W/\"YmluYXJ5J0FDQUFBQUFBNHhsU3FRPT0n\"",
"CustomerNumber": 1,
"Name": "San Pablo Nursery",
"Street": "1324 San Pablo Dam Road",
"City": "San Pablo",
"State": "CA",
"ZipCode": 94806,
"Contact": "Karen Graham",
"Phone": "(555) 912-2341",
"Fax": "(555) 912-2342",
"FavoriteItem": 13,
"PaymentTermsCode": "01",
"TaxId": 559244251,
"CreditLimit": 2000,
"GlobalRFA": "ACAAAAAA4xlSqQ==",
"REL_Orders": [
{
"@odata.etag": "W/\"YmluYXJ5J3VGZ0FBQUFBRkFmRyt3PT0n\"",
"OrderNumber": 133,
"CustomerNumber": 1,
"PlacedBy": "Karen Graham",
"CustomerReference": "IVZ3579",
"PaymentTermsCode": "CA",
"DateOrdered": "2017-01-19T00:00:00Z",
"DateCompleted": "2017-01-20T00:00:00Z",
"GlobalRFA": "uFgAAAAAFAfG+w=="
},
{
"@odata.etag": "W/\"YmluYXJ5J0tGd0FBQUFBZEpFdEp3PT0n\"",
"OrderNumber": 141,
"CustomerNumber": 1,
"PlacedBy": "Karen Graham",
"CustomerReference": "KGX4931",
"PaymentTermsCode": "CA",
"DateOrdered": "2017-01-20T00:00:00Z",
"DateCompleted": "2017-01-23T00:00:00Z",
"GlobalRFA": "KFwAAAAAdJEtJw=="
}
...
Of course, you can combine the use of $expand with the other OData operations discussed in OData Query Support. For example, you could use $select to constrain the properties that are returned for the customer record, like this:
https://localhost:8086/odata/v1/customers(CustomerNumber=1)?$select=CustomerNumber,Name&$expand=REL_Orders
Which would result in data like this:
{
"@odata.context": "https://harmonycoreapp.azurewebsites.net/odata/v1/$metadata#Customers(REL_Orders())/$entity",
"@odata.etag": "W/\"YmluYXJ5J0FDQUFBQUFBNHhsU3FRPT0n\"",
"CustomerNumber": 1,
"Name": "San Pablo Nursery",
"REL_Orders": [
{
"@odata.etag": "W/\"YmluYXJ5J3VGZ0FBQUFBRkFmRyt3PT0n\"",
"OrderNumber": 133,
"CustomerNumber": 1,
"PlacedBy": "Karen Graham",
"CustomerReference": "IVZ3579",
"PaymentTermsCode": "CA",
"DateOrdered": "2017-01-19T00:00:00Z",
"DateCompleted": "2017-01-20T00:00:00Z",
"GlobalRFA": "uFgAAAAAFAfG+w=="
},
{
"@odata.etag": "W/\"YmluYXJ5J0tGd0FBQUFBZEpFdEp3PT0n\"",
"OrderNumber": 141,
"CustomerNumber": 1,
"PlacedBy": "Karen Graham",
"CustomerReference": "KGX4931",
"PaymentTermsCode": "CA",
"DateOrdered": "2017-01-20T00:00:00Z",
"DateCompleted": "2017-01-23T00:00:00Z",
"GlobalRFA": "KFwAAAAAdJEtJw=="
}
...
You could also use those same operations to constrain the data returned for each order. To do so, add a pair of parentheses immediately after the navigation property. Then use the OData operations within the parentheses, like this:
https://localhost:8086/odata/v1/customers(Customernumber=1)?$select=CustomerNumber,Name&$expand=REL_Orders($select=OrderNumber,CustomerReference)
Which would result in data like this:
{
"@odata.context": "https://harmonycoreapp.azurewebsites.net/odata/v1/$metadata#Customers(REL_Orders())/$entity",
"@odata.etag": "W/\"YmluYXJ5J0FDQUFBQUFBNHhsU3FRPT0n\"",
"CustomerNumber": 1,
"Name": "San Pablo Nursery",
"REL_Orders": [
{
"@odata.etag": "W/\"YmluYXJ5J3VGZ0FBQUFBRkFmRyt3PT0n\"",
"OrderNumber": 133,
"CustomerReference": "IVZ3579",
},
{
"@odata.etag": "W/\"YmluYXJ5J0tGd0FBQUFBZEpFdEp3PT0n\"",
"OrderNumber": 141,
"CustomerReference": "KGX4931",
}
...
It is also possible to expand multiple relations at the same level, and here is an example of doing so:
https://localhost:8086/odata/v1/customers(CustomerNumber=1)?$expand=REL_Orders,REL_Item
And you can also expand multiple hierarchical relationships. This example retrieves data from five separate files:
https://localhost:8086/odata/v1/customers(CustomerNumber=1)?$expand=REL_Orders($expand=REL_OrderItems($expand=REL_Item($expand=REL_Vendor)))
To enable the ability to traverse data relationships, you must set the "Enable OData relations" option:
-
In the Harmony Core GUI tool, go to the OData tab, and scroll down to "Enable OData relations" and double-click that option.
-
In the "Enter new value" screen, click the diamond icon to the right of "Enable OData relations". (The diamond icon will change to a checkmark.) Then click OK.
-
Save the setting you made by selecting File > Save from the menu of the Harmony Core GUI tool.
-
Regenerate code for your solution by selecting Codegen > Regen from the menu.
-
When code generation is finished, a message display listing the files that were generated. Click OK to close the message.
Enabling this option does not cause any additional source files to be generated. Rather is causes additional code to be generated into several existing source files, as follows:
-
Additional
navigation propertiesare added to data model classes to represent relationships to other classes (structures). Some of these properties are defined as collections of entities (representing one-to-many relationships) while others are defined as individual entities (representing one-to-one relationships). -
Additional code is added to the data model metadata classes to declare the presence of the new navigation properties and to initialize them when new data objects are created.
-
A new OData option (
builder.Expand()) is added to the OData configuration code in the Startup class. -
Additional parameter options (support for
$expand) are added to the API documentation for various operations.
-
Select
Build > Rebuild Solutionfrom the Visual Studio menu. -
Check the
Outputwindow, you should see something like this:1>------ Rebuild All started: Project: Repository, Configuration: Debug Any CPU ------ 2>------ Rebuild All started: Project: Services.Models, Configuration: Debug Any CPU ------ 3>------ Rebuild All started: Project: Services.Controllers, Configuration: Debug Any CPU ------ 4>------ Rebuild All started: Project: Services.Isolated, Configuration: Debug Any CPU ------ 5>------ Rebuild All started: Project: Services, Configuration: Debug Any CPU ------ 6>------ Rebuild All started: Project: Services.Host, Configuration: Debug Any CPU ------ ========== Rebuild All: 6 succeeded, 0 failed, 0 skipped ==========
- In Visual Studio, press
F5(Start Debugging) to start the self-hosting application. Once again you should see the console window appear with the messages confirming that your service is running.
If you are working with the Harmony Core sample repository and data, these are some of the additional operations that should now be available:
-
Get customer 1 along with the name of their favorite item.
-
Get customer 1 along with all associated orders.
-
Get customer 1 along with all of their orders, and all items for each order.
- When you are done with your testing, stop the self-hosting application.
Next topic: Postman Tests