The dilemmas here:

1. query is an IQueryable. If you join it with offices, Entity Framework will throw an exception about primitive values, which is an obscure way of telling you that it can't translate offices into SQL.

2. So join in memory, i.e. with query.AsEnumerable(). But now all data from query will be pulled into memory, which has two adverse effects: neither the reduction in numbers of records by joining with offices nor the reduction in width of the result set by selecting only a restricted number of properties can be translated back to the SQL query.

You obviously want to benefit from both strands of data reduction.

As for the reduction in number of rows, there's no way to make Entity Framework join with local data other than lists of primitive values. Even then, joining is rather inefficient because EF has to convert the local list into a temporary SQL table (sort of), which requires a considerable amount of code. It's more efficient to use Contains, which translates into an IN statement:

var officesCodes = offices.Select(o => o.Code).ToList(); var employeeInfo = from e in employees where officesCodes.Contains(e.Office) select ... 

Now employeeInfo is an IQueryable, so it's possible to reduce the width of the result set by projection:

var employeeInfo = from e in employees where officesCodes.Contains(e.Office) select new { EmployeeId = e.EmployeeId, FullName = e.FullName, Office = e.Office, Position = e.Position, Languages = e.Languages }; 

This achieves the desired data reduction. But now you haven't got EmployeeData objects yet. Can't be done by this query, because they also contain data from offices. This final step can only be achieved by joining the result in memory with offices:

var employeeData = from e in employeeInfo.AsEnumerable() join o in offices on e.Office equals o.Code select new EmployeeData { EmployeeId = e.EmployeeId, FullName = e.FullName, Office = e.Office, Area = o.Area, Region = o.Region, OfficeName = o.Name, Position = e.Position, Languages = e.Languages };