dotnet / dotnet/efcore

Owned Entity is not loaded inside multiple levels of sub queries

Open
#33,709 1 comment 5 reactions 0 assignees View on GitHub
area-query customer-reported
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.