How do I use linq to do a WHERE against a collection object (using Netflix data source)

I am using LinqPad to learn Linq by querying the NetFlix OData source.

(BTW I know th开发者_如何学Goeir is a similar question already on SO...didn't help me).

Here is the query I got working which is awesome.

from x in Titles
//where x.Rating=="PG"
where x.Instant.Available==true
where x.AverageRating>=4.0
//where x.Rating.StartsWith("TV")
//where x.Genres.First (g => g.Name.Contains("Family") ) //(from y in Genres where y.Name.Contains("Family") select y)
//where x.Genres.First (g => g.Name=="")
//orderby x.Name
orderby x.AverageRating descending
//select x
//)
select new {x.Name, x.Rating, x.AverageRating, x.ShortSynopsis}

(Pardon all the comments...it is a testament to the fact I am experimenting and that I will change the query for various needs).

There are two thing I cannot figure out.

First. Let's say I only want to return the first 10 results.

Second (and most importantly). I want to filter by a partial string of the genre. Each title contains a Genres collection. I want to show only Genres where the Name contains a certain string (like "Family"). Even better filter using Titles where genre.name.contains "firstFilter" AND "secondFilter".

Basically, I want to filter by genre(s) and I cannot figure out how to do it since Title contains its own Genres collection and I cannot figure out how to return only title that are in one or more genres of the collection.

Thanks for your help!

ps...it seems that Netflix OData source does not support Any operator.

Seth

To return the first 10 results, surround your code above with parentheses and put a .Take(10) on the end

var foo = ( from x in Titles... ).Take(10);

There is no way to do take using query syntax in C# currently.

As for the genre filter, as klabranche points out, oData does not support many of the same Linq constructs you can use locally with a regular IEnumerable or IQueryable.

klabranche's solution doesn't support contains. It does however make 2 round trips to the server to get results. (see my comment on his answer as to why this seems necessary)

Here is an alternative which makes one roundtrip to the server to get data, then it processes that data locally. Because some of the query runs locally, you can use string.Contains, "or" clauses, and other goodness.

The downside of this approach is it retrieves more data over the wire than is needed to answer the query. On the other hand, it's easy to understand and it works.

When I combine "Family" and "Children", it returns 21 results.

var oDataQuery = from x in Titles
                 where x.AverageRating >= 4
                    && x.Instant.Available==true       
                 orderby x.AverageRating descending
                 select new {x.Name, x.Rating, x.AverageRating, x.ShortSynopsis, x.Genres};

var localQuery = from o in oDataQuery.ToList()
                 where o.Genres.Any(g => g.Name.Contains("Family"))
                    && o.Genres.Any(g => g.Name.Contains("Children"))
                 select new {o.Name, o.Rating, o.AverageRating, o.ShortSynopsis };

localQuery.Dump();

OData and the Netflix API support the Take() and Contains() methods:

from t in Titles
where t.Name.Contains("Matrix")
select t

(from t in Titles
where t.Name.Contains("Matrix")
select t).Take(10)

To get the first 10:

(from x in Titles
where x.Instant.Available==true 
where x.AverageRating>=4.0 
orderby x.AverageRating descending 
select new {x.Name, x.Rating, x.AverageRating, x.ShortSynopsis}
).Take(10)

To filter by a single genre (Not what you want...):

from g in Genres
from t in g.Titles 
where g.Name == "Horror" 
where t.Instant.Available==true
where t.AverageRating >=4.0
orderby t.AverageRating descending 
select new {t.Name, t.Rating, t.AverageRating, t.ShortSynopsis}

However, you wanted to have multiple genres BUT OData doesn't support Select Many queries which is why contains fails or trying to OR the Genre Name.

Below fails because contains returns many...

var q1 = from g in Genres
from t in g.Titles 
where g.Name.Contains("Horror")
where t.Instant.Available==true
where t.AverageRating >=4.0
orderby t.AverageRating descending 
select new {t.Name, t.Rating, t.AverageRating, t.ShortSynopsis};

To filter by multiple genres I found you can use a Concat or Union query (in LinqPad be sure to change to C# statements not expression):

var q1 = from g in Genres
from t in g.Titles 
where g.Name=="Horror"
where t.Instant.Available==true
where t.AverageRating >=4.0
orderby t.AverageRating descending 
select new {t.Name, t.Rating, t.AverageRating, t.ShortSynopsis};

var q2 = from g in Genres
from t in g.Titles 
where g.Name=="HBO"
where t.Instant.Available==true
where t.AverageRating >=4.0
orderby t.AverageRating descending 
select new {t.Name, t.Rating, t.AverageRating, t.ShortSynopsis};

var concat = q1.ToList().Concat(q2);
//var union = q1.Union(q2);

By unioning the two queries it will remove duplicates but these are what you want If I understand you correctly in that you want movies that are only in both genres?

In that case you will want to use Concat which will return all records.

Now you just need to find records that are in the query more than once and you have your results:

var results = from c in concat
group c by c.Name into grp
where grp.Count() > 1
select grp;