title: Filter data with EF Core in a single query
body: <p>I'm trying to filter data in ASP.NET Core 2.1 using EF Core DbContext. The scenario is as follows:
  Our main entity is a Movie. A movie has one or more genres and a genre can belong to one or more movies. The same goes for movie and actor. So we have two many-to-many relationships(code first) and the code that describes these is:</p>
  
  <pre><code>public class Movie
  {
      public int Id {get; set;}
      public string Title {get; set;}
      public ICollection&lt;MovieActor&gt; MovieActors {get; set;}
      public ICollection&lt;MovieGenre&gt; MovieGenres {get; set;}
  }
  
  public class Actor
  {
      public int Id {get; set;}
      public string Name {get; set;}
      public ICollection&lt;MovieActor&gt; MovieActors {get; set;}
  }
  
  public class Genre
  {
      public int Id {get; set;}
      public string Name {get; set;}
      public ICollection&lt;MovieGenre&gt; MovieGenres {get; set;}  
  }
  
  public class MovieActor 
  {
      public int MovieId {get; set;}
      public Movie Movie {get; set;}
  
      public int ActorId {get; set;}
      public Actor Actor {get; set;}  
  }
  
  public class MovieGenre
  {
      public int MovieId {get; set;}
      public Movie Movie {get; set;}
  
      public int GenreId {get; set;}
      public Genre Genre {get; set;}  
  }
  </code></pre>
  
  <p>The context responsible for handling queries with the database is MoviesDbContext. 
  I'm trying to filter all the data from the 'Movies' table based on two lists of ints, which represent the Ids of actors and genres in the database.</p>
  
  <pre><code>List&lt;int&gt; actorIds;
  List&lt;int&gt; genreIds;
  </code></pre>
  
  <p>For filtering, we want to get all movies that simultaneously follow the following rules:</p>
  
  <p>1) All movies whose list of actors contain at least one actor whose Id is found in the 'actorIds' list</p>
  
  <p>2) All movies whose list of genres contain at least one genre whose Id is found in the 'genreIds' list</p>
  
  <p>The solution that I found is as follows:</p>
  
  <pre><code>context.Movies
          .Include(m =&gt; m.MovieActors)
          .Include(m =&gt; m.MovieGenres)
          .Where(m =&gt; actorIds.Any(id =&gt; m.MovieActors.Any(ma =&gt; ma.Id == id)))
          .Where(m =&gt; genreIds.Any(id =&gt; m.MovieGenres.Any(mg =&gt; mg.Id == id)));
  </code></pre>
  
  <p>This does the filtering right, but the problem is that when EF Core translates the code into sql commands, it breaks the query into a lot of tiny queries and this causes severe performance issues, some queries taking tens of seconds.
  How can I refactor this so that it does the filtering in only one query?</p>