Select items from list using many ids

Question

The target is to get list of 10 popular movies.

There is a table called populars in the database that contains: moviecount - the number of times the film was rented. MovieID - id of the film.

There's also a table called Movies that contains all the data of the movies. This table contains a field for MovieID. The tables are not related and should not be linked between them.

I took the 10 id's of the most popular films

var TopTen = videoLibDB.populars
                 .Take(10)
                 .OrderBy(e => e.movieCount)
                 .Select(e => new { e.MovieID });

This is ok, but how do I create a List of the 10 best movies from "movies" table by using 10 MovieIDs of the populars table(the TopTen of the code above)?

In case of one id I can compare the moiveid from the popular table to the movieid in the movies table.

You would do a `join` in LINQ, just as you would do a `join` in SQL. — Servy, Sep 18 '12 at 18:33
btw your TopTen takes first 10, and THEN sorts them.. it should be done the other way around — Michal Klouda, Sep 18 '12 at 18:34
http://stackoverflow.com/questions/37324/what-is-the-syntax-for-an-inner-join-in-linq-to-sql — Shyju, Sep 18 '12 at 18:36

Jim Wooley · Answer 1 · 2012-09-18T18:58:41.927

2

First, make sure to sort (OrderBy) before take. In LINQ to SQL/EF, this may not be significant, but with LINQ to Objects it will result in only the first ten rows being evaluated.

Second, you need to either join your populars with the movies, or use the association between the tables. Assuming you have an association set-up, you should be able to do something like the following:

var TopTen = videoLibDB.populars
             .OrderByDescending(e => e.movieCount)
             .Take(10)
             .Select(e => new { e.MovieID, e.Movie.MovieName});

edited Sep 18 '12 at 18:58

answered Sep 18 '12 at 18:36

Jim Wooley

10,169
1
25
43

Should probably be OrderByDescending. – Joachim Isaksson Sep 18 '12 at 18:47
The tables are not related and cannnot be linked between them – Amir Cabili Sep 18 '12 at 18:47
@JoachimIsaksson Thanks for the catch. Corrected in the answer. – Jim Wooley Sep 18 '12 at 18:58
@AmirCabili Are the tables in the same database? If so, you can use assocations in your model even if they aren't in the database with either LINQ to SQL or EF. If you don't want to use associations, you can use a join. If they are not in the same database, you will need to fetch the values and then join them via LINQ to Objects in the client or use Contains if you know the in memory collection contains less than 2000 records. Please clarify where the requirement "cannot be linked between them" came from. – Jim Wooley Sep 18 '12 at 19:01

score 2 · Answer 2 · answered Sep 18 '12 at 18:43

If you have the relations set up correctly, @JimWooley's answer above is the easiest/best, this query is in case you don't have that possibility.

You have some problems in your linq query, you're taking 10 random movies and ranking only them, least popular first. This query fixes that and joins it with the movie table to get the most popular movies;

var query = 
    from movie in videoLibDB.movies
    where
        videoLibDB.populars
        .OrderByDescending(x => x.movieCount)   // Sort all, most popular first
        .Take(10)                               // but only keep the 10 first
        .Select(x => x.MovieID)                 // Take their MovieID 
        .Contains(movie.MovieID)                // and get movie data on them
    select movie;

Mike Perrenoud · Answer 3 · 2012-09-18T18:42:12.510

0

Try this, it should generate an IN clause.

var results = from q in videoLibDB.movies
              where videoLibDB.populars
                    .OrderBy(e => e.movieCount)
                    .Take(10)
                    .Select(e => e.MovieID)
                    .ToArray()
                    .Contains(q.MovieID)
              select q

And here is an article that goes more in depth.

edited Sep 18 '12 at 18:42

answered Sep 18 '12 at 18:36

Mike Perrenoud

66,820
29
157
232

score 0 · Answer 4 · answered Sep 18 '12 at 18:37

Use .Contains

First create an array containing the IDs....

var ids = new[] { 1,2,3,4,5,6,7,8,9,10 };

Then use .Contains

var TopTen = videoLibDB.populars
    .Where(e => ids.Contains(e.MovieID)
    .OrderBy(e => e.movieCount)
    .Select(e => new { e.MovieID });

It's the equivalent of an IN statement in T-SQL

score 0 · Answer 5 · answered Sep 18 '12 at 18:43

i don't see why you have that restriction that the tables may not be linked ... but hey...

var TopTen = videoLibDB.populars
       .OrderBy(e => e.movieCount)
       .Take(10)
       .Select(e => e.MovieID).ToArray();

var YourActualMovies = videoLibDB.movies
       .Where(x=>TopTen.Conatins(x.MovieID))
       .Select(...whatever you want...)

Select items from list using many ids

5 Answers5