Tutorial 02 11 Expanding Relations

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.

Navigation Properties

The mechanism for traversing data relationships is navigation properties, which are properties added to each data model class to represent relationships to other data files.

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 a one-to-one relationship from CUSTOMERS to ITEMS: a customer record contains the item code of the customer's favorite item. For these, you will see two navigation properties added to the customer data object:

;;; <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

Expanding Navigation Properties

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 returns all the information for customer 1. In addition to the properties that represent the data fields in the customer record, you will see an additional property named REL_Orders with all the information for all the orders associated with that customer.

{
  "@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 results in 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 can 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 results in 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, as in this example:

https://localhost:8086/odata/v1/customers(CustomerNumber=1)?$expand=REL_Orders,REL_Item

And you can 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)))

Enabling Relation Expansion

To enable the ability to traverse data relationships, you must set the Enable OData relations option:

At a Windows prompt, move to the directory with your Harmony Core solution, and type harmonycore gui.) Then, when the GUI for the Harmony Core CLI tool opens, go to the OData screen, scroll down to the Enable OData relations option, and double-click it.
In the "Enter new value" screen, click the diamond icon to the right of Enable OData relations. (The diamond will change to a checkmark.) Then click OK.

Generating the Code

Save the settings you made by selecting File > Save from the menu.
Generate code for your solution by selecting Codegen > Regen from the menu. When code generation is finished, a message lists the files that were generated. Click OK to close the message.

What Changed

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 properties are 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.

Building the Code

In Visual Studio, select Build > Rebuild Solution from the menu.

Check the Output window, 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 ==========

Testing the New Functionality

In Visual Studio, press F5 (Start Debugging) to start the self-hosting application. 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, you should also be able to do the following:
- Get customer 1 along with the name of their favorite item: https://localhost:8086/odata/v1/customers(CustomerNumber=1)?$expand=REL_Item($select=CommonName)
- Get customer 1 along with all associated orders: https://localhost:8086/odata/v1/customers(CustomerNumber=1)?$expand=REL_Orders
- Get customer 1 along with all their orders, and all items for each order: https://localhost:8086/odata/v1/customers(CustomerNumber=1)?$expand=REL_Orders($expand=REL_OrderItems)

Stop the Service

When you are done testing, stop the self-hosting application.

Next topic: Postman Tests

Provide feedback

Saved searches

Use saved searches to filter your results more quickly