JSON column translation fails when using .Union()

[dimkr]

(Moved from https://github.com/npgsql/efcore.pg/issues/3042)

I'm using Npgsql.EntityFrameworkCore.PostgreSQL 8.0.0 and one table has a JSON column.

[Owned]
public class ThingMetadata
{
        [...]
}

public class Thing
{
        [...]
        public ThingMetadata Metadata { get; set; }
        [...]
}

public class ThingsDbContext: DbContext
{
        public DbSet<Thing> Things { get; set; }

        [...]

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            [...]

            modelBuilder.Entity<Thing>().OwnsOne(
                t => t.Metadata, ownedNavigationBuilder =>
                {
                    ownedNavigationBuilder.ToJson();
                });

            [...]
        }
}

Queries on this table fail if they return entities and use .Union():

            using var db = new ThingsDbContext();

            var query = db.Things
                .Where(t => t.Id % 2 == 0)
                .Union(
                    db.Things
                         .Where(t => t.Id % 3 == 0)
                );

            // query.Count() works
            // db.Things.Where(t => t.Id % 2 == 0).ToList() works

            return query.ToList()

      Compiling query expression:
      'DbSet<Thing>()
          .Where(t => t.Id % 2 == 0)
          .Union(DbSet<Thing>()
              .Where(t => t.Id % 3 == 0))'
    [...]
      Including navigation: 'Thing.Metadata'.
Unhandled exception. System.InvalidOperationException: Translation of 'EF.Property<ThingMetadata>(StructuralTypeShaperExpression:
    [...]
    ValueBufferExpression:
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
, "Metadata")' failed. Either the query source is not an entity type, or the specified property does not exist on the entity type.
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression, Boolean applyDefaultTypeMapping)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateProjection(Expression expression, Boolean applyDefaultTypeMapping)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.IncludeExpression.VisitChildren(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
   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.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   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__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   [...]

Minimal repro against sqlite by @WhatzGames:

using Microsoft.EntityFrameworkCore;

await using TestContext context = new();

await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

await context.ClassAs
    .Where(x => x.Id % 2 == 0)
    .Union(context.ClassAs.Where(x => x.Id % 3 == 0))
    .ToArrayAsync();

public class TestContext : DbContext
{
    public DbSet<ClassA> ClassAs => Set<ClassA>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // optionsBuilder.UseNpgsql("Host=localhost;Database=efcore;Username=postgres;")
        //     .LogTo(Console.WriteLine);
        optionsBuilder.UseSqlite("Data Source=efcore.db;");
        base.OnConfiguring(optionsBuilder);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<ClassA>(builder =>
        {
            builder.OwnsOne(x => x.Test, navigationBuilder =>
            {
                //commenting out will result in a successful query
                navigationBuilder.ToJson();
            });
        });
        base.OnModelCreating(modelBuilder);
    }
}

public class ClassA
{
    public long Id { get; set; }

    public required ClassB Test { get; set; }
}

public class ClassB
{
    public Guid Id { get; set; }
}

[roji]

Confirmed, see minimal repro below.

@maumar does this look familiar? I haven't seen an issue already tracking this (putting on your list for now - but not necessarily for fixing right away).

Minimal repro

```c# await using var context = new BlogContext(); await context.Database.EnsureDeletedAsync(); await context.Database.EnsureCreatedAsync(); _ = context.Things .Where(t => t.Id % 2 == 0) .Union(context.Things.Where(t => t.Id % 3 == 0)) .ToList(); // .Count(); // Works public class BlogContext : DbContext { public DbSet Things { 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(); protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity().OwnsOne( t => t.Metadata, ownedNavigationBuilder => { ownedNavigationBuilder.ToJson(); }); } } public class Thing { public int Id { get; set; } public ThingMetadata Metadata { get; set; } } [Owned] public class ThingMetadata { public string SomeProperty { get; set; } } ```

[maumar]

Workaround: query for both sources to union separately and union them on the client.

[maumar]

Problem is SelectExpression.ApplyUnion. When we get there, we have 2 queries as input, whose projection mapping is EmptyProjectionMember to ClassA, which inside stores owned navigation map entry to ClassB (via Test navigation). However in HandleStructuralTypeProjection, where we are supposed to generate the result StructuralTypeProjectionExpression for union, we "lose" the owned navigation map contents. Later, when trying to process Include from ClassA to ClassB via Test, we peek into that owned navigation map and found nothing.

[maumar]

note that this is probably broken for other scenarios as well, basically anything with pushdown (?) - see https://github.com/dotnet/efcore/issues/32911 for potential scenarios to test when fixing this

[dimkr]

Workaround: query for both sources to union separately and union them on the client.

In my case, I can't do this because I have many duplicates, I have several performance-critical areas that use set operations and it's UNION (and not a UNION ALL) for good reasons. Currently, my workaround is to do the UNION but .Select(x => x.Id) at the end, then pass a huge array of IDs to other queries that reference the matching rows: the client never receives the JSON columns, it's a single query and the result is small.

[roji]

@maumar not a regression, right?

[maumar]

not a regression, exactly same error happens on 7