views:

88

answers:

3

Hello

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

(BTW I know their 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

+3  A: 

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();
Slaggg
Slaggg, absolutely fantastic answer. Answered my two questions I asked and a third question I didn't ask. The multi-"where" statements were bugging me but not anymore. Great job.
Seth Spearman
Linqpad is reporting a Not Supported Exception for the Any operator. I guess that the Netflix OData source does not support it. Do you know of another way of doing the same thing without the Any operator?
Seth Spearman
klabranche makes the great point that odata doesn't support everything we're used to locally. I've reworked the answer to work within the confines of odata.
Slaggg
@Slaggg - Good job. I am learning LINQ atm and figured there was a better way. Glad to see I helped you get there. :-)
klabranche
+1  A: 

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)
Dave Swersky
Technically, so does every IEnumerable and IQueryable...
Slaggg
True, though I think some OData implementations restrict some of the take/skip and contains to keep queries from affecting performance.
Dave Swersky
Interesting - I did not know that.
Slaggg
You are Containing on the Name of the movie. He wanted on the genre...
klabranche
+1  A: 

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;
klabranche
When I try to run this in LinqPad, I get "The method 'Concat' is not supported."
Slaggg
I can get it to run by running each query separately, and doing the concat in memory - changing the "var concat" line to "var concat = q1.ToList().Concat(q2.ToList());". The query as written returns 0 rseults.
Slaggg
It's working for me (actually did the above in LinqPad). I am on the latest linqPad version. You?
klabranche
Huh. I'm v4.26.2. Out of curiosity, how many odata queries show on the sql tab? I'm only able to get this approach to run with 2 separate queries.
Slaggg
try var concat = q1.ToList().Concat(q2); concat.Dump();
klabranche
klabranche
That works - adding the .ToList() to one of the queries. It's still 2 roundtrips however (see the SQL tab). I have an idea below on how to get to 1 rountrip (although it will return more data in the single RT)
Slaggg