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

try context.employee.includeFilter(x => x.Employment.Where(y => y.Employment.StartDate <= date && ((x.Employment.Enddate == null) || (x.Employment.Enddate >= date)) && y.Employee_ID != null)) — Gaurav Chaudhary, Mar 31 '22 at 13:38
Does this answer your question? [EF Query With Conditional Include](https://stackoverflow.com/questions/1535443/ef-query-with-conditional-include) — Ruben Martirosyan, Mar 31 '22 at 13:44
If I use .EnableSensitiveDataLogging(); I can see the generated SQL code and if I copy/paste this to SQL I get 83 results. Exactly what I expect. But the code of me or Gaurav does give me 308 results. The following will cleanup the results var cleanResults = results.Where(x => x.Employment.Count != 0) results=308 records big cleanResults = 83 records big But I would expect this is done during the original query — Maurice Lucas, Mar 31 '22 at 14:02
if tables are properly defined relationship in DB then INCLUDE else JOIN works for both. also what is the difference you are seeing in generated SQL vs your query. — Gaurav Chaudhary, Mar 31 '22 at 14:13
@GauravChaudhary Inside C# the generated code gives me a tolist with 308 results. The SQL code from the sensitiveLogging will give me inside SQL management studio query the expected 83 results. — Maurice Lucas, Mar 31 '22 at 14:30
See the duplicate, esp. the part that explains that you need to filter both the query and the `Include`. — Gert Arnold, Jun 06 '22 at 09:36

score 0 · Answer 1 · answered Mar 31 '22 at 13:53

0

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

answered Mar 31 '22 at 13:53

Aron

15,464
3
31
64

I tried this but EF will make the following code from this resulting in even people with an old contact to popup (so some will have 2 or 3 rows in the results) SELECT * FROM [Employees] AS [e] LEFT JOIN [Employment] AS [e1] ON [e].[ID] = [e1].[Employee_ID] WHERE EXISTS ( SELECT 1 FROM [Employment] AS [e0] WHERE ([e].[ID] = [e0].[Employee_ID]) AND (([e0].[StartDate] <= @__date_0) AND (([e0].[EndDate] IS NULL) OR ([e0].[EndDate] >= @__date_1)))) – Maurice Lucas Mar 31 '22 at 14:19
While the code below does give me the right amount of results SELECT * FROM [Employees] AS [e] LEFT JOIN [Employment] AS [e1] ON [e].[ID] = [e1].[Employee_ID] WHERE ([e].[ID] = [e1].[Employee_ID]) AND (([e1].[StartDate] <= @__date_0) AND (([e1].[EndDate] IS NULL) OR ([e1].[EndDate] >= @__date_1))) – Maurice Lucas Mar 31 '22 at 14:19

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

1 Answers1