How to $filter on $expand ?

[francisharvey]

Hi,

I'm trying to filter the expanded records but it does not filter record or returns an error.

This looks like the issue here: #1162

This query does not filter records:
https://server/data/ProductMasters('2012-0000-300')?$expand=ProductMasterConfigurations($filter=ProductConfigurationId eq 'ConfigA')

It produce the same output as this one :
https://elmodemo2beeb4be27bb4432aos.cloudax.dynamics.com/data/ProductMasters('2012-0000-300')?$expand=ProductMasterConfigurations

Using this query,
https://server/data/ProductMasters('2012-0000-300')?$expand=ProductMasterConfigurations&$filter=ProductMasterConfigurations/any(pmc: pmc/ProductConfigurationId eq 'ConfigA')

I'm getting this error:
"Instance property 'ProductMasterConfigurations' is not defined for type 'Dynamics.AX.Application.EcoResProductMasterEntity'"

Stacktrace seems to point to OData code:
"System.Linq.Expressions.Expression.Property(Expression expression, String propertyName) at System.Web.OData.Query.Expressions.FilterBinder.CreatePropertyAccessExpression(Expression source, IEdmProperty property) at System.Web.OData.Query.Expressions.FilterBinder.BindAnyNode(AnyNode anyNode)"

Since I'm not familiar with OData, I did search for information on the internet:
https://stackoverflow.com/questions/9171310/odata-filter-with-items-in-a-expand
And related to the error : https://stackoverflow.com/questions/44679832/instance-property-is-not-defined-for-type-system-int64

I also tried with Linq and got the same error:
context.ProductMasters.Expand("ProductMasterConfigurations").Where(pm => pm.ProductNumber == "2012-0000-300" && pm.ProductMasterConfigurations.Any(pmc => pmc.ProductConfigurationId == "ConfigA"))

So i'm unsure of the synthax or method that I need to use for this, any pointer would be very appreciated.

Thanks a lot!
Francis Harvey

[godrage]

from c#/odata client point of view this works for me:

(DataServiceQuery<FIAB>)context.FIAB
                            .Expand("FIABEMs($select=FIABEM_NR,EMAIL_ADR)")
                            .Where(x => 
                                         x.FIABEMs.Any(w => w.EMAIL_ADR.ToLower().Contains(pi_FindTxt.ToLower()))
                                    );

although for performance reasons i've restructured all those calls.

the odata syntax ref (http://docs.oasis-open.org/odata/odata/v4.0/odata-v4.0-part2-url-conventions.html) says:

5.1.1.10.1 any
The any operator applies a Boolean expression to each member of a collection and returns true if the expression is true for any member of the collection, otherwise it returns false. The any operator without an argument returns true if the collection is not empty.

Example 79: all Orders that have any Items with a Quantity greater than 100

http://host/service/Orders?$filter=Items/any(d:d/Quantity gt 100)

my guess to your error might be an edm-model issue not finding your expanded type nested to your root object or perhaps try rebuilding the service reference

[francisharvey]

Hi godrage, thanks a lot for your answer :-)
i think you may be right pointing me the edm model, but i can't find what is missing or even what should be there...

I was able to reverse the query, asking to ProductMasterConfigurations collection for record filtering on its key and expanding to the ProductMaster like this:
https://server/data/ProductMasterConfigurations?$expand=ProductMaster&$filter=ProductConfigurationId%20eq%20%27ConfigA%27%20and%20ProductMaster/ProductNumber%20eq%20%271200-50-B-2%27

At least i'm able to get something but I'm affraid I will need to expand on more entities from the ProductMaster record, this was the main reason I tried querying the ProductMasters collection at first.

Following your advice, this is the error:
Instance property 'ProductMasterConfigurations' is not defined for type 'Dynamics.AX.Application.EcoResProductMasterEntity'

and I found this in the metadata file:

<EntitySet Name="ProductMasterConfigurations" EntityType="Microsoft.Dynamics.DataEntities.ProductMasterConfiguration"> <NavigationPropertyBinding Path="ProductMasterConfigurationTranslations" Target="ProductMasterConfigurationTranslations" /> <NavigationPropertyBinding Path="ProductMaster" Target="ProductMasters" /> </EntitySet>
and this from the ProductMaster definition
<NavigationProperty Name="ProductMasterConfigurations" Type="Collection(Microsoft.Dynamics.DataEntities.ProductMasterConfiguration)" Partner="ProductMaster"> <ReferentialConstraint Property="ProductNumber" ReferencedProperty="ProductMasterNumber" /> </NavigationProperty>

I'm using the query directly in the browser, so the 'client code' from my application is not used here.
But I did notice the type mismatch between the internal AX type and external dataentities type, could it be the cause of the issue?

If so, is there a way to fix it? I don't really have access to the metadata 'configuration'...

Thanks
Francis Harvey

[KenitoInc]

Closing this since we are tracking #1162