Linq request make a lot of queries to DB

Question

I have ef classes like this:

class Product 
{
  public int ProductId {set; get;}
...
 public List<ProductBannedIn> BannedIn;
}

public class ProductBannedIn
{
  public int ProductId {set; get;}
  public Product Product {set; get;}
  public int CountryId {set; get;}
  public Country Country {set; get;}
}

And want to make request like:

... //query - added some filters before
var products = query.Include(x => x.BannedIn)
    .Join(context.ProductTranslation
        .Where(x => x.LanguageId == language.LanguageId),
           product => product.ProductId,
              translation => translation.Product.ProductId,
                  (x,y) => new {
                      Id = x. ProductId,
                      Name = y.Name,
                      Description = y.Description,
                      Type = x.TypeId,
                      BannedIn = x.BannedIn.Select(b => b.CountryId),
                   }).ToList();

Problem: Problem is that when I fetch for example 1000 products this part

BannedIn = x.BannedIn.Select(b => b.CountryId)

make query for every row and its very slow. What I seen in debugger that for each product make query to fetch BannedIn, but should be already fetched cause I have Include

What need to be achieved:
In general should be like 1 query to db and not for each row (x.BannedIn)

Answer 1

This is a known (so called N + 1 query) issue with correlated subqueries in pre 2.1 EF Core versions. It's been fixed in 2.1 - see New features in EF Core 2.1 - Optimization of correlated subqueries:

We have improved our query translation to avoid executing "N + 1" SQL queries in many common scenarios in which the usage of a navigation property in the projection leads to joining data from the root query with data from a correlated subquery. The optimization requires buffering the results from the subquery, and we require that you modify the query to opt-in the new behavior.

So upgrade to the latest EF Core bits if possible and then "opt-in" for the optimization by adding .ToList() to the correlated subquery as explained in the documentation link:

BannedIn = x.BannedIn.Select(b => b.CountryId).ToList(),

The result will be execution of 2 SQL queries (this is how EF Core works with correlated collections - 1 SQL query per collection), but not 1 per each product as currently.