OData filtering before selecting inside an expand query

user_7219158 0
Viewed 3346

I am writing a query for metadata in Dynamics 365, but I think this problem could be universal for any OData query.

My problem is as follow:

In Dynamics, we can have multiple types of fields in entities, such as strings, bools or lookup. I want to write a query that returns the type of entity the lookup is addressing.

{url}/api/data/v9.0/EntityDefinitions(LogicalName='Account')
?$select=
    LogicalName
&$expand=
    Attributes(
        $select=
            LogicalName,
            AttributeType,
            Targets; -- Problematic property
        $filter=
      AttributeType+eq+Microsoft.Dynamics.CRM.AttributeTypeCode'Lookup')

If I dont include the select for Targets in this query, I get the correct result, being all attributes where theyr AttributeType is Lookup.

But when I require the Targets to be aslo included, I get an error message 

Could not find a property named 'Targets' on type 'Microsoft.Dynamics.CRM.AttributeMetadata

Because the Target property only exists on those arrribute that are of the type Lookup, therefore selecting this column from a string attribute will fail and throw this error.

Is there a way to first filter to lookups before selecting a column? I have found here that the order of evaluation is

$filter, $inlinecount, $orderby, $skiptoken, $skip, $top, $expand, $select, $format

which is exactly what I need, except I don't think this order is the same when invoked inside the $expand attribute.

odata-v4 dynamics-crm odata query-string rest
2 Answers

For getting the lookup entities you might have better luck doing an $expand on ManyToOneRelationships instead of Attributes and getting the ReferencedEntity value.

ManyToOne

Something like this should work:
.../api/data/v9.1/EntityDefinitions(LogicalName='account') ?$select=LogicalName &$expand=ManyToOneRelationships($select=ReferencingAttribute,ReferencedEntity)

Subset of results:

{"@odata.context":"https://myOrg.api.crm.dynamics.com/api/data/v9.1/$metadata#EntityDefinitions(LogicalName,ManyToOneRelationships(ReferencingAttribute,ReferencedEntity))/$entity","LogicalName":"account","MetadataId":"70816501-edb9-4740-a16c-6a5efbc05d84","ManyToOneRelationships":[{"ReferencingAttribute":"msdyn_accountkpiid","ReferencedEntity":"msdyn_accountkpiitem","MetadataId":"2a712c96-09b1-e811-a842-000d3a33bdbd"},{"ReferencingAttribute":"preferredequipmentid","ReferencedEntity":"equipment","MetadataId":"b4b462b5-ee78-467d-a97a-45264d234816"},{"ReferencingAttribute":"primarycontactid","ReferencedEntity":"contact","MetadataId":"410707b1-9554-4cd9-8437-6608b1802904"},{"ReferencingAttribute":"masterid","ReferencedEntity":"account","MetadataId":"51fa4af7-93d0-4f06-8949-38a0036ddc64"},{"ReferencingAttribute":"preferredsystemuserid","ReferencedEntity":"systemuser","MetadataId":"a6b48e23-fada-4b7f-8655-530bba050765"},{"ReferencingAttribute":"createdbyexternalparty","ReferencedEntity":"externalparty","MetadataId":"9967fe7d-84ee-4a26-9ad7-a8fdbdfa2316"},{"ReferencingAttribute":"modifiedby","ReferencedEntity":"systemuser","MetadataId":"8be02a9d-0776-4c76-b35f-1c92dd791d9e"},{"ReferencingAttribute":"parentaccountid","ReferencedEntity":"account","MetadataId":"57511732-b553-4cfb-bcf2-d280f9f8c6f1"},{"ReferencingAttribute":"entityimageid","ReferencedEntity":"imagedescriptor","MetadataId":"5b4942d5-1fcd-49ca-91c0-2737f5f104f3"},

Also, for reference, I tried doing an $expand on Attributes AND Targets:
../api/data/v9.1/EntityDefinitions(LogicalName='account')?$select=LogicalName&$expand=Attributes($filter=AttributeType+eq+Microsoft.Dynamics.CRM.AttributeTypeCode%27Lookup%27&$expand=Targets)

It throws an error:

"Query option '$expand' was specified more than once, but it must be specified at most once."

user_44165120

It is possible to expand Attributes and ManyToOneRelationships in the same call if you separate them by a comma.

GET https://{{baseUrl}}/api/data/v9.1/EntityDefinitions(LogicalName='account')?$select=LogicalName,EntitySetName&$expand=Attributes($select=LogicalName),ManyToOneRelationships($select=ReferencingAttribute,ReferencedEntity)
user_101413830
Related