LINQ query dropping includes when adding `.Contains()` in where clause

Question

I have a somewhat complex query I'm trying to build in Linq (EntityFramework Core 2.1), and I hit behavior I can't comprehend. The below query runs well and seemingly efficiently:

var q = (
from n in TaskUpdates.Include(t => t.Status).Include("Task").Include("Task.Requirement").Include("User").Include("User.Employee")
where n.User.Employee.EmployeeNumber == 765448466
group n by n.UpdateDate into tu
select tu.OrderByDescending(t=>t.UpdateDate).FirstOrDefault()
)
.Select(x => x.Task.Requirement);

This works as I'd expect, does all the joins I want and includes the expected fields in the SELECT clause:

SELECT [t].[TaskUpdateID], [t].[Active], [t].[TaskId], [t].[Notes], [t].[StatusId], [t].[UpdateDate], [t].[UserId], [t.Task].[TaskID], [t.Task].[Active], [t.Task].[CreatedDate], [t.Task].[RequirementId], [t.Task].[UserId], [t.Task.Requirement].[RequirementID], [t.Task.Requirement].[Active], [t.Task.Requirement].[Description], [t.Task.Requirement].[Hours], [t.Task.Requirement].[Link], [t.Task.Requirement].[Name], [t.Task.Requirement].[RequirementTypeId], [t.Task.Requirement].[ExternalId], [t.Task.Requirement].[SortOrder], [t.Status].[StatusId], [t.Status].[Active], [t.Status].[IsComplete], [t.Status].[Title], [t.User].[UserId], [t.User].[Active], [t.User].[Created], [t.User].[EmployeeNumber], [t.User].[LastLogin], [t.User].[LastUpdated], [t.User.Employee].[EMPLOYEENUMBER], [t.User.Employee].[BEGINDATE], [t.User.Employee].[CITY], [t.User.Employee].[EMPLOYEETYPE], [t.User.Employee].[ENDDATE], [t.User.Employee].[FIRST_NAME], [t.User.Employee].[GENERATION_SUFFIX], [t.User.Employee].[STATUS], [t.User.Employee].[LAST_NAME], [t.User.Employee].[MIDDLE_NAME], [t.User.Employee].[MOBILE], [t.User.Employee].[ORGCODE], [t.User.Employee].[PHONE_NUMBER], [t.User.Employee].[PRIMARYEMAIL], [t.User.Employee].[STATE], [t.User.Employee].[STREET], [t.User.Employee].[TITLE], [t.User.Employee].[ZIPCODE], [t.User.Employee].[BUILDING], [t.User.Employee].[ROOM]
FROM [TaskUpdates] AS [t]
INNER JOIN [Tasks] AS [t.Task] ON [t].[TaskId] = [t.Task].[TaskID]
LEFT JOIN [Requirements] AS [t.Task.Requirement] ON [t.Task].[RequirementId] = [t.Task.Requirement].[RequirementID]
INNER JOIN [Status] AS [t.Status] ON [t].[StatusId] = [t.Status].[StatusId]
INNER JOIN [Users] AS [t.User] ON [t].[UserId] = [t.User].[UserId]
INNER JOIN [DirectoryPeople] AS [t.User.Employee] ON [t.User].[EmployeeNumber] = [t.User.Employee].[EMPLOYEENUMBER]
WHERE [t.User.Employee].[EMPLOYEENUMBER] = 765448466
ORDER BY [t].[UpdateDate]
GO

(I'm using LINQPad to experiment with this query and get the SQL.) In particular, the ending .Select(...) method correctly returns the Requirement object from the query.

What baffles me is if I want to make this query return data for multiple employees, and I change the where clause like so:

var employeeNumbers = new int[] { 765448466 };

var q = (
from n in TaskUpdates.Include(t => t.Status).Include("Task").Include("Task.Requirement").Include("User").Include("User.Employee")
//where n.User.Employee.EmployeeNumber == 765448466
where employeeNumbers.Contains(n.User.Employee.EmployeeNumber)
group n by n.UpdateDate into tu
select tu.OrderByDescending(t=>t.UpdateDate).FirstOrDefault()
)
.Select(x => x.Task.Requirement);

This changes the resulting SQL WHERE clause exactly as I would expect, but it now completely ignores the Includes in the from clause:

SELECT [t].[TaskUpdateID], [t].[Active], [t].[TaskId], [t].[Notes], [t].[StatusId], [t].[UpdateDate], [t].[UserId]
FROM [TaskUpdates] AS [t]
INNER JOIN [Users] AS [t.User] ON [t].[UserId] = [t.User].[UserId]
INNER JOIN [DirectoryPeople] AS [t.User.Employee] ON [t.User].[EmployeeNumber] = [t.User.Employee].[EMPLOYEENUMBER]
WHERE [t.User.Employee].[EMPLOYEENUMBER] IN (765448466)
ORDER BY [t].[UpdateDate]
GO

(only joins as necessary to execute the where) and the result of the final .Select(...) now returns null.

Is this known behavior, with or without explanation? Am I using the Include directives incorrectly, or is there a better way/place for them to go that will resolve this issue?

Nick Spicer Nick Spicer · Accepted Answer · 2020-06-23T12:52:16

I can't say for certain the cause, I would suspect EF is going down a different translation path with the Contains and missing the Includes, however as you can see it's not translating the GroupBy at all, so it can definitely be reworked to match more the EF style.

TaskUpdates
  .Include(x => x.Task)
      .ThenInclude(x => x.Requirement)
  .Where(x => employeeNumbers.Contains(x.User.Employee.EmployeeNumber))
  .ToList()
  .GroupBy(x => x.UpdateDate)
  .Select(x => new {
      UpdateDate = x.Key,
      FirstRequirement = x.First().Task.Requirement
  })
  .ToList();

This should translate the statements before the first ToList into SQL, populate the results in-memory and allow C# to do the groupby and aggregates on the whole object which SQL would be unable to do.

LINQ query dropping includes when adding `.Contains()` in where clause

1 Answers