Filter child entity LINQ methods

Question

Consider the following query :

  IQueryable<Employee> ret = this.ObjectContext.Employees
            .Include("EmployeeInformation")
            .Include("LatestInformation")
            .Where(emp => emp.idJobTitle == 1 && emp.idCrtLoc == 1);

The Employees entity doesn't have a navigation property to LatestInformation's entity(So I can't directly access the other entity) but the LatestInformation does have a navigation property to the Employees entity.

How can I filter the LatestInformation entity of this query?

The expected query should look like this :

ret = ret.Where(r=> LatestInformation.Where(li => li.year == 2015)); // Ofcourse this piece of code is wrong.

So , the question was how to filter the LatestInformation entity ?

Answer 1

If there is no navigation property from Employee to LatestInformation, query it the other way around. Something like:

var ret = this.ObjectContext.LatestInfomration
    .Where(i => i.Employee != null && i.year == 2015)
    .Select(i => i.Employee)
    .Where(emp => emp.idJobTitle == 1 && emp.idCrtLoc == 1);

---

Edited to match OP comment below:

// Extract needed data:
var employeeIdListWithInfo = this.ObjectContext.LatestInfomration
    .Where(i => i.Employee != null)
    .Select(i => i.Employee.Id)
    .ToList();

// Build the query (not executed yet)
var employeeWithInformation = this.ObjectContext.LatestInfomration
    .Where(i => i.Employee != null && i.year == 2015)
    .Select(i => i.Employee)
    .Where(emp => emp.idJobTitle == 1 && emp.idCrtLoc == 1);

var lonelyEmployees = this.ObjectContext.Employee
    .Where(emp => !employeeIdListWithInfo.Contains(emp.Id));

var ret = employeeWithInformation.Union(lonelyEmployees);