Select columns by using OData

Important

When you query data, limit the amount of data returned to optimize performance. Only select the columns with data that you need.

When you select columns using OData in Dataverse Web API queries, use the $select query option to choose which columns to return. In OData, every column is represented as a property. If you don't include a $select query option, the query returns all properties, which can reduce performance.

The following example requests the name and revenue properties from the first row of the accounts EntitySet resource:

Request:

GET [Organization URI]/api/data/v9.2/accounts?$select=name,revenue&$top=1
Accept: application/json  
OData-MaxVersion: 4.0  
OData-Version: 4.0  

Response:

HTTP/1.1 200 OK  
Content-Type: application/json; odata.metadata=minimal  
OData-Version: 4.0  

{
    "@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts(name,revenue)",
    "value": [
        {
            "@odata.etag": "W/\"81052965\"",
            "name": "Litware, Inc. (sample)",
            "revenue": 20000.0000,
            "_transactioncurrencyid_value": "228f42f8-e646-e111-8eb7-78e7d162ced1",
            "accountid": "4624eff7-53d3-ed11-a7c7-000d3a993550"
        }
    ]
}

The primary key property is always returned, so you don't need to include it in your $select. In this example, accountid is the primary key.

You might see other property values included in the response. In this case, the _transactioncurrencyid_value lookup property for the related Currency (TransactionCurrency) table/entity reference is included because revenue is a currency property.

Which properties are available?

You can find all the available properties for an entity in the $metadata service document. For more information, see Web API Properties.

Tip

To quickly discover which properties are available, send a request by using the $top query option with a value of 1 without using $select. For example GET [Organization URI]/api/data/v9.2/accounts?$top=1

The Web API Entity Type Reference describes the entity types included with Dataverse.

Formatted values

The server generates formatted values as string values that you can use in your application. Formatted values include:

• The localized labels for choice, choices, yes/no, status, and status reason columns
• The primary name value for lookup and owner properties
• Currency values with currency symbols
• Formatted date values in the user's time zone

To include formatted values in your results, use the Prefer request header to send the odata.include-annotations preference.

Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"

Formatted values are one of several annotations you can request. Use Prefer: odata.include-annotations="*" to include all annotations. For more information, see Request annotations.

The formatted value is returned with the record by using an annotation that follows this convention:

<property name>@OData.Community.Display.V1.FormattedValue

For example:

Request:

GET [Organization URI]/api/data/v9.2/accounts?$select=name,revenue,_primarycontactid_value,customertypecode,modifiedon
&$top=1
Accept: application/json  
OData-MaxVersion: 4.0  
OData-Version: 4.0
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"

The following table describes the values and formatted values that are returned for the requested properties.

Property	Value	Formatted value
name	Litware, Inc. (sample)	None
revenue	20000.0000	$20,000.00
_primarycontactid_value	70bf4d48-34cb-ed11-b596-0022481d68cd	Susanna Stubberod (sample)
customertypecode	1	Competitor
modifiedon	2023-04-07T21:59:01Z	4/7/2023 2:59 PM
_transactioncurrencyid_value	228f42f8-e646-e111-8eb7-78e7d162ced1	US Dollar
accountid	78914942-34cb-ed11-b596-0022481d68cd	None

Response:

HTTP/1.1 200 OK  
Content-Type: application/json; odata.metadata=minimal  
OData-Version: 4.0
Preference-Applied: odata.include-annotations="OData.Community.Display.V1.FormattedValue"

{
    "@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts(name,revenue)",
    "value": [
{
            "@odata.etag": "W/\"81359849\"",
            "name": "Litware, Inc. (sample)",
            "revenue@OData.Community.Display.V1.FormattedValue": "$20,000.00",
            "revenue": 20000.0000,
            "_primarycontactid_value@OData.Community.Display.V1.FormattedValue": "Susanna Stubberod (sample)",
            "_primarycontactid_value": "70bf4d48-34cb-ed11-b596-0022481d68cd",
            "customertypecode@OData.Community.Display.V1.FormattedValue": "Competitor",
            "customertypecode": 1,
            "modifiedon@OData.Community.Display.V1.FormattedValue": "4/7/2023 2:59 PM",
            "modifiedon": "2023-04-07T21:59:01Z",
            "_transactioncurrencyid_value@OData.Community.Display.V1.FormattedValue": "US Dollar",
            "_transactioncurrencyid_value": "228f42f8-e646-e111-8eb7-78e7d162ced1",
            "accountid": "78914942-34cb-ed11-b596-0022481d68cd"
        }
    ]
}

