[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();

[roji]

This turned out to be because of some special TPC predicate checks we have when applying WHERE, which implement OfType logic (removing TPC tables once the user narrows the hierarchy down). The logic specifically checks for a predicate that's a simple equality of the TPC discriminator column; the latter is identified by looking at the column's table to see if it's a TpcTablesExpression.

Now, in 8.0, ColumnExpression referenced its table directly, so the check simply worked; but in 9.0, columns only have their table aliases, and the table must be resolved from the SelectExpression (our SQL tree is now a tree rather than a graph). The 9.0 logic simply assumes that the column's identifier would be found in the (current) SelectExpression, but when the column references a table from an outer query, the table isn't found in the inner SelectExpression and an exception is thrown.

The fix in #35120 simply fails the check if the table isn't found on the current SelectExpression, and translates it as usual. The special logic should absolutely not apply for TpcTableExpressions in outer queries, since a predicate in a subquery doesn't narrow down the outer query's TPC table list.