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
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Users online (4,008)