Invalid query generated when retrieving nested complex collection as json in combination with global query filter

[tpoiesz]

The following program throws an exception on the call to ToListAsync (line 12):

using System.Diagnostics;
using Microsoft.EntityFrameworkCore;

const string connectionString = "Server=localhost;Database=complex-issues;Port=5432;User Id=postgres;Password=postgres;Include Error Detail=true";
var options = new DbContextOptionsBuilder<AppDbContext>()
  .UseNpgsql(connectionString)
  .Options;
var dbContext = new AppDbContext(options);
await dbContext.Database.EnsureDeletedAsync();
await dbContext.Database.EnsureCreatedAsync();

var portfolios = await dbContext
  .Portfolios
  .Include(portfolio => portfolio.Configuration)
  .AsNoTracking()
  .ToListAsync();

Debug.Assert(portfolios.Count == 0);

public class Portfolio
{
  public Guid Id { get; init; }
  public required PortfolioConfiguration Configuration { get; init; }
}

public class PortfolioConfiguration
{
  public Guid Id { get; init; }
  public Guid TenantId { get; init; }
  public required Portfolio Portfolio { get; init; }
  public required List<PortfolioConfigurationField> Fields { get; init; }
}

public class PortfolioConfigurationField
{
  public required string Value { get; init; }
}

public class Tenant
{
  public Guid Id { get; init; }
}

public class AppDbContext(DbContextOptions options) : DbContext(options)
{
  public DbSet<Portfolio> Portfolios { get; init; }
  
  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
    base.OnModelCreating(modelBuilder);
    modelBuilder.Entity<Tenant>();
    modelBuilder.Entity<PortfolioConfiguration>().HasOne(configuration => configuration.Portfolio)
      .WithOne(portfolio => portfolio.Configuration).HasForeignKey<PortfolioConfiguration>();
    modelBuilder.Entity<PortfolioConfiguration>().HasOne<Tenant>().WithMany().HasForeignKey(configuration => configuration.TenantId);
    modelBuilder.Entity<PortfolioConfiguration>().ComplexCollection(configuration => configuration.Fields).ToJson();
    
    modelBuilder.Entity<PortfolioConfiguration>()
      .HasQueryFilter(configuration => configuration.TenantId == Guid.NewGuid());
  }

  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
  {
    base.OnConfiguring(optionsBuilder);
    optionsBuilder.LogTo(message =>
    {
      Debug.WriteLine(message);
    });
  }
}

The exception is:

Unhandled exception. Npgsql.PostgresException (0x80004005): 42703: column p1.Fields does not exist

POSITION: 40
   at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Program.<Main>$(String[] args) in /Users/thomaspoiesz/Development/projects/finview/EFCoreComplexIssues/Program.cs:line 12
   at Program.<Main>(String[] args)
  Exception data:
    Severity: ERROR
    SqlState: 42703
    MessageText: column p1.Fields does not exist
    Position: 40
    File: parse_relation.c
    Line: 3504
    Routine: errorMissingColumn

The generated SQL is:

SELECT p."Id", p1."Id", p1."TenantId", p1."Fields"
      FROM "Portfolios" AS p
      LEFT JOIN (
          SELECT p0."Id", p0."TenantId"
          FROM "PortfolioConfiguration" AS p0
          WHERE p0."TenantId" = gen_random_uuid()
      ) AS p1 ON p."Id" = p1."Id"

Notice the missing p0."Fields" in the second SELECT statement.

csproj looks like:

<Project Sdk="Microsoft.NET.Sdk">

    <PropertyGroup>
        <OutputType>Exe</OutputType>
        <TargetFramework>net10.0</TargetFramework>
        <ImplicitUsings>enable</ImplicitUsings>
        <Nullable>enable</Nullable>
    </PropertyGroup>

    <ItemGroup>
      <PackageReference Include="Microsoft.EntityFrameworkCore" Version="10.0.1" />
      <PackageReference Include="Microsoft.EntityFrameworkCore.Abstractions" Version="10.0.1" />
      <PackageReference Include="Microsoft.EntityFrameworkCore.Analyzers" Version="10.0.1" />
      <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="10.0.1" />
      <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="10.0.0" />
    </ItemGroup>

</Project>

[roji]

Thanks, this was already fixed for 10.0.2.