Using ThenInclude with condition in Entity Framework

Question

return _companyRepository.GetAll().Where(company => company.Id == Id)
             .Include(company => company.offices.Where(o => o.IsActive))
             .ThenInclude(office => office.People.Where(p => p.IsFired))
             .ThenInclude(person => person.Children)
             .ToList(); 

How can I get all fired people of all active offices using Entity Framework Core?

I'm getting this error:

Where clause in not valid inside ThenInclude or Include

company.offices.Where(o => o.IsActive) 
office.People.Where(p => p.IsFired)

I know I can not use Where inside Include. the question is how to filter data with Include clause?

Answer 1

It's not possible.

I'd suggest to look around for any of the 3rd party libraries listed there, example :

EntityFramework.Filters

Query IncludeFilter

How to filter “Include” entities in entity framework?

Answer 2

You do not need to use any Include, in case of you use your data in your query. Include is just a way to disable Lazy Loading. Include controls whether a navigation property is filled with data or not. Include is not made to filter anything.

Your query will also return companies (with people) not people.

  return _companyRepository.GetAll().Where(company => company.Id == Id)
         .SelectMany(company => company.offices)
         .Where(o => o.IsActive)
         .SelectMany(office => office.People)
         .Where(p => p.IsFired) 
         .Include(person => person.Children)
         .ToList(); 

This creates a List with persons, including their children. The last include controls says, all the children are loaded with this query. If you remove this include, you still can access the children, but than it is load-on-demand or lazy-loading. You will not notice any difference in the functions of your program, but in the communication with your sql-server.