dotnet
Include() on owned types does not work when placed after Where()
When an owned type has navigation properties, Include() only works when it is placed before Where(). In this sample Location is an owned type with 2 navigation properties: room and building.
async Task Main()
{
var c = new Context();
var sourcePlanningIds = new List<long> { 21 };
IQueryable<Planning> sourcePlannings = c.Plannings.Where(p => sourcePlanningIds.Contains(p.Id));
var p = c.Plannings
.Where(target => sourcePlannings.Any(sourcePlanning =>
sourcePlanning.Location.Room == target.Location.Room
&& sourcePlanning.Location.Building == target.Location.Building
))
.Include(p => p.Location)
.ThenInclude(p => p.Building)
.Include(p => p.Location)
.ThenInclude(p => p.Room)
.ToListAsync();
var planning = (await p).First();
Console.WriteLine(planning.Location.Building); // null
Console.WriteLine(planning.Location.Room); // null
}
when Include()s are before Where() the code works as expected
async Task Main()
{
var c = new Context();
var sourcePlanningIds = new List<long> { 21 };
IQueryable<Planning> sourcePlannings = c.Plannings.Where(p => sourcePlanningIds.Contains(p.Id));
var p = c.Plannings
.Include(p => p.Location)
.ThenInclude(p => p.Building)
.Include(p => p.Location)
.ThenInclude(p => p.Room)
.Where(target => sourcePlannings.Any(sourcePlanning =>
sourcePlanning.Location.Room == target.Location.Room
&& sourcePlanning.Location.Building == target.Location.Building
))
.ToListAsync();
var planning = (await p).First();
Console.WriteLine(planning.Location.Building); // not null
Console.WriteLine(planning.Location.Room); // not null
}
Context configuration + classes
public class Context : DbContext
{
public DbSet<Planning> Plannings { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Planning>(config =>
{
config.ToTable("Planning");
config.OwnsOne(c => c.Location, x =>
{
x.Property<int>("BuildingId").HasColumnName("BuildingId").IsRequired();
x.HasOne(x => x.Building).WithMany().HasForeignKey("BuildingId").IsRequired();
x.Property<int?>("RoomId").HasColumnName("RoomId");
x.HasOne(x => x.Room).WithMany().HasForeignKey("RoomId").IsRequired(false);
});
});
modelBuilder.Entity<Building>(config =>
{
config.ToTable("Building");
});
modelBuilder.Entity<Room>(config =>
{
config.ToTable("Room");
});
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer("...")
.EnableSensitiveDataLogging(true);
}
}
public class Planning
{
public long Id { get; set; }
public Location Location { get; private set; }
}
public class Location
{
public virtual Building Building { get; set; }
public virtual Room Room { get; set; }
}
public class Room
{
public int Id { get; set; }
}
public class Building
{
public int Id { get; set; }
public string Name { get; set; }
}
EF Core version: Microsoft.EntityFrameworkCore.SqlServer 6.0.25 and 8.0.0 Target framework: .NET 6.0/8.0
Note for triage: this is a case where there is an owned type with navigations to non-owned nested types. A query without Includes treats these as auto-included:
SELECT [p].[Id], [p].[BuildingId], [p].[RoomId]
FROM [Planning] AS [p]
LEFT JOIN [Room] AS [r] ON [p].[RoomId] = [r].[Id]
LEFT JOIN [Building] AS [b] ON [p].[BuildingId] = [b].[Id]
WHERE EXISTS (
SELECT 1
FROM [Planning] AS [p0]
LEFT JOIN [Room] AS [r0] ON [p0].[RoomId] = [r0].[Id]
LEFT JOIN [Building] AS [b0] ON [p0].[BuildingId] = [b0].[Id]
WHERE [p0].[Id] IN (
SELECT [s].[value]
FROM OPENJSON(@__sourcePlanningIds_0) WITH ([value] bigint '$') AS [s]
) AND ([r0].[Id] = [r].[Id] OR ([r0].[Id] IS NULL AND [r].[Id] IS NULL)) AND ([b0].[Id] = [b].[Id] OR ([b0].[Id] IS NULL AND [b].[Id] IS NULL)))
This seems wrong, since Room and Building are not owned.
However, the non-owned types do not get materialized in this case, which is correct.
If the Includes are used explicitly before the Where clause, then the same query is generated, which is now correct, and the non-owned types get materialized, which is also correct.
If the Includes are used after the Where clause, then EF behaves like the first case (no Includes) again. However, in this case, the query is correct, but the materialization doesn't work, which is wrong.
@ajcvickers please note the Name property in Building class, in the last case you mentioned, Name should be also present in the query, but the query only selects ids
includes before where
SELECT [e].[Id], [e].[BuildingId], [e].[RoomId], [b].[Id], [b].[Name], [r].[Id]
FROM [Planning] AS [e]
LEFT JOIN [Room] AS [r] ON [e].[RoomId] = [r].[Id]
LEFT JOIN [Building] AS [b] ON [e].[BuildingId] = [b].[Id]
WHERE EXISTS (
SELECT 1
FROM [Planning] AS [e0]
LEFT JOIN [Room] AS [r0] ON [e0].[RoomId] = [r0].[Id]
LEFT JOIN [Building] AS [b0] ON [e0].[BuildingId] = [b0].[Id]
WHERE ([e0].[Id] = CAST(21 AS bigint)) AND ((([r0].[Id] = [r].[Id]) OR (([r0].[Id] IS NULL) AND ([r].[Id] IS NULL))) AND (([b0].[Id] = [b].[Id]) OR (([b0].[Id] IS NULL) AND ([b].[Id] IS NULL)))))
includes after where
SELECT [e].[Id], [e].[BuildingId], [e].[RoomId]
FROM [Planning] AS [e]
LEFT JOIN [Room] AS [r] ON [e].[RoomId] = [r].[Id]
LEFT JOIN [Building] AS [b] ON [e].[BuildingId] = [b].[Id]
WHERE EXISTS (
SELECT 1
FROM [Planning] AS [e0]
LEFT JOIN [Room] AS [r0] ON [e0].[RoomId] = [r0].[Id]
LEFT JOIN [Building] AS [b0] ON [e0].[BuildingId] = [b0].[Id]
WHERE ([e0].[Id] = CAST(21 AS bigint)) AND ((([r0].[Id] = [r].[Id]) OR (([r0].[Id] IS NULL) AND ([r].[Id] IS NULL))) AND (([b0].[Id] = [b].[Id]) OR (([b0].[Id] IS NULL) AND ([b].[Id] IS NULL)))))
side note:
since Building is required on planning why the generated query does a LEFT JOIN?
We also encountered this issue, interesting note though, when the owned entity contains multiple includes, only the includes that are references in the Where clause will get omitted.
