EF Core, projection sub collection eager loading

Question

In EF core I am trying to project out a resultset with a subcollection.

 ctx.As.Select(a => new
    {
        AId = a.Id,
        BNames = a.Bs.Select(x=>x.Name) //SubCollection
    })

However when this is executed the BNames collection is lazy, so when it gets enumerated it fires off a seperate query per each row. eg for 2 items in the B's collection.

[a].[Id] AS [AId] FROM [As] AS [a]

p_executesql N'SELECT [x].[Name]
FROM [Bs] AS [x]
WHERE @_outer_Id = [x].[AId]',N'@_outer_Id int',@_outer_Id=1

p_executesql N'SELECT [x].[Name]
FROM [Bs] AS [x]
WHERE @_outer_Id = [x].[AId]',N'@_outer_Id int',@_outer_Id=2

In EF6 the same query results in the following (as I would expect):

SELECT 
[Extent1].[Id] AS [Id], 
[Project1].[C1] AS [C1], 
[Project1].[Name] AS [Name]
FROM  [dbo].[A] AS [Extent1]
LEFT OUTER JOIN  (SELECT 
    [Extent2].[Name] AS [Name], 
    [Extent2].[A_Id] AS [A_Id], 
    1 AS [C1]
    FROM [dbo].[B] AS [Extent2] ) AS [Project1] ON [Extent1].[Id] = [Project1].[A_Id]
ORDER BY [Extent1].[Id] ASC, [Project1].[C1] ASC

How can I get EF to eager load the subcollection BNames?

---

Note: This is not the same problem as including sub entities with .Include as this is a custom projection, not a navigation property. Using .Include here results in an error.

Full repo code here: https://gist.github.com/lukemcgregor/692834629da09b21d5a35515e86c9002

Answer 1

This is possible in EF Core 2.1 using its correlated subquery optimisations.

You need to update to EF Core 2.1 and make a small modification to opt into the optimization by adding a .ToList() to the subquery:

foreach (var thing in ctx.As
    .Select(a => new
    {
        AId = a.Id,
        BNames = a.Bs.Select(x => x.Name).ToList()
    }))

See Optimization of correlated subqueries in New features in EF Core 2.1.