Joining to in memory List

I have an in memory list that I am joining to results from a query using entity framework. My list will most likely never be greater than 2500 records. The results from the database can fluctuate, depending on the filters used and it will grow in size. I've been able to join the results successfully but it feels a bit sluggish and I worry as the database grows that it could get worse. Is there anything I can do to make this more efficient? Please let me know if you need any additional information. Thanks!

var query = from e in _context.Employees
            where (...filters...)
            select e;

var employees = query.AsEnumerable();
var offices = _officeService.GetAllOffices();

var employeeData = from e in employees
                   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
                   };

return employeeData;

The dilemmas here:

-
query is an IQueryable. If you join it with offices, i.e. without AsEnumerable(), 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.

-
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
                   };

StackExchange Code Review Q#161741, answer score: 10