Lookup property data

When a lookup property represents a multi-table (or polymorphic relationship), you need to request specific annotations to determine which table contains the related data.

For example, many tables have records that users or teams own. The system stores ownership data in a lookup column named ownerid. This column is a single-valued navigation property in OData. You can use $expand to create a join to get this value, but you can't use $select. However, you can use $select to get the corresponding _ownerid_value lookup property.

When you include the _ownerid_value lookup property with your $select, it returns a GUID value. This value doesn't tell you whether the owner of the record is a user or a team. You need to request annotations to get this data.

To include these annotations in your results, use the Prefer request header to send the odata.include-annotations preference with these settings:

Prefer: odata.include-annotations="Microsoft.Dynamics.CRM.associatednavigationproperty,Microsoft.Dynamics.CRM.lookuplogicalname"

Tip

Or use Prefer: odata.include-annotations="*" to include all annotations. For more information, see Request annotations.

Request:

GET [Organization URI]/api/data/v9.2/accounts?$select=name,_ownerid_value&$top=2
Accept: application/json  
OData-MaxVersion: 4.0  
OData-Version: 4.0
Prefer: odata.include-annotations="Microsoft.Dynamics.CRM.associatednavigationproperty,Microsoft.Dynamics.CRM.lookuplogicalname"

The following response returns two different account records. The _ownerid_value@Microsoft.Dynamics.CRM.lookuplogicalname annotation tells you that a team owns the first one, and a systemuser owns the second one.

Response:

HTTP/1.1 200 OK  
Content-Type: application/json; odata.metadata=minimal  
OData-Version: 4.0
Preference-Applied: odata.include-annotations="Microsoft.Dynamics.CRM.associatednavigationproperty,Microsoft.Dynamics.CRM.lookuplogicalname"

{
    "@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts(name,_ownerid_value)",
    "value": [
        {
            "@odata.etag": "W/\"81550512\"",
            "name": "Adventure Works (sample)",
            "_ownerid_value@Microsoft.Dynamics.CRM.associatednavigationproperty": "ownerid",
            "_ownerid_value@Microsoft.Dynamics.CRM.lookuplogicalname": "team",
            "_ownerid_value": "39e0dbe4-131b-e111-ba7e-78e7d1620f5e",
            "accountid": "1adef0b8-54d3-ed11-a7c7-000d3a993550"
        },
        {
            "@odata.etag": "W/\"81359849\"",
            "name": "Litware, Inc. (sample)",
            "_ownerid_value@Microsoft.Dynamics.CRM.associatednavigationproperty": "ownerid",
            "_ownerid_value@Microsoft.Dynamics.CRM.lookuplogicalname": "systemuser",
            "_ownerid_value": "4026be43-6b69-e111-8f65-78e7d1620f5e",
            "accountid": "78914942-34cb-ed11-b596-0022481d68cd"
        }
    ]
}

• <lookup property name>@Microsoft.Dynamics.CRM.lookuplogicalname is the logical name of the related table.
• <lookup property name>@Microsoft.Dynamics.CRM.associatednavigationproperty is the name of the corresponding single-valued navigation property. You can use $expand by using this value in another request to get more data from the related record.

Column aliases

For Web API, use fetchxml to specify customized aliases for columns.

Currently, you can't specify column aliases by using Dataverse Web API with OData. OData 4.0 doesn't include the $compute system query option introduced in OData 4.01 that's required to provide this capability.

Next steps

Learn how to join tables.

Join tables