[Regression] "Table not found with alias" for column access inside subquery predicate

[slubowsky]

Have code working up to EF 8.0.11. After upgrading to EF 9 new exception is thrown. Code to reproduce is shown below (its cut out of a much more complex real use case, kept chopping down to this which I hope is small enough for you to see the issue)

Using VS 17.13.0 Preview 1.0 on Window 11. Works with EF 8.0.11 fails with following exception EF 9.0

System.InvalidOperationException
  HResult=0x80131509
  Message=Table not found with alias 'd'
  Source=Microsoft.EntityFrameworkCore.Relational
  StackTrace:
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.GetTable(ColumnExpression column)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ApplyPredicate(SqlExpression sqlExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateWhere(ShapedQueryExpression source, LambdaExpression predicate)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelectMany(ShapedQueryExpression source, LambdaExpression collectionSelector, LambdaExpression resultSelector)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutorExpression[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass11_0`1.<ExecuteCore>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteCore[TResult](Expression query, Boolean async, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.<ToListAsync>d__67`1.MoveNext()
   at ConsoleApp2.Program.<>c__DisplayClass8_0.<<Main>b__0>d.MoveNext() in C:\Users\slubo\source\repos\ConsoleApp2\ConsoleApp2\Program.cs:line 81
   at ConsoleApp2.Program.Main(String[] _) in C:\Users\slubo\source\repos\ConsoleApp2\ConsoleApp2\Program.cs:line 79

Code to reproduce included below:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

namespace ConsoleApp2
{
    internal class Program
    {
        public class Data
        {
            public int Id { get; set; }
        }

        public class A : Data
        {
            public int BId { get; set; }
            public B B { get; set; }

            public int DId { get; set; }
            public D D { get; set; }
        }

        public class B : Data
        {
            public string Name { get; set; }
        }

        public class C : Data
        {
            public string AccountNumber { get; set; }
            public string Name { get; set; }
        }

        public class D : Data
        {
            public string Name { get; set; }
        }

        public class E : Data
        {

        }

        public class F : Data
        {
            public int EId { get; set; }
            public E E { get; set; }
        }

        public class TestContext : DbContext
        {
            public DbSet<A> As { get; set; }
            public DbSet<B> Bs { get; set; }
            public DbSet<C> Cs { get; set; }
            public DbSet<D> Ds { get; set; }
            public DbSet<E> Es { get; set; }
            public DbSet<F> Fs { get; set; }
           
            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                optionsBuilder.UseSqlServer(
                    @"Server=(localdb)\mssqllocaldb;Database=Test;Integrated Security=True");
            }

            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                base.OnModelCreating(modelBuilder);

                foreach (IMutableForeignKey relationship in modelBuilder.Model.GetEntityTypes().SelectMany(e => e.GetForeignKeys()))
                {
                    relationship.DeleteBehavior = DeleteBehavior.Restrict;
                }
            }
        }

        private static void Main(string[] _)
        {
            using var context = new TestContext();

            Task.Run(async () =>
            {
                var results = await context.As
                    .GroupJoin(
                            context.Bs,
                            rc => new
                            {
                                rc.BId
                            },
                            p => new
                            {
                                BId = p.Id
                            },
                            (rc, p) => new { rc, p })
                        .SelectMany(x => x.p, (rc, p) => new { rc.rc, p })
                        .SelectMany(
                            x => context.Cs,
                            (rc, g) => new { rc.rc, rc.p, g })
                        .SelectMany(
                            x => context.Es
                                    .Join(context.Fs, ac => ac.Id, ar => ar.EId, (ac, ar) => new { ac, ar })
                                    .Join(
                                        context.Cs,
                                        ac => new
                                        {
                                            glID = true
                                        },
                                        g => new
                                        {
                                            glID = true
                                        },
                                        (ac, g) => new { ac.ac, ac.ar, g })
                                    // THIS WHERE causes failure in EF 9 but not 8
                                    .Where(g => x.rc.D.Name == "A"),
                            (rc, gl1) => new { rc.rc, rc.p, rc.g, gl1 })
                        .ToListAsync();

                Console.WriteLine(results);

            }).GetAwaiter().GetResult();
        }
    }
}

[ajcvickers]

Confirmed regression from EF8.

[roji]

Stripped down repro:

await using var context = new BlogContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

var results = await context.As
    .SelectMany(a => context.Es.Where(g => a.Name == "A"))
    .ToListAsync();

public class BlogContext : DbContext
{
    public DbSet<A> As { get; set; }
    public DbSet<E> Es { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

public class A
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class E
{
    public int Id { get; set; }
}

[roji]

Also reproes with:

var results = await context.As
    .Where(a => context.As.Where(g => a.Name == "A").Any())
    .ToListAsync();