OData over Web API - how to request nested properties?

Question

I am currently teaching OData, but I came across a situation that I could not solve. Either I misunderstand the OData specifications, or I need to do something to make it work.

I created a small object model for books and authors (EF / CF). Pretty simple things with a one-to-many relationship from author to books:

modelBuilder.Entity<Book>().HasRequired(b => b.Author); modelBuilder.Entity<Author>().HasMany(a => a.Books); 

Now, at the request of the authors, I would like to be able to extend the Books property and filter its (nested) properties. For example, if I ask "who wrote the Harry Potter books," like this ...

 http://myBooksDatabase/Authors?$expand=Books&$filter=contains(Books/Name,'Harry Potter')&$select=Name 

... I get this error response:

 { error: { code: "" message: "The query specified in the URI is not valid. The parent value for a property access of a property 'Name' is not a single value. Property access can only be applied to a single value." innererror: { message: "The parent value for a property access of a property 'Name' is not a single value. Property access can only be applied to a single value." type: "Microsoft.OData.Core.ODataException" stacktrace: " at Microsoft.OData.Core.UriParser.Parsers.EndPathBinder.BindEndPath(EndPathToken endPathToken) at Microsoft.OData.Core.UriParser.Parsers.MetadataBinder.BindEndPath(EndPathToken endPathToken) at Microsoft.OData.Core.UriParser.Parsers.MetadataBinder.Bind(QueryToken token) at Microsoft.OData.Core.UriParser.Parsers.MetadataBinder.BindFunctionParameter(FunctionParameterToken token) at Microsoft.OData.Core.UriParser.Parsers.MetadataBinder.Bind(QueryToken token) at Microsoft.OData.Core.UriParser.Parsers.FunctionCallBinder.<BindFunctionCall>b__8(FunctionParameterToken ar) at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at Microsoft.OData.Core.UriParser.Parsers.FunctionCallBinder.BindFunctionCall(FunctionCallToken functionCallToken) at Microsoft.OData.Core.UriParser.Parsers.MetadataBinder.BindFunctionCall(FunctionCallToken functionCallToken) at Microsoft.OData.Core.UriParser.Parsers.MetadataBinder.Bind(QueryToken token) at Microsoft.OData.Core.UriParser.Parsers.FilterBinder.BindFilter(QueryToken filter) at Microsoft.OData.Core.UriParser.ODataQueryOptionParser.ParseFilterImplementation(String filter, ODataUriParserConfiguration configuration, IEdmType elementType, IEdmNavigationSource navigationSource) at Microsoft.OData.Core.UriParser.ODataQueryOptionParser.ParseFilter() at System.Web.OData.Query.FilterQueryOption.get_FilterClause() at System.Web.OData.Query.Validators.FilterQueryValidator.Validate(FilterQueryOption filterQueryOption, ODataValidationSettings settings) at System.Web.OData.Query.FilterQueryOption.Validate(ODataValidationSettings validationSettings) at System.Web.OData.Query.Validators.ODataQueryValidator.Validate(ODataQueryOptions options, ODataValidationSettings validationSettings) at System.Web.OData.Query.ODataQueryOptions.Validate(ODataValidationSettings validationSettings) at System.Web.OData.EnableQueryAttribute.ValidateQuery(HttpRequestMessage request, ODataQueryOptions queryOptions) at System.Web.OData.EnableQueryAttribute.ExecuteQuery(Object response, HttpRequestMessage request, HttpActionDescriptor actionDescriptor) at System.Web.OData.EnableQueryAttribute.OnActionExecuted(HttpActionExecutedContext actionExecutedContext)" }- }- } ODataQueryOptions options, ODataValidationSettings validationSettings) at System.Web.OData.Query.ODataQueryOptions.Validate (ODataValidationSettings { error: { code: "" message: "The query specified in the URI is not valid. The parent value for a property access of a property 'Name' is not a single value. Property access can only be applied to a single value." innererror: { message: "The parent value for a property access of a property 'Name' is not a single value. Property access can only be applied to a single value." type: "Microsoft.OData.Core.ODataException" stacktrace: " at Microsoft.OData.Core.UriParser.Parsers.EndPathBinder.BindEndPath(EndPathToken endPathToken) at Microsoft.OData.Core.UriParser.Parsers.MetadataBinder.BindEndPath(EndPathToken endPathToken) at Microsoft.OData.Core.UriParser.Parsers.MetadataBinder.Bind(QueryToken token) at Microsoft.OData.Core.UriParser.Parsers.MetadataBinder.BindFunctionParameter(FunctionParameterToken token) at Microsoft.OData.Core.UriParser.Parsers.MetadataBinder.Bind(QueryToken token) at Microsoft.OData.Core.UriParser.Parsers.FunctionCallBinder.<BindFunctionCall>b__8(FunctionParameterToken ar) at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at Microsoft.OData.Core.UriParser.Parsers.FunctionCallBinder.BindFunctionCall(FunctionCallToken functionCallToken) at Microsoft.OData.Core.UriParser.Parsers.MetadataBinder.BindFunctionCall(FunctionCallToken functionCallToken) at Microsoft.OData.Core.UriParser.Parsers.MetadataBinder.Bind(QueryToken token) at Microsoft.OData.Core.UriParser.Parsers.FilterBinder.BindFilter(QueryToken filter) at Microsoft.OData.Core.UriParser.ODataQueryOptionParser.ParseFilterImplementation(String filter, ODataUriParserConfiguration configuration, IEdmType elementType, IEdmNavigationSource navigationSource) at Microsoft.OData.Core.UriParser.ODataQueryOptionParser.ParseFilter() at System.Web.OData.Query.FilterQueryOption.get_FilterClause() at System.Web.OData.Query.Validators.FilterQueryValidator.Validate(FilterQueryOption filterQueryOption, ODataValidationSettings settings) at System.Web.OData.Query.FilterQueryOption.Validate(ODataValidationSettings validationSettings) at System.Web.OData.Query.Validators.ODataQueryValidator.Validate(ODataQueryOptions options, ODataValidationSettings validationSettings) at System.Web.OData.Query.ODataQueryOptions.Validate(ODataValidationSettings validationSettings) at System.Web.OData.EnableQueryAttribute.ValidateQuery(HttpRequestMessage request, ODataQueryOptions queryOptions) at System.Web.OData.EnableQueryAttribute.ExecuteQuery(Object response, HttpRequestMessage request, HttpActionDescriptor actionDescriptor) at System.Web.OData.EnableQueryAttribute.OnActionExecuted(HttpActionExecutedContext actionExecutedContext)" }- }- } 

I understand that I can get this from a request to the Books object ...

 http://myBooksDatabase/Books?$expand=Author&$filter=contains(Name,'Harry') 

... but the problem I get occurs when I try to reference nested properties, no matter how I do it. The above request works and represents the entire Author object, but if I add &$select=Author/Name , I get the following response:

 { error: { code: "" message: "The query specified in the URI is not valid. Found a path with multiple navigation properties or a bad complex property path in a select clause. Please reword your query such that each level of select or expand only contains either TypeSegments or Properties." innererror: { message: "Found a path with multiple navigation properties or a bad complex property path in a select clause. Please reword your query such that each level of select or expand only contains either TypeSegments or Properties." type: "Microsoft.OData.Core.ODataException" stacktrace: " at Microsoft.OData.Core.UriParser.Visitors.SelectPropertyVisitor.ProcessTokenAsPath(NonSystemToken tokenIn) at Microsoft.OData.Core.UriParser.Visitors.SelectPropertyVisitor.Visit(NonSystemToken tokenIn) at Microsoft.OData.Core.UriParser.Syntactic.NonSystemToken.Accept(IPathSegmentTokenVisitor visitor) at Microsoft.OData.Core.UriParser.Parsers.SelectBinder.Bind(SelectToken tokenIn) at Microsoft.OData.Core.UriParser.Parsers.SelectExpandBinder.Bind(ExpandToken tokenIn) at Microsoft.OData.Core.UriParser.Parsers.SelectExpandSemanticBinder.Bind(IEdmStructuredType elementType, IEdmNavigationSource navigationSource, ExpandToken expandToken, SelectToken selectToken, ODataUriParserConfiguration configuration) at Microsoft.OData.Core.UriParser.ODataQueryOptionParser.ParseSelectAndExpandImplementation(String select, String expand, ODataUriParserConfiguration configuration, IEdmStructuredType elementType, IEdmNavigationSource navigationSource) at Microsoft.OData.Core.UriParser.ODataQueryOptionParser.ParseSelectAndExpand() at System.Web.OData.Query.Validators.SelectExpandQueryValidator.Validate(SelectExpandQueryOption selectExpandQueryOption, ODataValidationSettings validationSettings) at System.Web.OData.Query.SelectExpandQueryOption.Validate(ODataValidationSettings validationSettings) at System.Web.OData.Query.Validators.ODataQueryValidator.Validate(ODataQueryOptions options, ODataValidationSettings validationSettings) at System.Web.OData.Query.ODataQueryOptions.Validate(ODataValidationSettings validationSettings) at System.Web.OData.EnableQueryAttribute.ValidateQuery(HttpRequestMessage request, ODataQueryOptions queryOptions) at System.Web.OData.EnableQueryAttribute.ExecuteQuery(Object response, HttpRequestMessage request, HttpActionDescriptor actionDescriptor) at System.Web.OData.EnableQueryAttribute.OnActionExecuted(HttpActionExecutedContext actionExecutedContext)" }- }- } ExpandToken expandToken, SelectToken selectToken, ODataUriParserConfiguration configuration) at Microsoft.OData.Core.UriParser.ODataQueryOptionParser.ParseSelectAndExpandImplementation (String select, String expand, ODataUriParserConfiguration configuration, IEdmStructuredType elementType, IEdmNavigationSource navigationSource { error: { code: "" message: "The query specified in the URI is not valid. Found a path with multiple navigation properties or a bad complex property path in a select clause. Please reword your query such that each level of select or expand only contains either TypeSegments or Properties." innererror: { message: "Found a path with multiple navigation properties or a bad complex property path in a select clause. Please reword your query such that each level of select or expand only contains either TypeSegments or Properties." type: "Microsoft.OData.Core.ODataException" stacktrace: " at Microsoft.OData.Core.UriParser.Visitors.SelectPropertyVisitor.ProcessTokenAsPath(NonSystemToken tokenIn) at Microsoft.OData.Core.UriParser.Visitors.SelectPropertyVisitor.Visit(NonSystemToken tokenIn) at Microsoft.OData.Core.UriParser.Syntactic.NonSystemToken.Accept(IPathSegmentTokenVisitor visitor) at Microsoft.OData.Core.UriParser.Parsers.SelectBinder.Bind(SelectToken tokenIn) at Microsoft.OData.Core.UriParser.Parsers.SelectExpandBinder.Bind(ExpandToken tokenIn) at Microsoft.OData.Core.UriParser.Parsers.SelectExpandSemanticBinder.Bind(IEdmStructuredType elementType, IEdmNavigationSource navigationSource, ExpandToken expandToken, SelectToken selectToken, ODataUriParserConfiguration configuration) at Microsoft.OData.Core.UriParser.ODataQueryOptionParser.ParseSelectAndExpandImplementation(String select, String expand, ODataUriParserConfiguration configuration, IEdmStructuredType elementType, IEdmNavigationSource navigationSource) at Microsoft.OData.Core.UriParser.ODataQueryOptionParser.ParseSelectAndExpand() at System.Web.OData.Query.Validators.SelectExpandQueryValidator.Validate(SelectExpandQueryOption selectExpandQueryOption, ODataValidationSettings validationSettings) at System.Web.OData.Query.SelectExpandQueryOption.Validate(ODataValidationSettings validationSettings) at System.Web.OData.Query.Validators.ODataQueryValidator.Validate(ODataQueryOptions options, ODataValidationSettings validationSettings) at System.Web.OData.Query.ODataQueryOptions.Validate(ODataValidationSettings validationSettings) at System.Web.OData.EnableQueryAttribute.ValidateQuery(HttpRequestMessage request, ODataQueryOptions queryOptions) at System.Web.OData.EnableQueryAttribute.ExecuteQuery(Object response, HttpRequestMessage request, HttpActionDescriptor actionDescriptor) at System.Web.OData.EnableQueryAttribute.OnActionExecuted(HttpActionExecutedContext actionExecutedContext)" }- }- } , ODataValidationSettings validationSettings) at System.Web.OData.Query.SelectExpandQueryOption.Validate (ODataValidationSettings validationSettings) at System.Web.OData.Query { error: { code: "" message: "The query specified in the URI is not valid. Found a path with multiple navigation properties or a bad complex property path in a select clause. Please reword your query such that each level of select or expand only contains either TypeSegments or Properties." innererror: { message: "Found a path with multiple navigation properties or a bad complex property path in a select clause. Please reword your query such that each level of select or expand only contains either TypeSegments or Properties." type: "Microsoft.OData.Core.ODataException" stacktrace: " at Microsoft.OData.Core.UriParser.Visitors.SelectPropertyVisitor.ProcessTokenAsPath(NonSystemToken tokenIn) at Microsoft.OData.Core.UriParser.Visitors.SelectPropertyVisitor.Visit(NonSystemToken tokenIn) at Microsoft.OData.Core.UriParser.Syntactic.NonSystemToken.Accept(IPathSegmentTokenVisitor visitor) at Microsoft.OData.Core.UriParser.Parsers.SelectBinder.Bind(SelectToken tokenIn) at Microsoft.OData.Core.UriParser.Parsers.SelectExpandBinder.Bind(ExpandToken tokenIn) at Microsoft.OData.Core.UriParser.Parsers.SelectExpandSemanticBinder.Bind(IEdmStructuredType elementType, IEdmNavigationSource navigationSource, ExpandToken expandToken, SelectToken selectToken, ODataUriParserConfiguration configuration) at Microsoft.OData.Core.UriParser.ODataQueryOptionParser.ParseSelectAndExpandImplementation(String select, String expand, ODataUriParserConfiguration configuration, IEdmStructuredType elementType, IEdmNavigationSource navigationSource) at Microsoft.OData.Core.UriParser.ODataQueryOptionParser.ParseSelectAndExpand() at System.Web.OData.Query.Validators.SelectExpandQueryValidator.Validate(SelectExpandQueryOption selectExpandQueryOption, ODataValidationSettings validationSettings) at System.Web.OData.Query.SelectExpandQueryOption.Validate(ODataValidationSettings validationSettings) at System.Web.OData.Query.Validators.ODataQueryValidator.Validate(ODataQueryOptions options, ODataValidationSettings validationSettings) at System.Web.OData.Query.ODataQueryOptions.Validate(ODataValidationSettings validationSettings) at System.Web.OData.EnableQueryAttribute.ValidateQuery(HttpRequestMessage request, ODataQueryOptions queryOptions) at System.Web.OData.EnableQueryAttribute.ExecuteQuery(Object response, HttpRequestMessage request, HttpActionDescriptor actionDescriptor) at System.Web.OData.EnableQueryAttribute.OnActionExecuted(HttpActionExecutedContext actionExecutedContext)" }- }- } 

