Too many columns selected for collection subquery

[InspiringCode]

Wenn doing a collection subquery, several ID columns of the related entities are also selected instead of only the used ones.

For example:

var query = db
    .Users
    .Select(x => new
    {
        Id = x.Id,
        DefaultLanguageCode = x.DefaultLanguageCode,
        Permissions = x.AssignedPermissions.Select(x => x.BusinessKey).ToArray(),
    });

Generates:

SELECT [u].[Id], [u].[DefaultLanguageCode], [t].[BusinessKey], [t].[UserId], [t].[PermissionId], [t].[Id]
FROM [adm].[User] AS [u]
LEFT JOIN (
    SELECT [p].[BusinessKey], [u0].[UserId], [u0].[PermissionId], [p].[Id]
    FROM [adm].[UserPermission] AS [u0]
    INNER JOIN [adm].[Permission] AS [p] ON [u0].[PermissionId] = [p].[Id]
) AS [t] ON [u].[Id] = [t].[UserId]
ORDER BY [u].[Id], [t].[UserId], [t].[PermissionId]

But I would expect it to generate:

SELECT [u].[Id], [u].[DefaultLanguageCode], [t].[BusinessKey]
FROM [adm].[User] AS [u]
LEFT JOIN (
    SELECT [p].[BusinessKey]
    FROM [adm].[UserPermission] AS [u0]
    INNER JOIN [adm].[Permission] AS [p] ON [u0].[PermissionId] = [p].[Id]
) AS [t] ON [u].[Id] = [t].[UserId]
ORDER BY [u].[Id]

Which could be further simplified to (but this might maybe perform worse, I didn't test):

SELECT [u].[Id], [u].[DefaultLanguageCode], [p].[BusinessKey]
FROM [adm].[User] AS [u]
LEFT JOIN[adm].[UserPermission] AS [u0] ON [u0].[UserId] = [u].Id
LEFT JOIN [adm].[Permission] AS [p] ON [u0].[PermissionId] = [p].[Id]
ORDER BY [u].[Id]

[roji]

I agree that we're projecting unneeded columns here, and this is indeed a result of the collection projection.

@maumar does this sound familiar to you (any other issue tracking this)?

[maumar]

These extra column projections are used as identifiers, so we know how to bucket the results. Related issue: https://github.com/dotnet/efcore/issues/32329

[roji]

Makes sense - closing as duplicate.

[roji]

Duplicate of #32329