Error when replacing .ToList() calls in with an IQueryable

Question

I'm trying to improve the efficiency of this method and I can't figure out why my "solution" is throwing an error. Here's the existing and working, albeit slow, code:

public async Task<ICollection<Animal>> GetAnimalsAsync(AnimalFilters animalFilters)
{
    
    var animalList = await db.Animal
        .Include(a => a.Zoos)
            .ThenInclude(z => 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 => animal.Zoos != null 
            && animalFilters.HabitatIds.Intersect(animals.Zoos
            .Select(zoo => (Guid?) zoo.Habitats.Id)).Any()).ToList();
    }
    
    return animalList;  
}

Now to improve efficiency, what I'd like to do is try to limit my .ToList() 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:

public async Task<ICollection<Animal>> GetAnimalsAsync(AnimalFilters animalFilters)
{
    
    IQueryable<Animal> animalList = await db.Animal
        .Include(a => a.Zoos)
            .ThenInclude(f => 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 => animal.Zoos != null 
            && animalFilters.HabitatIds.Intersect(animals.Zoos
            .Select(zoo => (Guid?) zoo.Habitats.Id)).Any());
    }
    
    var toReturn = await animalList.ToListAsync();
    return toReturn;    
}

However, this throws an error:

System.InvalidOperationException: The LINQ expression [...] could not be translated.

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.

After doing some research, such as looking at the linked docs and this stackoverflow question, I'm still not sure why the answer doesn't seem to work in my case.

Answer 1

Try the following realization:

public async Task<ICollection<Animal>> GetAnimalsAsync(AnimalFilters animalFilters)
{
    
    IQueryable<Animal> animalList = db.Animal
        .Include(a => a.Zoos)
            .ThenInclude(f => 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 => animalFilters.HabitatIds.Contains(
            animals.Zoos.SelectMany(zoo => zoo.Habitats).Select(h => h.Id)));
    }
    
    var toReturn = await animalList.ToListAsync();
    return toReturn;    
}