Entities with JSON owned entities can't be queried out with FromSql

[roji]

For an entity type that has JSON owned entities, the following fails:

var items = await context.Blogs.FromSql($"WITH query AS (SELECT * FROM Blogs) SELECT * FROM query").ToListAsync();

It seems like when applying the final projection, we compose an additional SELECT on top:

SELECT b.Id, b.Name, b.Owned
FROM WITH query AS (SELECT * FROM Blogs) SELECT * FROM query

... and this (correctly) causes a non-composable SQL failure. We should not need to compose this extra SELECT on top.

With the focus on complex types for mapping JSON in EF 10, owned entities are getting de-prioritized. Given that, and given that we haven't seen many reports, i'll put this on the backlog for now.

Repro

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

var items = await context.Blogs.FromSql($"WITH query AS (SELECT * FROM Blogs) SELECT * FROM query").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>().OwnsOne(b => b.Owned).ToJson();
    }
}

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

    public Owned Owned { get; set; }
}

public class Owned
{
    public int OwnedProperty { get; set; }
}

[V3lu]

@roji I can help with this one. Can you assign this issue to me?

[roji]

@Siv-I we do not assign issues to community members in general, only to people in the EF team. You're welcome to look at this nevertheless, but:

1. As I wrote above we're generally deprioritizing work on JSON-mapped owned entities, so it's likely we wouldn't be able to give your PR much attention anytime soon.
2. This is likely to be a non-trivial fix requiring deep knowledge of the EF query pipeline. Unless you're experienced in EF internals, I'd advise looking for a different, simpler issue to start with.