locked
Why doesn't this select work? RRS feed

Answers

  • Well - what would it mean for Products/Name == "abc" ?

    It's like in C#, if you have a property Products which is of type List<Product> you can't do Products == "abc", doesn't make sense :-)

    Guessing that you might mean two things:

    1) Get me all Suppliers and for them all their Products, but only those products which name is not "abc". This is currently not possible in OData. The $filter only applies to the top-level entity set, so in the above example the Suppliers. If $expand is used, those expanded collections will always be retrieved as a whole. The feature request to support filters on expanded collections has been around for a while, but it wasn't important enough to support just yet.

    2) Get me all Suppliers which don't have the product called "abc". This is possible in OData V3 using the all operator:

    http://services.odata.org/V3/OData/OData.svc/Suppliers?$expand=Products&$select=Name,Products/Name&$filter=Products/all(p: p/Name ne 'abc')

    Thanks,


    Vitek Karas [MSFT]

    • Marked as answer by DavidThielen Friday, October 12, 2012 4:23 PM
    Friday, October 12, 2012 2:45 PM
    Moderator
  • It means slightly different thing:

    $filter and also $orderby (and $top and $skip, basically everything except $expand and $select) only applies to the results returned by the top-level entity set. So in the above example you can only order and filter the Suppliers. The expanded Products will always come unfiltered and ordered as per the server default (if at all).

    On the other hand, the expressions in $filter can use navigation properties. So it is OK to say ~/Products?$filter=Category/Name eq 'food' and it means "Get me all the products which category is called food.". Using the any/all operators you can even use collection navigation properties (as shown above). But the filter will only be used to filter the top-level, even though it may use the expanded stuff to do so. (Note that the navigation property doesn't have to be expanded in order for filter to use it).

    It is also possible to use navigation properties in $orderby, but I would discourage it. Some servers might not support it and even though who do would likely produce rather expensive queries to do so.

    In your PS, the filter applies to Products. Navigations in the path part of the URI are effectively invisible for the query options (it also would not make sense to filter the Categories, since you already picked exactly one of them anyway).

    Thanks,


    Vitek Karas [MSFT]

    • Marked as answer by DavidThielen Friday, October 12, 2012 4:23 PM
    Friday, October 12, 2012 4:17 PM
    Moderator

All replies

  • Well - what would it mean for Products/Name == "abc" ?

    It's like in C#, if you have a property Products which is of type List<Product> you can't do Products == "abc", doesn't make sense :-)

    Guessing that you might mean two things:

    1) Get me all Suppliers and for them all their Products, but only those products which name is not "abc". This is currently not possible in OData. The $filter only applies to the top-level entity set, so in the above example the Suppliers. If $expand is used, those expanded collections will always be retrieved as a whole. The feature request to support filters on expanded collections has been around for a while, but it wasn't important enough to support just yet.

    2) Get me all Suppliers which don't have the product called "abc". This is possible in OData V3 using the all operator:

    http://services.odata.org/V3/OData/OData.svc/Suppliers?$expand=Products&$select=Name,Products/Name&$filter=Products/all(p: p/Name ne 'abc')

    Thanks,


    Vitek Karas [MSFT]

    • Marked as answer by DavidThielen Friday, October 12, 2012 4:23 PM
    Friday, October 12, 2012 2:45 PM
    Moderator
  • I think this is the key issue:

    The $filter only applies to the top-level entity set

    If I understand this right, then the filter can only be on properties that are part of the single root entity set (the resource?) in the uri request. So we can $expand and $select from multipe entity sets, but only filter on one entity set.

    Is that correct?

    Also, what about $orderby - is it restricted to the root entity set too?

    thanks - dave

    ps - I am using the term resource as defined in OData Resource Path - and for the uri http://services.odata.org/OData/OData.svc/Category(1)/Products?$top=2&$orderby=name does that limit filters to Products, or to both Category & Products?



    Who will win The International Collegiate Programming Championships?


    Friday, October 12, 2012 3:09 PM
  • It means slightly different thing:

    $filter and also $orderby (and $top and $skip, basically everything except $expand and $select) only applies to the results returned by the top-level entity set. So in the above example you can only order and filter the Suppliers. The expanded Products will always come unfiltered and ordered as per the server default (if at all).

    On the other hand, the expressions in $filter can use navigation properties. So it is OK to say ~/Products?$filter=Category/Name eq 'food' and it means "Get me all the products which category is called food.". Using the any/all operators you can even use collection navigation properties (as shown above). But the filter will only be used to filter the top-level, even though it may use the expanded stuff to do so. (Note that the navigation property doesn't have to be expanded in order for filter to use it).

    It is also possible to use navigation properties in $orderby, but I would discourage it. Some servers might not support it and even though who do would likely produce rather expensive queries to do so.

    In your PS, the filter applies to Products. Navigations in the path part of the URI are effectively invisible for the query options (it also would not make sense to filter the Categories, since you already picked exactly one of them anyway).

    Thanks,


    Vitek Karas [MSFT]

    • Marked as answer by DavidThielen Friday, October 12, 2012 4:23 PM
    Friday, October 12, 2012 4:17 PM
    Moderator
  • That all makes sense. I wish you could filter on the $expand items but I can understand that limitation.

    One last clarification on this. When you have $filter=Category/Name - that is filtering Category(1)/Products/Category/Name, not Category(1)/Name. In other words, it is filtering off of a child (via a navigation) of the Category(1)/Products.

    So it is anything you can get to from the resource. But navigation links off the resource let you filter other entity sets.

    ??? - thanks - dave


    Who will win The International Collegiate Programming Championships?

    Friday, October 12, 2012 4:23 PM
  • Correct, up until the "But navigation links off the resource let you filter other entity sets.". I don't understand what that means... sorry.

    Thanks,


    Vitek Karas [MSFT]

    Friday, October 12, 2012 4:32 PM
    Moderator
  • I may be mis-phrasing. By navigation link I mean Category in Category(1)/Products/Category/Name. Category is not a property of Products, it is a navigation link to the child Category for a Product.

    What is the correct way to phrase that?

    thanks - dave


    Who will win The International Collegiate Programming Championships?

    Friday, October 12, 2012 4:34 PM
  • I did understand that part, what I didn't understand is "let you filter other entity sets". Maybe if you included a small sample... I might need the sample and a coffee to start my brain (currently in Redmond, so it's morning for me).

    Thanks,


    Vitek Karas [MSFT]

    Friday, October 12, 2012 4:48 PM
    Moderator
  • When I worked at Microsoft (Redmond) I spent 6 months working in the Far East and flying back and forth - that jet lag will wipe you out. Also, while you are in Redmond, you must go to Kidd Valley - best burgers in Seattle.

    When I said other EntitySets, I mean the Category in Category(1)/Products/Category. That is a child of Products but it is an entity set.

    ??? - thanks - dave


    Who will win The International Collegiate Programming Championships?

    Friday, October 12, 2012 7:10 PM