Error selecting nested json collections with query splitting on

[stanislav-a-frolov]

Hi, everyone.

When I try to select nested JSON collection with the following config:

modelBuilder.Entity<EntityMain>()
    .OwnsMany(e => e.Inner, b => b.OwnsMany(e => e.Values, b2 => b2.ToTable(table)).ToJson("inner").ToTable(table))
    .ToTable(table);

Query:

dbContext.Set<EntityMain>().AsNoTracking().Select(c => 
    c.Inner!.Where(c => c.Key == "Asd")
    .Select(c => new NamedTimeSeriesEntry { Key = c.Key, Values = c.Values.Where(v => v.Date <= new DateTime(2022, 3, 1)).ToList() }))
    .ToListAsync().Result;

When query splitting is on (UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery)) I get the following exception:

System.AggregateException: 'One or more errors occurred. (The given key 'Property: TimeSeriesEntry.NamedTimeSeriesEntryId (no field, int) Shadow Required PK FK AfterSave:Throw' was not present in the dictionary.)'

When I turn query splitting off it works fine. It seems that query splitting should not be used for JSON entities.
Any workarounds for now?
Minimum repro included.
Json-Nested-Collection-Query-minirepro.zip

EF Core version: 8.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer 8.0.0, Npgsql.EntityFrameworkCore.PostgreSQL 8.0.0
Target framework: .NET 8.0
Operating system: Windows

[roji]

Confirmed, seems like we have an issue specifically with projecting collections out of JSON documents (filtering works fine). See minimal repro with two different queries producing two different errors (but possibly related). Note that this is not a regression - AFAICT the error requires composing Select on top of a JSON collection, this wasn't supported pre-8.0.

@maumar I think this is in your area, interested in investigating?

Minimal repro

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

ctx.Blogs.Add(new Blog
{
    Name = "BlogName",
    Posts = new List<Post>
    {
        new()
        {
            Title = "PostTitle",
            Comments = new List<Comment>
            {
                new(),
                new()
            }
        }
    }
});
await ctx.SaveChangesAsync();

// FAILS: The given key 'Property: Comment.PostId (no field, int) Shadow Required PK FK AfterSave:Throw' was not present in the dictionary.
// _ = await ctx.Set<Blog>()
//     .AsNoTracking()
//     .AsSplitQuery()
//     .Select(b => b.Posts.Select(p => p.Comments.Where(c => c.Date <= new DateOnly(2022, 3, 1)).ToList()))
//     .ToListAsync();

// FAILS: Invalid column name 'key'.
// _ = await ctx.Set<Blog>()
//     .AsNoTracking()
//     .AsSplitQuery()
//     .Select(b => b.Posts.Select(p => p.Comments.ToList()))
//     .ToListAsync();

// WORKS (filtering)
// _ = await ctx.Set<Blog>()
//     .AsNoTracking()
//     .AsSplitQuery()
//     .Where(b => b.Posts.Count(p => p.Comments.Any(c => c.Date <= new DateOnly(2022, 3, 1))) == 2)
//     .ToListAsync();

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { 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<Blog>()
            .OwnsMany(b => b.Posts, b =>
            {
                b.ToJson();
                b.OwnsMany(p => p.Comments);
            });
    }
}

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

    public List<Post> Posts { get; set; }
}

public class Post
{
    public int Id { get; set; }
    public string Title { get; set; }

    public List<Comment> Comments { get; set; }
    public Blog Blog { get; set; }
}

public class Comment
{
    public int Id { get; set; }
    public DateOnly Date { get; set; }

    public Post Post { get; set; }
}