EF: how do I use a include with a where statement?

Question

I have an Employee with an Employment and I want to get a list of all employees with a valid employment.

The valid code in SQL would be

SELECT *
FROM dbo.Employees AS employee INNER JOIN
  dbo.Employment AS employment ON employee.ID = employment.Employee_ID AND 
  employment.StartDate <= '2022-3-31' AND (employment.EndDate IS NULL OR
                         employment.EndDate >= '2022-3-31')

If I try something like this I C# I would expect to be able to use this

var date = DateTime.Now

context.employee.include(x => x.Employment.Where(y => y.Employment.StartDate <= date && ((x.Employment.Enddate ==null) || (x.Employment.Enddate >= date))

But this doesn't work.

I have also tried to include Z.EntityFramework.Plus.EFCore and use the following code

var date = DateTime.Now

context.employee.includeFilter(x => x.Employment.Where(y => y.Employment.StartDate <= date && ((x.Employment.Enddate ==null) || (x.Employment.Enddate >= date))

But this does give me results but will include also the people without an employment

Could somebody give me a hint how to fix this?

Maurice

Answer 1

Why don't you just do

context.Employees.Include(x => x.Employment)
    .Where(x => x.Employments.Any(employment => 
              employment.StartDate <= date &&
              (employment.EndDate == null || employment.EndDate > date)));

Given that a person can be employed multiple times in the same company....