CODEWITHSUNDEEP
c#linq
Amir Cabili
Amir Cabili

Reputation: 21

Select items from list using many ids

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.

Upvotes: 2

Views: 3098

Answers (5)

Jim Wooley
Jim Wooley

Reputation: 10398

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

Upvotes: 2

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

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;

Upvotes: 2

DarkSquirrel42
DarkSquirrel42

Reputation: 10257

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...)

Upvotes: 0

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

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.

Upvotes: 0

Antony Scott
Antony Scott

Reputation: 21978

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

Upvotes: 0

Related Questions