OData over Web API - how to query nested properties?

Question

I'm currently teaching myself OData but I've run into a situation I haven't been able to resolve. Either it's me misunderstanding the OData specs or I need to do something to make it work.

I have set up a small model of Books and Authors entities (EF/CF). Pretty simple stuff 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, when querying Authors I would like to be able to expand the Books property and filter on its (nested) properties. For example, if I ask "who wrote the Harry Potter books", like so ...

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)"
        }-
    }-
}

I realize I can get that from querying the Books entity ...

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

... but the problem I get comes from when I attempt reference to nested properties, no matter how I do it. The above query works, and presents the whole Author entity, 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)"
        }-
    }-
}

Here's 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, like I said, is there something else I need to add or configure to make references properties in related entities working?

Answer 1

Jonas, I hope you got there in the end :) For all those punters at home, Jonas has identified 2 issues:

1. How to Select an Entity if at least one of it's child items satisfies a criteria

2. How to expand child entities but only select specific columns in the expanded set

Answer 1: Use 'any' function to filter the authors to those who have a book that contains 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 operators that evaluate a Boolean expression on a collection. Both must be prepended with a navigation path that identifies a collection. The argument of a lambda operator is a lambda variable name followed by a colon (:) and a Boolean expression that uses the lambda variable name to refer to properties of the related entities identified by the navigation path.

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)

5.1.1.10.2 all

The all operator applies a Boolean expression to each member of a 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 Items with a Quantity greater than 100

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

Answer 2: Use nested '$select' inside the Books '$expand' statement to limit the columns that should be returned within the expansion

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

also works for the other example offered:

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

But these two queries are not quite the same, The first query will find the authors who wrote a book with 'Harry Potter' in the name, but the $expand will list all books that the author has written, even if 'Harry Potter' is not in the name.

this is not intended to be a complete result set, just an example to illustrate the point, please note The Tales of Beedle the Bard does not include the string Harry Potter in the name, but it is returned because the Author has written other books that do have Harry Potter in the name.

[ 
  { Name: "J K Rowling", Books: [ 
    { Name: "Harry Potter and the Philosopher's 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 the books in the database with 'Harry Potter' in the name regardless of the author, but will include the author's name:

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

So while OP has suggested that you can get similar data by changing primary controller to select from, the data is in a different shape and may contain redundant/replicated information, or may filter out rows that you were otherwise expecting. If you change the controller, you WILL be changing the resultant shape of the data, so don't make decisions like that in haste.

Note that while the OData v4 specification includes a number of different options for usage of $select and $expand, not all of these syntax options are supported by the ASP.Net Web API implementation provided via the official NuGet packages...

I'm not sure on the official reasoning, but so far I have not been disadvantaged (when it comes to nested $expand and $select) by this limited implementation of the specification

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