none
OData v4 navigation property drill down doesn't work as expected RRS feed

  • Question

  • Hello,

    I experience a problem when drilling down to related entities via 1..* navigation properties with the latest Power Query version which now supports OData v4 feeds. For some reason, instead of returning entities that are related to a parent record, Power Query returns all records from a related entity set. For example, when I click on products table link on a manufacturer record, I would expect to see only products related to that manufacturer but instead I see all products. 

    I suspect there may be something missing in the service metadata document (below) but can't figure out what it is. I will gladly provide sample Web API project if required but wanted to check first if anyone else observed the same behavior.

    Thanks in advance for your help!

    Cheers,

    Alex

    <?xml version="1.0" encoding="utf-8"?>
    <edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
    <edmx:DataServices>
    <Schema Namespace="DemoApi.Core.Model" xmlns="http://docs.oasis-open.org/odata/ns/edm">
    <EntityType Name="product" OpenType="true">
    <Key>
    <PropertyRef Name="id" />
    </Key>
    <Property Name="id" Type="Edm.Int32" Nullable="false" />
    <Property Name="name" Type="Edm.String" />
    <Property Name="description" Type="Edm.String" />
    <Property Name="catalog_number" Type="Edm.String" />
    <Property Name="price" Type="Edm.Decimal" />
    <Property Name="manufacturer_id" Type="Edm.Int32" />
    <NavigationProperty Name="manufacturer" Type="DemoApi.Core.Model.manufacturer" />
    </EntityType>
    <EntityType Name="manufacturer" OpenType="true">
    <Key>
    <PropertyRef Name="id" />
    </Key>
    <Property Name="id" Type="Edm.Int32" Nullable="false" />
    <Property Name="name" Type="Edm.String" />
    <Property Name="phone" Type="Edm.String" />
    <Property Name="email" Type="Edm.String" />
    <NavigationProperty Name="products" Type="Collection(DemoApi.Core.Model.product)" />
    </EntityType>
    <EntityType Name="customer" OpenType="true">
    <Key>
    <PropertyRef Name="id" />
    </Key>
    <Property Name="id" Type="Edm.Int32" Nullable="false" />
    <Property Name="name" Type="Edm.String" />
    <Property Name="phone" Type="Edm.String" />
    <Property Name="email" Type="Edm.String" />
    <Property Name="customer_number" Type="Edm.String" />
    <NavigationProperty Name="users" Type="Collection(DemoApi.Core.Model.user)" />
    </EntityType>
    <EntityType Name="user" OpenType="true">
    <Key>
    <PropertyRef Name="id" />
    </Key>
    <Property Name="id" Type="Edm.Int32" Nullable="false" />
    <Property Name="customer_id" Type="Edm.Int32" />
    <Property Name="name" Type="Edm.String" />
    <Property Name="email" Type="Edm.String" />
    <Property Name="first_name" Type="Edm.String" />
    <Property Name="last_name" Type="Edm.String" />
    <NavigationProperty Name="customer" Type="DemoApi.Core.Model.customer" />
    </EntityType>
    <EntityType Name="Address" OpenType="true">
    <Key>
    <PropertyRef Name="Id" />
    </Key>
    <Property Name="Id" Type="Edm.Int32" Nullable="false" />
    <Property Name="Line1" Type="Edm.String" />
    <Property Name="City" Type="Edm.String" />
    <Property Name="PostalCode" Type="Edm.String" />
    <Property Name="Country" Type="Edm.String" />
    </EntityType>
    <ComplexType Name="Phone">
    <Property Name="CountryCode" Type="Edm.Int32" Nullable="false" />
    <Property Name="Number" Type="Edm.String" />
    <Property Name="Extension" Type="Edm.String" />
    </ComplexType>
    </Schema>
    <Schema Namespace="Functions" xmlns="http://docs.oasis-open.org/odata/ns/edm">
    <Function Name="MostExpensive" IsBound="true">
    <Parameter Name="bindingParameter" Type="Collection(DemoApi.Core.Model.product)" />
    <ReturnType Type="Collection(DemoApi.Core.Model.product)" />
    </Function>
    <EntityContainer Name="Container">
    <EntitySet Name="products" EntityType="DemoApi.Core.Model.product">
    <NavigationPropertyBinding Path="manufacturer" Target="manufacturers" />
    </EntitySet>
    <EntitySet Name="manufacturers" EntityType="DemoApi.Core.Model.manufacturer">
    <NavigationPropertyBinding Path="products" Target="products" />
    </EntitySet>
    <EntitySet Name="customers" EntityType="DemoApi.Core.Model.customer">
    <NavigationPropertyBinding Path="users" Target="users" />
    </EntitySet>
    <EntitySet Name="users" EntityType="DemoApi.Core.Model.user">
    <NavigationPropertyBinding Path="customer" Target="customers" />
    </EntitySet>
    <EntitySet Name="addresses" EntityType="DemoApi.Core.Model.Address" />
    </EntityContainer>
    </Schema>
    </edmx:DataServices>
    </edmx:Edmx>

    Wednesday, May 20, 2015 10:30 PM

