Conditionally include and filter related entities in EF Core 8

How can I query the Employees and Holidays collections so that even if no holidays are found matching their specified criteria, the employees matching their criteria are still returned?

Entities:

public class Employee
{
    public Guid Id { get; set; }
    public List<Holiday> Holidays { get; set; } = [];
}

public class Holiday
{
    public Guid Id { get; set; }
    public Guid EmployeeId { get; set; }
    public DateTime Date { get; set; }
}

Query:

I have a method that needs to:

• Search for employee by Id
• And either
  • Dont include any holidays
  • Include all holidays for the employee, if any exist
  • Include all historic holidays for the employee, if any exist
  • Include all future holidays for the employee, if any exist

enum IncludeHolidays { None, All, Past, Future }

Employee? GetEmployee(Guid employeeId, IncludeHolidays includeHolidays)
{
    var query = ctx.Employees
        .Where(e => e.Id == employee.Id)
        .AsQueryable();

    if (includeHoliday != IncludeHolidays.None)
    {
        query = query.Include(e => e.Holidays);

        switch (includeHoliday)
        {
            case IncludeHolidays.Future:
                query = query.Where(e => e.Holidays.Where(h => h.Date >= DateTime.UtcNow));
                break;
            case IncludeHolidays.Past:
                query = query.Where(e => e.Holidays.Where(h => h.Date < DateTime.UtcNow));
                break;
        }
    }

    return query.FirstOrDefault();
}

The above method doesnt compile because of the nested Where calls. However I cannot use Holidays.Any or Holidays.Any because these will exclude the Employee if they do not have holidays matching that criteria.

How can I query against the Holidays without affecting the Employee that's identified?

An example SQL query that would achieve what I'm trying to do could be:

select * from "Employees" e 
left join "Holidays" h on e."Id" = h."EmployeeId" and h."Date" >= current_timestamp
where e."Id" = 'employee_id';

Edit 1

Having applied the changes suggested in the answers, I'm finding the the filter on Holiday.Date doesnt seem to be applied, and the results include all holidays regardless of their date.

var employee = new Employee
{
    Id = Guid.NewGuid(),
};
var pastHol = new Holiday
{
    Id = Guid.NewGuid(),
    EmployeeId = employee.Id,
    Date = DateTime.UtcNow.AddDays(-1)
};
var futureHol = new Holiday
{
    Id = Guid.NewGuid(),
    EmployeeId = employee.Id,
    Date = DateTime.UtcNow.AddDays(+1)
};

ctx.Employees.Add(employee);
ctx.Holidays.AddRange([pastHol, futureHol]);
ctx.SaveChanges();

var result = GetEmployee(employee.Id, IncludeHolidays.Future);

// Output has two holidays, one for yesterday, one for tomorrow
Console.WriteLine(JsonSerializer.Serialize(result));


Employee? GetEmployee(Guid employeeId, IncludeHolidays includeHolidays)
{
    var query = ctx.Employees
        .Where(e => e.Id == employee.Id)
        .AsQueryable();

    if (includeHolidays != IncludeHolidays.None)
    {
        switch (includeHolidays)
        {
            case IncludeHolidays.Future:
                query = query.Include(e => e.Holidays.Where(h => h.Date >= DateTime.UtcNow));
                break;
            case IncludeHolidays.Past:
                query = query.Include(e => e.Holidays.Where(h => h.Date < DateTime.UtcNow));
                break;
        }
    }

    return query.FirstOrDefault();
}

However the generated SQL looks good and should be filtering on the date:

      SELECT t."Id", t0."Id", t0."Date", t0."EmployeeId"
      FROM (
          SELECT e."Id"
          FROM "Employees" AS e
          WHERE e."Id" = @__employee_Id_0
          LIMIT 1
      ) AS t
      LEFT JOIN (
          SELECT h."Id", h."Date", h."EmployeeId"
          FROM "Holidays" AS h
          WHERE h."Date" >= now()
      ) AS t0 ON t."Id" = t0."EmployeeId"
      ORDER BY t."Id"

Is there something else going on here within entity framework where its caching the entities I've just created and returning them even though the DB doesnt return them?

Edit 2

Just found the Filtered Include docs which mentions:

In case of tracking queries, results of Filtered Include may be unexpected due to navigation fixup. All relevant entities that have been queried for previously and have been stored in the Change Tracker will be present in the results of Filtered Include query, even if they don't meet the requirements of the filter. Consider using NoTracking queries or re-create the DbContext when using Filtered Include in those situations

Sounds like this is the reason I'm seeing results that do not match my filter criteria. And updating the query to use NoTracking solves the issue:

var query = ctx.Employees
    .Where(e => e.Id == employee.Id)
    .AsNoTracking();

// .. filters

// Only has 1 future holiday and matches the filters
return query.FirstOrDefault();