Anyway to require EF Core to use two queries for related data instead of 1 or N+1?

Question

Simple scenario of Category and Product (one-to-many). Category has a Description field that may be a long text. Now I need to query a list of categories with all their products. Explicit loading documentation mentions two obvious approaches which are not good in my case (see below).

My approach

var categories = await dc.Categories
    .AsNoTracking()
    .ToListAsync();

var catDict = categories.ToDictionary(q => q.Id);
var catIds = catDict.Keys;

var products = await dc.Products
    .Where(q => catIds.Contains(q.CategoryId))
    .ToListAsync();

foreach (var product in products)
{
    // Category.Products is initialized with empty List already
    catDict[product.CategoryId].Products.Add(product);
}

This approach only needs two queries and do not have redundant data. I am still happy even if EF Core could pull a JOIN but select only Id of Category instead.

Is there any method/command/query in EF Core that asks it to query related data this way?

It should be similar to Explicit Loading but if there is a method that can apply to a list of entities:

    await context.Collection(categories) // instead of context.Entry(...) for a single entity
        .Collection(b => b.Products)
        .LoadAsync();

---

Other approaches mentioned:

1 query approach

var categories = await dc.Categories
    .AsNoTracking()
    .Include(q => q.Products)
    .ToListAsync();

This one generates only one DB call but there are too much redundant data:

N+1 queries approach

var categories = await dc.Categories
    .AsNoTracking()
    .ToListAsync();

foreach (var category in categories)
{
    category.Products = await dc.Products
        .Where(q => q.CategoryId == category.Id)
        .ToListAsync();
}

This approach removes redundant data but now there are N+1 DB calls.

Answer 1

I think you are looking for split queries: https://learn.microsoft.com/en-us/ef/core/querying/single-split-queries#split-queries

var categories = await dc.Categories
    .AsNoTracking()
    .Include(q => q.Products)
    .AsSplitQuery()
    .ToListAsync();