'EF Core 3.1 not mapping Included child collections

I'm trying to retrieve an entity and eager load it's child collection like so (I'm using EFCore 3.1)

var test = _context.Clients
  .Include(c => c.Bookings)
  .ThenInclude(b => b.AdmissionType)
  .FirstOrDefault(c => c.Id == 54);

The result is that test contains the client data, but the Bookings property is an empty list -- which it should not be.

Using SQL Profiler, I grabbed the executed SQL statement generated by EF (The code below uses ... to exclude a bunch of unnecessary properties)

SELECT [t].[Id], [t].[AgencyId], ... [t0].[Id], [t0].[AdmissionTypeId], [t0].[AgencyId], ...
FROM (
    SELECT TOP(1) [c].[Id], [c].[AgencyId], [c].[AgencyReferenceNumber], ...
    FROM [Clients] AS [c]
    WHERE [c].[Id] = 54
) AS [t]
LEFT JOIN (
    SELECT [b].[Id], [b].[AdmissionTypeId], [b].[AgencyId], ... [b0].[Id] AS [Id0], [b0].[Description], [b0].[ExcludeReporting]
    FROM [Bookings] AS [b]
    INNER JOIN [lookup].[BookingAdmissionTypes] AS [b0] ON [b].[AdmissionTypeId] = [b0].[Id]
    WHERE [b0].[ExcludeReporting] <> CAST(1 AS bit)
) AS [t0] ON [t].[Id] = [t0].[ClientId]
ORDER BY [t].[Id], [t0].[Id], [t0].[Id0]

Executing this in SSMS, I get back 4 records that include both Client and Booking data. So the query is definitely working. Somehow though, EF Core doesn't seem to want to map the results into my child collection.

Here are my Entity definitions and Mappings:

//Just the important bits
public class Client {
  public int Id {get; set;}
  public IList<Booking> Bookings {get; set;} = new List<Booking>();
}

public class Booking {
  public int Id {get; set;}
  public int ClientId {get; set;}
  public Client Client {get; set;}
  public int AdmissionTypeId {get; set;}
  public BookingAdmissionType AdmissionType {get; set;}
}

public class BookingAdmissionType {
  public int Id {get; set; }
  public string Description {get; set;}
  public bool ExcludeFromReporting {get; set;}
}

//EF Mapping Definitions
builder.Entity<Client>()
  .HasMany(c => c.Bookings)
  .WithOne(b => b.Client)
  .HasForeignKey(b => b.ClientId);

builder.Entity<Booking>()
  .HasOne(b => b.AdmissionType)
  .WithMany()
  .HasForeignKey(b => b.AdmissionTypeId);

builder.Entity<Booking>().HasQueryFilter(b => !b.AdmissionType.ExcludeReporting);

builder.Entity<BookingAdmissionType>().ToTable("BookingAdmissionTypes","lookup");

Update

I have tried both IgnoreQueryFilter and completely removing the QueryFilter on Booking. I still get the same result -- no bookings are mapped on the client.

However, just this morning I added this query just before querying clients

var test1 = _context.Bookings.Where(b => b.ClientId ==54);

This did cause the Bookings to be populated for the Client. However, I would maintain that this is not consistent with what I used to do in .NetCore 2.2.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source