Left join on included entities are duplicated if accessing getter property

[PolarizedIons]

Description

Originally reported to the Postgres provider repo here: npgsql/efcore.pg#2655

When doing an:

• include on a collection
• a join on that
• access a "computed" property on something in the join
• while also returning that "something" directly

The left join to get the include gets duplicated by N + 1 amount of times,
where N is the amount of computed properties

Issue

await db.FirstEntities
    .Include(x => x.ToInclude)
    .Join(
        db.SecondEntities,
        first => first.SecondEntityId,
        second => second.FirstEntityId,
        (first, second) => new {
            first,
            haha1 = first.ComputedProperty1,
            haha2 = first.ComputedProperty2,
            haha3 = first.ComputedProperty3,
        }
    )
    .ToListAsync();

produces

SELECT f."Id", f."SecondEntityId", s."Id", f0."Id", f0."Col1", f0."Col2", f0."Col3", f0."Col4", f0."Col5", f0."FirstEntityId", s."FirstEntityId", f1."Id", f1."Col1", f1."Col2", f1."Col3", f1."Col4", f1."Col5", f1."FirstEntityId", f2."Id", f2."Col1", f2."Col2", f2."Col3", f2."Col4", f2."Col5", f2."FirstEntityId", f3."Id", f3."Col1", f3."Col2", f3."Col3", f3."Col4", f3."Col5", f3."FirstEntityId"
FROM "FirstEntities" AS f
INNER JOIN "SecondEntities" AS s ON f."SecondEntityId" = s."FirstEntityId"
LEFT JOIN "FirstEntityIncludes" AS f0 ON f."Id" = f0."FirstEntityId"
LEFT JOIN "FirstEntityIncludes" AS f1 ON f."Id" = f1."FirstEntityId"
LEFT JOIN "FirstEntityIncludes" AS f2 ON f."Id" = f2."FirstEntityId"
LEFT JOIN "FirstEntityIncludes" AS f3 ON f."Id" = f3."FirstEntityId"
ORDER BY f."Id", s."Id", f0."Id", f1."Id", f2."Id"

Repo

Contains a readme with more info, and a very simple Program.cs file that shows the core issue.

https://github.com/PolarizedIons/PgEfCore.Bug.DuplicateSubqueries

Provider and version information

EF Core version:
Database provider: Npgsql.EntityFrameworkCore.PostgreSQL
Target framework: .NET 6.0
Operating system: NixOS
IDE: Rider 2022.3.2

[maumar]

related to #20291 (pending selector work)

[maumar]

this is the tree we end up after nav expansion:

DbSet<FirstEntity>()
    .Join(
        inner: DbSet<SecondEntity>(), 
        outerKeySelector: f => (object)f.SecondEntityId, 
        innerKeySelector: s => (object)s.FirstEntityId, 
        resultSelector: (f, s) => new TransparentIdentifier<FirstEntity, SecondEntity>(
            Outer = f, 
            Inner = s
        ))
    .Select(ti => new { 
        first = IncludeExpression(
            EntityExpression:
            ti.Outer, 
            NavigationExpression:
            MaterializeCollectionNavigation(
                Navigation: FirstEntity.ToInclude,
                subquery: DbSet<FirstEntityInclude>()
                    .Where(f0 => EF.Property<Guid?>(ti.Outer, "Id") != null && object.Equals(
                        objA: (object)EF.Property<Guid?>(ti.Outer, "Id"), 
                        objB: (object)EF.Property<Guid?>(f0, "FirstEntityId")))), ToInclude)
        , 
        haha1 = IncludeExpression(
            EntityExpression:
            ti.Outer, 
            NavigationExpression:
            MaterializeCollectionNavigation(
                Navigation: FirstEntity.ToInclude,
                subquery: DbSet<FirstEntityInclude>()
                    .Where(f1 => EF.Property<Guid?>(ti.Outer, "Id") != null && object.Equals(
                        objA: (object)EF.Property<Guid?>(ti.Outer, "Id"), 
                        objB: (object)EF.Property<Guid?>(f1, "FirstEntityId")))), ToInclude)
        .ComputedProperty1, 
        haha2 = IncludeExpression(
            EntityExpression:
            ti.Outer, 
            NavigationExpression:
            MaterializeCollectionNavigation(
                Navigation: FirstEntity.ToInclude,
                subquery: DbSet<FirstEntityInclude>()
                    .Where(f2 => EF.Property<Guid?>(ti.Outer, "Id") != null && object.Equals(
                        objA: (object)EF.Property<Guid?>(ti.Outer, "Id"), 
                        objB: (object)EF.Property<Guid?>(f2, "FirstEntityId")))), ToInclude)
        .ComputedProperty2, 
        haha3 = IncludeExpression(
            EntityExpression:
            ti.Outer, 
            NavigationExpression:
            MaterializeCollectionNavigation(
                Navigation: FirstEntity.ToInclude,
                subquery: DbSet<FirstEntityInclude>()
                    .Where(f3 => EF.Property<Guid?>(ti.Outer, "Id") != null && object.Equals(
                        objA: (object)EF.Property<Guid?>(ti.Outer, "Id"), 
                        objB: (object)EF.Property<Guid?>(f3, "FirstEntityId")))), ToInclude)
        .ComputedProperty3
     })

because of computed columns (client eval) we process each column projection as separate subquery with include, which then get converted into those superfluous joins during ApplyProjection phase.