cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
openclue
Helper I
Helper I

WEB API - Filtering on nested navigation property

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"
    }

 

 

 

 

....

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
EricRegnier
Super User
Super User

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!

View solution in original post

6 REPLIES 6
rampprakash
Super User
Super User

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

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
Super User
Super User

@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

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
Super User
Super User

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
Helper I
Helper I

@EricRegnier @rampprakash @Prakash4691 

 

Thank you all.

 

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

 

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Users online (2,539)