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

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

CodePudding user response：

Please try this:-

var result = context.Employee.Join(
                  context.Employment, 
                  employee=> employee.ID,
                  employment => employment.Employee_ID,
                  (x,y) => new {
                                id = x.Id,
                                firstname = x.Firstname,
                                lastname = x.Lastname,
                                Startdate = y.Startdate, 
                                Enddate = y.Enddate
                            }).ToList();

    var date = DateTime.Now;
    
    var final = result.Where(y => y.StartDate <= date && ((y.Enddate ==null) || (y.Enddate >= date))).ToList();

CodePudding user response：

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....