Owned Entity is not loaded inside multiple levels of sub queries
- Dominant language
- C#
- Stars
- 14.8k
- Forks
- 3.4k
- PR merge metrics
- PR metrics pending
Description
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.
```cs
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 Apartments { get; set; }
public DbSet 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(e =>
{
e.HasMany(a => a.Persons)
.WithOne()
.HasForeignKey(p => p.ApartmentId);
});
modelBuilder.Entity(e =>
{
e.OwnsOne(p => p.PhoneNumber);
});
}
}
public class Apartment
{
public int Id { get; set; }
public List 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.
Contributor guide
Assessment
This issue has not been assessed yet.