Skip to content

Tutorial 02 11 Expanding Relations

Jump to bottom
Steve Ives edited this page May 19, 2020 · 12 revisions

Harmony Core Logo

Expanding Relations

An extremely powerful feature of OData services, if your repository metadata supports it by including information about relations between structures, is the ability to expose and follow relations between entities when querying.

VIDEO: Creating a Basic Solution

For example, in the Harmony Core sample repository and data, we have customers. Each customer may have associated orders, and each order has one or more order line items. Each of those line items refers to an inventory item, and each inventory item is associated with a specific vendor.

If enabled, 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 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 all navigation properties 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), while other navigation properties represent one-to-many relationships (e.g. each VENDOR may be related to any number of ITEMS).

Another example is that in the Harmony Core sample repository and data, the CUSTOMERS structure has a relationship to the ORDERS structure, with one customer having zero or more orders in the orders file. There is also a relationship from CUSTOMERS to ITEMS because the 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

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(1)?$expand=REL_Orders

This URL would be used to return all of 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 of 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=="
    }
}

In the example above, the customer only has two orders on file.

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(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(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(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(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 enable the ENABLE_RELATIONS option:

  1. Edit regen.bat and remove the rem comment from the beginning of the line, like this:

    set ENABLE_RELATIONS=-define ENABLE_RELATIONS

Generating the Code

  1. Save your changes to regen.bat.

  2. If you don't already have a command prompt open in the solution folder, use the Tools > Command Prompt (x64) menu option to open a Windows command prompt, and type the following command:

    cd ..

  3. Type the following command to regenerate your code:

    regen

  4. As the batch file executes you will see various messages confirming which source files are being generated. Look for the word DONE to indicate that all code generation tasks completed successfully.

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

  1. Select Build > Rebuild Solution from the Visual Studio menu.

  2. 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

  1. 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 then these are some of the additional operations that should now be available:

  1. Get customer 1 along with the name of their favorite item.

  2. Get customer 1 along with all associated orders.

  3. Get customer 1 along with all of their orders, and all items for each order.

Stop the Service

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

Next topic: Postman Tests

Clone this wiki locally