The API Server supports OData $expand functionality, allowing a single request to provide data from a record and from related entities. To enable this feature, the relationships between entites must be defined in the API Server resources.

Relationships

Relationships between resources must be defined in the API Server before using the OData $expand functionality. There are several ways to define relationships between entities. The examples below use the Product/Category relationships.

Using the Schema Editor

A relationship can be defined manually in the schema editor by adding the ‘relationships’ XML attribute to the column defining the relationship. The attribute should follow the format: relationshipName(ResourceName.ColumnName). Multiple relationships can be separated by the | symbol. The formatting the relationship definition depends on its cardinality.

One-to-many Relationships

A one-to-many relationship can be defined in the schema entry corresponding to the primary key in the schema of the parent object. Note that the name of the relationships starts with a * to indicate the cardinality:

<attr name="CategoryID" key="true" type="int" columnsize="11" 
      isNullable="false" readonly="false" relationships="*Products(Product.CategoryID)" 
      desc="" />

Many-to-one Relationships

A many-to-one relationship can be defined in the line corresponding to the foreign key in the schema of the child object:

<attr name="CategoryID" key="false" type="int" columnsize="11" 
      isNullable="true" readonly="false" relationships="ProductsCategory(Category.CategoryID)" 
      desc="" />

Partner Attribute

You can add an @ prefix to the name of a relationship to add the Partner attribute, as shown in the examples below.

For a one-to-many relationship that includes an * symbol:

relationships="*@Products(Product.CategoryID)"

For a many-to-one relationship:

relationships="@ProductsCategory(Category.CategoryID)"

The application does not perform any validation on the Partner attribute. As a result, when using this feature, you must ensure that the following conditions are met:

• The reverse relationship is defined and marked with the Partner attribute.
• The name of the reverse relationship is exactly the same as the name of the current item, including whether it includes or excludes plural.

OData Expand Syntax

Once the relationships between related entites are defined, the $expand keyword enables the selection of records properties as well as the set of related objects inline in a single request. Please refer to the OData documentation for more details on the syntax of $expand queries.

One-to-many Relationship

For each base record, retrieve all related objects.

http://MyServer:MyPort/api.rsc/Categories(1)?$expand=Products

Response:

{
    "@odata.context": "http://localhost:8153/api.rsc/$metadata#Categories",
    "value": [
        {
            "CategoryID": "1",
            "CategoryName": "Books",
            "Products": [
                {
                    "ProductID": "101",
                    "Price": "45",
                    "Name": "Astronomy 101",
                    "Supplierid": "1",
                    "CompanyId": "1",
                    "CategoryId": "1"
                },
                {
                    "ProductID": "102",
                    "Price": "50",
                    "Name": "Physics 101",
                    "Supplierid": "2",
                    "CompanyId": "1",
                    "CategoryId": "1"
                },
                {
                    "ProductID": "103",
                    "Price": "65",
                    "Name": "Chemistry 101",
                    "Supplierid": "3",
                    "CompanyId": "1",
                    "CategoryId": "1"
                }
            ]
        }
    ]
}

Many-to-one Relationship

For each record, retrieve the related parent record.

http://MyServer:MyPort/api.rsc/Products?$expand=ProductsCategory&$filter=price lt 60&$orderby=price desc

Response:

{
    "@odata.context": "http://localhost:8153/api.rsc/$metadata#Products",
    "value": [
        {
            "ProductId": 102,
            "Name": "Physics 101",
            "Price": "50",
            "CategoryId": "1",
            "ProductsCategory": [
                {
                    "CategoryName": "Books",
                    "CategoryId": "1"
                }
            ]
        },
        {
            "ProductId": 101,
            "Name": "Astronomy 101",
            "Price": "45",
            "CategoryId": "1",
            "ProductsCategory": [
                {
                    "CategoryName": "Books",
                    "CategoryId": "1"
                }
            ]
        }
    ]
}

Multiple Expansions

The $expand parameter can take a comma-separated list of values, combining many-to-one and one-to-many relationships.

http://MyServer:MyPort/api.rsc/Products(102)?$expand=Supplier,ProductReviews

Response:

{
    "@odata.context": "http://localhost:8153/api.rsc/$metadata#Products",
    "value": [
        {
            "ProductId": 102,
            "Name": "Physics 101",
            "Price": "50",
            "ProductReviews": [
                {
                    "ProductReviewID": "1001",
                    "ProductId": "102",
                    "Rating": "4.5",
                    "Description": "Easy to read, good illustrations."
                },
                    {
                    "ProductReviewID": "1002",
                    "ProductId": "102",
                    "Rating": "1.5",
                    "Description": "Boring!"
                }
            ],
            "Supplier": [
                {
                    "SupplierId": "1",
                    "SupplierName": "New Science Publishing"
                }
            ]
        }
    ]
}

Expand Options

OData API calls can use expand options for a more granular definitions of the related entities to return. Expand options are specified as a semicolon-separated list, in parenthesis appended to the related entity.

Select

The $select option defines which columns of the related entity will be returned. If not specified, all available values will be returned. e.g.:

http://MyServer:MyPort/api.rsc/Products(102)?$expand=ProductReviews($select=Description),Supplier($select=SupplierName)

Filter

The $filter option can be used to define a criteria to apply on related results. Related entities which do not conform by this criteria will be ommitted from the response. e.g.:

http://MyServer:MyPort/api.rsc/Products(102)?$expand=ProductReviews($select=Rating;$filter=Rating ge 4)