Improve the query generated for many-to-many explicit/lazy load

[ajcvickers]

We made a patch fix to the query in #23589. This issue is now in 6.0 to add a cleaner, non-patch solution.

---

Original issue

The query must:

• Return the loaded entities
• Track the loaded entities
• Track the join entities associated with the loaded entities

This is what we currently generate:

DbSet<EntityOne>()
    .AsTracking()
    .Where(e => EF.Property<int>(e, "Id") == (int)ValueBuffer.get_Item(0))
    .SelectMany(e => e.TwoSkip)
    .Include(e => e.OneSkip
        .Where(e => EF.Property<int>(e, "Id") == (int)ValueBuffer.get_Item(0)))

Which translates to the following on SQL Server:

DECLARE @__p_0 int = 3;

SELECT [t].[Id], [t].[CollectionInverseId], [t].[Name], [t].[ReferenceInverseId], [e].[Id], [t].[OneId], [t].[TwoId], [t0].[OneId], [t0].[TwoId], [t0].[Id], [t0].[Name]
FROM [EntityOnes] AS [e]
INNER JOIN (
    SELECT [e0].[Id], [e0].[CollectionInverseId], [e0].[Name], [e0].[ReferenceInverseId], [j].[OneId], [j].[TwoId]
    FROM [JoinOneToTwo] AS [j]
    INNER JOIN [EntityTwos] AS [e0] ON [j].[TwoId] = [e0].[Id]
) AS [t] ON [e].[Id] = [t].[OneId]
LEFT JOIN (
    SELECT [j0].[OneId], [j0].[TwoId], [e1].[Id], [e1].[Name]
    FROM [JoinOneToTwo] AS [j0]
    INNER JOIN [EntityOnes] AS [e1] ON [j0].[OneId] = [e1].[Id]
    WHERE [e1].[Id] = @__p_0
) AS [t0] ON [t].[Id] = [t0].[TwoId]
WHERE [e].[Id] = @__p_0
ORDER BY [e].[Id], [t].[OneId], [t].[TwoId], [t].[Id], [t0].[OneId], [t0].[TwoId], [t0].[Id]

[smitpatel]

How about

DbSet<EntityOne>()
    .AsTracking()
    .Where(e => EF.Property<int>(e, "Id") == (int)ValueBuffer.get_Item(0))
    .Include(e => e.TwoSkip)
    .ToList()
    .SelectMany(e => e.TwoSkip)

Include will bring join entity. EntityOne should not materialize again if already tracked by context. And client side SelectMany will correct the returning shape.

[smitpatel]

Client side SelectMany is not possible since it returns IQueryable.

[ajcvickers]

Removing from milestone to use this to track better fix for #23475.