Here are my two OData controllers for authors and books:

 namespace My.OData.Controllers { public class AuthorsController : ODataController { // GET /Author [EnableQuery] public IQueryable<Author> Get() { return MediaContext.Singleton.Authors; } // GET /Authors(<key>) [EnableQuery] public SingleResult<Author> Get([FromODataUri] Guid key) { var result = MediaContext.Singleton.Authors.Where(b => b.Id == key); return SingleResult.Create(result); } // GET /Authors(<key>)/Books [EnableQuery] public IQueryable<Book> GetBooks([FromODataUri] Guid key) { return MediaContext.Singleton.Authors.Where(a => a.Id == key).SelectMany(author => author.Books); } } public class BooksController : ODataController { // GET /Books [EnableQuery] public IQueryable<Book> Get() { return MediaContext.Singleton.Books; } // GET /Books(<key>) [EnableQuery] public SingleResult<Book> Get([FromODataUri] Guid key) { var result = MediaContext.Singleton.Books.Where(b => b.Id == key); return SingleResult.Create(result); } // GET /Books(<key>)/Author [EnableQuery] public SingleResult<Author> GetAuthor([FromODataUri] Guid key) { return SingleResult.Create(MediaContext.Singleton.Books.Where(b => b.Id == key).Select(b => b.Author)); } } } 

So, as I said, is there anything else I need to add or configure to create link properties in related objects?

Answer 1

Jonas, I hope you got there :) For all these players at home, Jonas identified 2 questions:

• How to select an object if at least one of its children satisfies the criteria

• How to expand child objects, but only select specific columns in an expanded set

Answer 1: Use the "any" function to filter authors by those who have a book containing the string "Harry Potter" in the name of the book

 http://myBooksDatabase/Authors?$filter=Books/any(b:contains(b/Name,'Harry Potter'))&$select=Name 

REF: http://docs.oasis-open.org/odata/odata/v4.0/errata03/os/complete/part2-url-conventions/odata-v4.0-errata03-os-part2-url-conventions- complete.html

5.1.1.10 Lambda operators

OData defines two statements that evaluate a Boolean expression in a collection. Both must be added using the navigation path that identifies the collection. The argument of the lambda operator is the name of the lambda variable, followed by a colon (:) and a Boolean expression that uses the name of the lambda variable to refer to the properties of related objects identified by the navigation path.

5.1.1.10.1 any

Any operator applies a logical expression for each member of the collection and returns true if the expression is true for any member of the collection, otherwise it returns false. Any statement without an argument returns true if the collection is not empty.

Example 79: all Orders that have any items with a quantity of more than 100 http: // host / service / Orders ? $ filter = Items / any (d: d / Quantity gt 100)

5.1.1.10.2 all

The all operator applies a boolean expression to each member of the collection and returns true if the expression is true for all members of the collection, otherwise it returns false. Example 80: all orders that have only elements with quantities greater than 100

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

Answer 2: Use the nested '$ select' inside the Books $ expand statement to restrict the columns to be returned within the extension

 http://myBooksDatabase/Authors?$filter=Books/any(b:contains(b/Name,'Harry Potter'))&$select=Name&$expand=Books($select=Name,ISBN) 

also works for another suggested example:

 http://myBooksDatabase/Books?$expand=Author($select=Name)&$filter=contains(Name,'Harry')&$select=Name,ISBN 

But these two questions are not exactly the same. The first query will find the authors who wrote the book with "Harry Potter" in the title, but $ expand will list all the books that the author wrote, "Harry Potter" is not in the title.

this is not intended to be a complete set of results, just an example illustrating the point, pay attention to “Tales of the Beadle Bard” does not include the line “Harry Potter” in the title, but it comes back because the author wrote other books that have Harry Potter in the title.

 [ { Name: "JK Rowling", Books: [ { Name: "Harry Potter and the Philosopher Stone", ISBN: "9781408855652" }, { Name: "The Tales of Beedle the Bard", ISBN: "9780747599876" }, { Name: "Harry Potter and the Cursed Child - Parts I and II", ISBN: "9780751565355" } ] }, { Name: "Bruce Foster", Books: [ { Name: "Harry Potter: A Pop-Up Book: Based on the Film Phenomenon", ISBN: "9781608870080" } ]} ] 

The second query will return all books in the database with "Harry Potter" in the name, regardless of the author, but will include the name of the author:

 [ { Name: "Harry Potter and the Philosopher Stone", ISBN: "9781408855652", Author: { Name: "JK Rowling" } }, { Name: "Harry Potter and the Cursed Child - Parts I and II", ISBN: "9780751565355", Author: { Name: "JK Rowling" } }, { Name: "Harry Potter: A Pop-Up Book: Based on the Film Phenomenon", ISBN: "9781608870080", Author: { Name: "Bruce Foster" } } ] 

Thus, although the OP suggested that you can get similar data by changing the primary controller to select, the data will have a different shape and may contain redundant / replicated information or may filter out rows that you expected otherwise. If you change the controller, you will change the resulting data form, so do not make such decisions in a hurry.

Note that although the OData v4 specification includes many different options for using $ select and $ expand, not all of these syntax parameters are supported by the ASP.Net Web API implementation provided through official NuGet packages ...

I'm not sure about the official reasoning, but so far I have not been at a disadvantage (when it comes to nested $ expand and $ select) thanks to this limited implementation of the specification

The examples offered in this solution were tested against Microsoft.AspNet.OData v5.6.0 - 5.9.1