I've a query which should count all the movies which contain the given genres.

The query works but it query's the link table for each movie in the database which results in a 1 minute and 6 second load time.

The table structure looks like the following:

**Movie**
Contains all the movies

**Genre**
Contains all the names of the genres

**MovieGenre**
Each record in this table contains a movie id and genre name.

```
 public async Task<int> CountMovies(List<string> pGenres)
    {
        return await (
            from movie in Context.Movie
            join genre in Context.MovieGenre on movie.MovieId equals genre.MovieId into genres
            where pGenres.All(pGenre => genres.Any(genre => genre.GenreName == pGenre))
            select movie
        ).CountAsync();
    }

```

How can I prevent round trips to the server and do everything in the database?

The code above results in 108 round trips to the database, for example:

```
SELECT [genre0].[genre_name],[genre0].[movie_id]
FROM [Movie_Genre] AS [genre0]
WHERE 353 /* @_outer_MovieId */ = [genre0].[movie_id]

```