WEB API - Filtering on nested navigation property

[openclue]

Hi,

Having such of Dataverse table model:

EntityA--navigation property x -->EntityX--navigation property y-->EntityY with column 'address'

is it possible to query for EntityA but filter on address from EntityY.

I tried this way:

...&$filter=x\y\name eq 'some value' 

or 

...&$filter=contains(x\y\name, 'some value')

In both cases I get an error:

 "error": {
        "code": "0x80040216",
        "message": "entityRelationshipRole for given navigation property not found"
    }

....

[rampprakash]

Hello @openclue,

You can try below code

https://[base URL]/api/data/v9.1/Entity1/
?$expand=Entity2($filter=Entity2FieldName eq fieldValue;$select=Entity2FieldNames)

Please Mark as Answer if it is helpful and provide Kudos

Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA

Blog : https://microsoftcrmtechie.blogspot.com

[openclue]

Hello @rampprakash 

I've tried it before. 

This will filter the "entities2" to those meeting the filter condition but return all "entities1" (this will filter the entities2 to those meeting the condition but return all entities1 with some nulls).

BTW - it's easy to filter by entity2 fields - I need to filter out entities1 by entitys3 attribute value:

(Entity1-->Entity2-->Entity3.FieldName)

Let's say we have:

Order->OrderLine->Product

I need a way to query for orders with the specified barcode (Product's field).

[Prakash4691]

@openclue 

I think multi level expand is possible only for entity which has lookup that starts with N:1. Included reference link below,

https://docs.microsoft.com/en-us/powerapps/developer/data-platform/webapi/retrieve-related-entities-...

If you want to navigate from 1:N only one level of depth is possible. You can find examples from above link.

Are you trying to connect dataverse from client? If not, I would recommend to use fetchXML.

If it answers your question, kindly give kudo and accept it as solution.

Regards,

Prakash

[rampprakash]

Hi @openclue,

Cool then we need to use multiple web API calls to make it work is that okay for you?

Else we can use FetchXml calls what you prefer ?

[EricRegnier · Accepted solution]

Hi @openclue,

Unfortunately with OData don't support "inner join" type of behavior like in SQL so you won't be able to filter entity A with entity C filter. There are 2 lambda expressions available (any and all), but you can't use them in an $expand. The only option is to use FetchXml with link-entity in Web API. Here's an example: 

<fetch>  
   <entity name='entityA'>  
      <attribute name='columnA'/>
      <link-entity name='entityB' alias='entityB' to='entityBid' from='entityAid' link-type='inner'>  
         <attribute name='columnB'/>  
        <link-entity name='entityC' alias='entityC' to='entityCid' from='entityBid' link-type='inner'>  
         <attribute name='columnC'/> 
        </link-entity>  
      </link-entity>  
   </entity>  
 </fetch>

 then after encoded it pass it as a fetchXml= parameter.

[Organization URI]/api/data/v9.2/accounts?fetchXml=%3Cfetch%20mapping%3D%27logical%27%3E%3Centity%20name%3D%27account%27%3E%3Cattribute%20name%3D%27accountid%27%2F%3E%3Cattribute%20name%3D%27name%27%2F%3E%3Cattribute%20name%3D%27accountnumber%27%2F%3E%3C%2Fentity%3E%3C%2Ffetch%3E

More info: https://docs.microsoft.com/powerapps/developer/data-platform/webapi/retrieve-and-execute-predefined-... 
Hope this helps!

[openclue]

@EricRegnier @rampprakash @Prakash4691 

Thank you all.

I need to take a closer look at fetchXml then...