Answers

All replies

  • Hi Alex,

    Can you look at fiddler while performing the query in PowerQuery to see what requests gets sent to your service? I would imagine something like "/manufacturers/?$expand Products"

    Generally, the results displayed are the reflection of the payload we got from the service. You might want to look at that payload in fiddler and see if the data match what you see in PQ, in which case, you should look at the service to see why is it returning this payload.


    Thanks, Hadeel

    Thursday, May 21, 2015 9:27 PM
  • Hi, Hadeel:

    Thank you for your reply! I captured Power Query sessions in Fiddler when clicking on related products link and here is what I see:

    1. First, it requests the service document: http://localhost:3006/odata 
    2. Second, it requests the service metadata document:  http://localhost:3006/odata/$metadata 
    3. Third, it requests top 2 manufacturers filtered by PK: http://localhost:3006/odata/manufacturers?$filter=id%20eq%201&$top=2 
    4. And finally it requests all products: http://localhost:3006/odata/products

    I don't see anything like http://localhost:3006/odata/manufacturers(1)/products - this is what one would expect when clicking on a manufacturer's products link, correct?

    I can confirm that both: http://localhost:3006/odata/manufacturers(1)?$expand=products and http://localhost:3006/odata/manufacturers(1)/products are valid routes and return correct results in the browser. I can also expand products for all manufacturers http://localhost:3006/odata/manufacturers?$expand=products w/o problems. 

    Please let me know if there is anything else I should be looking at or you need more information.

    Thanks again for the help!

    Alex

    Friday, May 22, 2015 12:20 AM
  • Hi Alex,

    What's the query you see in the PQ when you click on 'Advanced Editor'?


    Thanks, Hadeel

    Friday, May 22, 2015 12:23 AM
  • let
        Source = OData.Feed("http://localhost:3006/odata"),
        manufacturers_table = Source{[Name="manufacturers",Signature="table"]}[Data],
        #"1" = manufacturers_table{[id=1]}[products]
    in
        #"1"

    Thanks!

    Alex


    Friday, May 22, 2015 12:50 AM
  • Alright, I tried that query with a service where I have access and it works as expected. In fiddler, when we issue a request to:  http://localhost:3006/odata/manufacturers?$filter=id%20eq%201&$top=2  can you look at the json payload coming back and confirm what's the @odata.id url coming back from the service for id =1?

    Thanks, Hadeel

    Friday, May 22, 2015 1:05 AM
  • Thanks again, Hadeel.

    You are right, @odata.id is not present. In fact, it's a little surprising that in order to drill down a navigation, you would need @odata.id instance annotation. According to OData Json format specification, @odata.id is optional when minimal metadata is requested and only must be there if full metadata is requested or entity-id is not identical to the canonical URL of the entity-id.  "If odata.metadata=minimal is specified and the odata.id is not present in the entity then the canonical URL MUST be used as the entity-id."

    I will verify that adding @odata.id instance annotation solves the problem and get back with you. But if it indeed works that means developers who use ASP.NET Web API OData will have to customize code (and it's not that easy to add instance annotations in the current implementation of OData for Web API) to make their services work with Power Query. My suggestion would be to change Power Query to use canonical URL for entity-id if @odata.id is not present in the payload. This will make it compliant with the spec. Just a suggestion...

    Thanks,

    Alex

     


    Friday, May 22, 2015 1:05 PM
  • Hi, Hadeel:

    I have added @odata.id instance annotations (in fact I added them all :)) and still no luck - it just pulls all the products no matter what. :( Do you have any other suggestions for me to try?

    Thanks,

    Alex

    Friday, May 22, 2015 3:33 PM
  • Hmmm, nothing that I can figure out without a copy of your service to be able to debug it locally. If there is no sensitive data, could you click the feedback button and send a frown attaching the service and a reference to that thread. I will debug it as soon as I get it :)

    Thanks, Hadeel

    Friday, May 22, 2015 5:44 PM
  • Gladly... can you point me to where this feedback button is? :)
    Friday, May 22, 2015 5:59 PM
  • In PowerQuery tab there is a Help button, in the drop down --> Send Feedback --> Send a Frown / Smile :) Also, there is a very useful tool from OData team to validate services: http://services.odata.org/validation/# In case you want to  give it a try.

    Thanks, Hadeel


    Friday, May 22, 2015 6:01 PM
  • OK, the feedback email is on its way... I added link to this thread. It also has quite a big attachment due to many external references in that demo project but hopefully it'll get through. Let me know when you get it.

    Thanks and have a good weekend!

    Alex

    Friday, May 22, 2015 6:14 PM
  • Hi Korygin,

    Unfortunately this is a bug on our side. The fix should be available in our next release J

    Thanks, Hadeel

    Thursday, May 28, 2015 12:56 AM