Owned Entity is not loaded inside multiple levels of sub queries

[TwentyFourMinutes]

When nesting queries, with entities that hold owned entities, some queries no longer return the owned entity when being projected into a typed wrapper. Surprisingly when using an anonymous object everything works as expected. Also note that this doesn't seem to be related to the materializer, as the ToQueryString won't return the PhoneNumber is the SQL itself.

Sample:

• When select new Wrapper is used the PhoneNumber is null, however when using select new it is returned correctly.

using Microsoft.EntityFrameworkCore;
using System.Text.Json;

int apartmentId = 0;

await using (var db = new AppDbContext())
{
    var apartment = new Apartment();
    db.Add(apartment);
    await db.SaveChangesAsync();

    var person = new Person
    {
        ApartmentId = apartment.Id,
        PhoneNumber = new("old")
    };

    db.Add(person);
    await db.SaveChangesAsync();

    apartmentId = apartment.Id;
}

await using (var db = new AppDbContext())
{
    var query = (from apartment in db.Apartments

                 let persons = (from otherApartment in db.Apartments

                                from person in (from p in db.Persons
                                                where p.ApartmentId == otherApartment.Id
                                                // Removing the Wrapper type and using an anonymous type correctly returns the PhoneNumber.
                                                select new Wrapper
                                                {
                                                    Person = p
                                                })

                                where otherApartment.Id == apartment.Id
                                select person).ToList()
                 where apartment.Id == apartmentId
                 select new
                 {
                     apartment.Id,
                     persons
                 });

    // PhoneNumber is not queried in the query string.
    Console.WriteLine(query.ToQueryString());

    var result = await query.ToListAsync();

    // PhoneNumber is null even though it shouldn't be.
    Console.WriteLine(JsonSerializer.Serialize(result));
}

public class AppDbContext : DbContext
{
    public DbSet<Apartment> Apartments { get; set; }
    public DbSet<Person> Persons { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseNpgsql("Host=127.0.0.1;Port=5433;Database=efcore-owned-entity;Username=postgres;Password=developer");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Apartment>(e =>
        {
            e.HasMany(a => a.Persons)
             .WithOne()
             .HasForeignKey(p => p.ApartmentId);
        });

        modelBuilder.Entity<Person>(e =>
        {
            e.OwnsOne(p => p.PhoneNumber);
        });
    }
}

public class Apartment
{
    public int Id { get; set; }
    public List<Person> Persons { get; set; }
}

public class Person
{
    public int Id { get; private set; }

    public PhoneNumber PhoneNumber { get; set; }

    public int ApartmentId { get; set; }
}

public record PhoneNumber(string? Number);

public class Wrapper
{
    public Person Person { get; set; }
}

Results for the two WriteLines:

SQL:

-- @__apartmentId_0='6' SELECT a."Id", t."Id", t."ApartmentId", t."Id0" FROM "Apartments" AS a LEFT JOIN ( SELECT p."Id", p."ApartmentId", a0."Id" AS "Id0" FROM "Apartments" AS a0 INNER JOIN "Persons" AS p ON a0."Id" = p."ApartmentId" ) AS t ON a."Id" = t."Id0" WHERE a."Id" = @__apartmentId_0 ORDER BY a."Id", t."Id0"

JSON:

[{"Id":6,"persons":[{"Person":{"Id":6,"PhoneNumber":null,"ApartmentId":6}}]}]

When changing the projection to an anonymous object this is the expected output:

-- @__apartmentId_0='7' SELECT a."Id", t."Id", t."ApartmentId", t."PhoneNumber_Number", t."Id0" FROM "Apartments" AS a LEFT JOIN ( SELECT p."Id", p."ApartmentId", p."PhoneNumber_Number", a0."Id" AS "Id0" FROM "Apartments" AS a0 INNER JOIN "Persons" AS p ON a0."Id" = p."ApartmentId" ) AS t ON a."Id" = t."Id0" WHERE a."Id" = @__apartmentId_0 ORDER BY a."Id", t."Id0"

[{"Id":7,"persons":[{"Person":{"Id":7,"PhoneNumber":{"Number":"old"},"ApartmentId":7}}]}]

I am using the Npgsql adapter as well version 8.0.4 for all packages. See the repo for a Ready2Run sample https://github.com/TwentyFourMinutes/EFCoreOwnedEntityNotLoaded.

[maumar]

i'm able to reproduce this, problem is in nav expansion, it doesn't peek into the Wrapper to generate Include expressions for SharedTypeEntityExpandingExpressionVisitor to expand it later. Workaround is, as @TwentyFourMinutes pointed out, to not use the wrapper type but regular anonymous type instead.

expression tree before translation with the wrapper type:

DbSet<Apartment>()
    .Where(a => a.Id == __apartmentId_0)
    .Select(a => new { 
        Id = a.Id, 
        persons = DbSet<Apartment>()
            .SelectMany(
                collectionSelector: a0 => DbSet<Person>()
                    .Where(p => p.ApartmentId == a0.Id)
                    .Select(p => new Wrapper{ Person = p }
                    ), 
                resultSelector: (a0, c) => new TransparentIdentifier<Apartment, Wrapper>(
                    Outer = a0, 
                    Inner = c
                ))
            .Where(ti => ti.Outer.Id == a.Id)
            .Select(ti => ti.Inner)
            .ToList()
     })

without wrapper type:

DbSet<Apartment>()
    .Where(a => a.Id == __apartmentId_0)
    .Select(a => new { 
        Id = a.Id, 
        persons = DbSet<Apartment>()
            .SelectMany(
                collectionSelector: a0 => DbSet<Person>()
                    .Where(p => p.ApartmentId == a0.Id)
                    .Select(p => new { Person = p }), 
                resultSelector: (a0, c) => new TransparentIdentifier<Apartment, <>f__AnonymousType23<Person>>(
                    Outer = a0, 
                    Inner = c
                ))
            .Where(ti => ti.Outer.Id == a.Id)
            .Select(ti => new { Person = Include(
                Entity: ti.Inner.Person, 
                Navigation: PhoneNumber, EF.Property<PhoneNumber>(ti.Inner.Person, "PhoneNumber") })
            .ToList()
     })