title: Error when replacing .ToList() calls in with an IQueryable
body: <p>I'm trying to improve the efficiency of this method and I can't figure out why my &quot;solution&quot; is throwing an error. Here's the existing and working, albeit slow, code:</p>
  <pre><code>public async Task&lt;ICollection&lt;Animal&gt;&gt; GetAnimalsAsync(AnimalFilters animalFilters)
  {
      
      var animalList = await db.Animal
          .Include(a =&gt; a.Zoos)
              .ThenInclude(z =&gt; z.Habitats)
          .ToListAsync();
  
      // returns the list of animals that:
      // a) have zoos
      // b) have zoos that have habitats matching the animalFilters.HabitatIds
      
      if (animalFilters.HabitatIds != null) 
      {
          animalList = animalList.Where(animal =&gt; animal.Zoos != null 
              &amp;&amp; animalFilters.HabitatIds.Intersect(animals.Zoos
              .Select(zoo =&gt; (Guid?) zoo.Habitats.Id)).Any()).ToList();
      }
      
      return animalList;  
  }
  </code></pre>
  <p>Now to improve efficiency, what I'd like to do is try to limit my <code>.ToList()</code> calls, so I wrote the following code. Essentially all I did was remove all the unnecessary ToList() calls (there are a lot more cases in the real code) and try to use an IQueryable to just make one long query that it only turned into a list at the very end. Here's the new code:</p>
  <pre><code>public async Task&lt;ICollection&lt;Animal&gt;&gt; GetAnimalsAsync(AnimalFilters animalFilters)
  {
      
      IQueryable&lt;Animal&gt; animalList = await db.Animal
          .Include(a =&gt; a.Zoos)
              .ThenInclude(f =&gt; f.Habitats);
  
      // returns the list of animals that:
      // a) have zoos
      // b) have zoos that have habitats matching the animalFilters.HabitatIds
      
      if (animalFilters.HabitatIds != null) 
      {
          animalList = animalList.Where(animal =&gt; animal.Zoos != null 
              &amp;&amp; animalFilters.HabitatIds.Intersect(animals.Zoos
              .Select(zoo =&gt; (Guid?) zoo.Habitats.Id)).Any());
      }
      
      var toReturn = await animalList.ToListAsync();
      return toReturn;    
  }
  </code></pre>
  <p>However, this throws an error:</p>
  <p><code>System.InvalidOperationException: The LINQ expression [...] could not be translated.</code></p>
  <p><code>Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.</code></p>
  <p>After doing some research, such as looking at the linked docs and <a href="https://stackoverflow.com/questions/36614461/how-to-reduce-the-execution-time-of-a-linq-search-query">this stackoverflow question</a>, I'm still not sure why the answer doesn't seem to work in my case.</p>