Normalization of array access to ElementAt breaks broke PG JSON array traversal

[roji]

#29656 added CollectionIndexerToElementAtNormalizingExpressionVisitor to the preprocessing phase; this visitor normalizes array and list indexing (e.g. arr[2], list[2]) to ElementAt method calls.

Unfortunately, this breaks JSON array traversal on the PG side. The normalization itself isn't the issue,

Input query (test JsonPocoQueryTest.Array_of_object):

var x = ctx.JsonbEntities.Single(e => e.Customer.Orders[0].Price == 99.5m);

WIth 8.0-preview.1, we get the following tree after preprocessing:

DbSet<JsonbEntity>()
    .Where(j => j.Customer.Orders
        .Select(s => s.Price)
        .ElementAt(0) == 99.5)
    .Single()

Note how the member access for Price gets pulled up as a Select before the ElementAt; I'm guessing this is maybe the doing of nav expansion. In any case, this tree no longer faithfully represents what's going on; do we translate this construct in the EF SQL Server JSON support?

/cc @maumar

[maumar]

The rewrite is done in SubqueryMemberPushdownExpressionVisitor.
It was done to support queries like this:

ss.Set<JsonEntityBasic>()
                .Select(x => new { x.Id, x.OwnedCollectionRoot[0].OwnedCollectionBranch[prm].OwnedCollectionLeaf })
                .AsNoTracking()

i.e. collection navigation after array access. Without the pushdown, nav expansion doesn't know how to wrap MaterializeCollectionNavigationExpression around the collection call, and we need that to properly reason about the results when processing the query further.

This is the query before nav expansion (but with pushdown):

DbSet<JsonEntityBasic>()
    .Select(x => new { 
        Id = x.Id, 
        OwnedCollectionLeaf = x.OwnedCollectionRoot
            .AsQueryable()
            .ElementAt(0).OwnedCollectionBranch
            .AsQueryable()
            .Select(s => s.OwnedCollectionLeaf)
            .ElementAt(__prm_0)
     })

this is after nav expansion

DbSet<JsonEntityBasic>()
    .Select(j => new { 
        Id = j.Id, 
        OwnedCollectionLeaf = EF.Property<List<JsonOwnedBranch>>(EF.Property<List<JsonOwnedRoot>>(j, "OwnedCollectionRoot")
            .AsQueryable()
            .ElementAt(0), "OwnedCollectionBranch")
            .AsQueryable()
            .Select(o0 => MaterializeCollectionNavigation(
                Navigation: JsonEntityBasic.OwnedCollectionRoot#JsonOwnedRoot.OwnedCollectionBranch#JsonOwnedBranch.OwnedCollectionLeaf,
                subquery: EF.Property<List<JsonOwnedLeaf>>(o0, "OwnedCollectionLeaf")
                    .AsQueryable()))
            .ElementAt(__prm_0)
     })

without the pushdown, the query would look something like this:

DbSet<JsonEntityBasic>()
    .Select(x => new { 
        Id = x.Id, 
        OwnedCollectionLeaf = x.OwnedCollectionRoot
            .AsQueryable()
            .ElementAt(0).OwnedCollectionBranch
            .AsQueryable()
            .ElementAt(__prm_0).OwnedCollectionLeaf
     })

and we would need to back-track to the outer select to apply MCNE

[maumar]

but the specific reason why JSON needed this change was when it array access chain ends in collection navigation (maybe a reference nav - need to double check). If that's not something that npg need to worry about, perhaps we could make the rewrite more targeted to that scenario?