Request all titles by actor using LINQ to Netflix OData

Question

I'm experimenting with LINQPad to use LINQ to query the Netflix OData. I'm trying to search for all films with a particular actor in. For example:

from t in Titles 
from p in t.Cast
where p.Name == "Morgan Freeman"
select t.Name

this results in:

NotSupportedException: Can only project the last entity type in the query being translated

I also tried:

from p in People
from t in p.TitlesActedIn
where p.Name == "Morgan Freeman"
select t.Name

which results in the following error:

NotSupportedException: The method 'Select' is not supported

I've tried a few other approaches, such as using Id's in the where clause, and selecting different things, but have got nowhere.

Answer 1

You can do either this:

from p in People
where p.Id == 190
from t in p.TitlesActedIn
select new { Name = t.Name }

But note that this requires you not specify the Id, this translates to: /People(190)/TitlesActedIn?$select=Name

If you need to filter based on non-key properties, you need to do something like:

from p in People
where p.Name == "Morgan Freeman"
select new Person {
    TitlesActedIn = p.TitlesActedIn
}

This translates to: /People?$filter=Name eq 'Morgan Freeman'&$expand=TitlesActedIn

You could also just ask for the names of those titles, but LinqPad doesn't seem to have a way to do that, due to the type of properties it generates. It would look like:

from p in People
where p.Name == "Morgan Freeman"
select new Person {
    TitlesActedIn = p.TitlesActedIn.Select(t => new Title { Name = t.Name })
}

Which would translate to: /People?$filter=Name eq 'Morgan Freeman'&$expand=TitlesActedIn&$select=TitlesActedIn/Name

Thanks, Vitek Karas [MSFT]