dotnet / dotnet/efcore

Include() on owned types does not work when placed after Where()

Open
#32,320 4 comments 2 reactions 0 assignees View on GitHub
area-query customer-reported needs-design
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

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.

```C#
async Task Main()
{
var c = new Context();

var sourcePlanningIds = new List { 21 };

IQueryable 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

```C#
async Task Main()
{
var c = new Context();

var sourcePlanningIds = new List { 21 };

IQueryable 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
```C#
public class Context : DbContext
{
public DbSet Plannings { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);

modelBuilder.Entity(config =>
{
config.ToTable("Planning");

config.OwnsOne(c => c.Location, x =>
{
x.Property("BuildingId").HasColumnName("BuildingId").IsRequired();
x.HasOne(x => x.Building).WithMany().HasForeignKey("BuildingId").IsRequired();

x.Property("RoomId").HasColumnName("RoomId");
x.HasOne(x => x.Room).WithMany().HasForeignKey("RoomId").IsRequired(false);
});

});

modelBuilder.Entity(config =>
{
config.ToTable("Building");

});

modelBuilder.Entity(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

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.