Is there a way to specify that I only want to return the first record (or last) of a expanded set of child records using odata?
http://myurl/odata/ParenTable?$count=true&$filter=(Id eq 123456)&$expand=ChildTable($orderby=AddedTimeStamp desc;$top=1)
This is what I am attempting but it returns the message
The query specified in the URI is not valid
Your URL convention is compliant to OData v4 for the behaviour you are expecting.
11.2.4.2.1 Expand Options
The set of expanded entities can be further refined through the application of expand options, expressed as a semicolon-separated list of system query options, enclosed in parentheses.Allowed system query options are
$filter
,$select
,$orderby
,$skip
,$top
,$count
,$search
,$expand
, and$levels
.
But there are older versions and proprietary implementations that are known to not support all or in some cases any of these options like filtering or limiting ($skip
,$top
) expressions within the $expand
query option.
.Net implementations do not support
$search
OOTB, the API author must manually implement the query option.
That specific error is generally an indicator that the path component, not the query is invalid as most OData runtimes will return more descriptive error response when the resource or collection was correctly resolved but the query could not be parsed or executed. In this case I suspect you have anonymized the path, so we can only speculate, for instance there is an obvious potential typo in the documented path, there is a 't' missing have you tried:
http://myurl/odata/ParentTable?$count=true&$filter=(Id eq 123456)&$expand=ChildTable($orderby=AddedTimeStamp desc;$top=1)
or is the resource pluralised:
http://myurl/odata/ParenTables?$count=true&$filter=(Id eq 123456)&$expand=ChildTable($orderby=AddedTimeStamp desc;$top=1)
http://myurl/odata/ParentTables?$count=true&$filter=(Id eq 123456)&$expand=ChildTable($orderby=AddedTimeStamp desc;$top=1)
You should include an example of the URL that does work, try without the $top
and without the $orderby
clauses within the expansion clause. We need to eliminate the errors related to a bad path, vs a bad query.
If you do this via Postman, you can then update your question and post the entire response content.
Both the current ASP.Net and ASP.Net Core implementations do support this, if you are the author of the API please include your controller implementation and the version of the framework you are using so we can assist in greater detail.
If your API does not support this, then given that you are limiting to the $top=1
you could invert the request and use the Child
collection resource instead:
Assuming that ~/ChildTable
is the route to the ChildTable
referred to in your example expansion
http://myurl/odata/ChildTable?$filter=ParentTable/Id eq 123456&$orderby=AddedTimeStamp desc&$top=1&$expand=ParentTable