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;

• Why not let the database do the JOIN? – Sergey Slepov Apr 25 '17 at 14:22
• The data in the list is pulled from a web api and does not reside in my database. – DrivenTooFar Apr 25 '17 at 14:49

The dilemmas here:

1. 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.

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

• query is an IQueryable. If you join it with offices, Entity Framework will throw not quite. OP uses AsEnumerable so it won't throw. – t3chb0t Apr 25 '17 at 15:56
• @t3chb0t I mean, if you join it as such, without AsEnumerable. – Gert Arnold Apr 25 '17 at 15:58
• It's hard to optimize this query. I was also thinking of officesCodes.Contains so that SQL can use IN but now we actually might hit the limit of it Limit on the WHERE col IN (…) condition if there are too many offices and 2500 is not that far from it. – t3chb0t Apr 26 '17 at 4:22
• @t3chb0t There is a way out, but I'd love to hear some feedback from OP on this. If a number of 2500 is hardly ever reached and it's usually far less, it may be OK. – Gert Arnold Apr 26 '17 at 7:12
• So first of all, I tried your suggestion and it does seem to run faster so it has been optimized. Thank you for that. Secondly, while the total number of offices may be around 2500, I may be able to filter that list to be less since a user pulling this information will almost never need all of the results. Should I filter my office list and update my query? – DrivenTooFar Apr 26 '17 at 11:55

Gert Arnold gave a great answer but let me suggest one more soution to try. Yes, data which is got from another source (rather than DB) can be processed in 2 ways:

1. Download as small data part from DB to local as possible and join locally (usually using AsEnumerable() or basically ToList()). You got many good thoughts on this in other answers.
2. Another one is different - upload your local data to server somehow and perform query on DB side. Uploading can be done differently: using some temp tables OR using VALUES. Fortunately there is a small extension for EF now (for both EF6 and EF Core) which you could try (It is written by me). It is EntityFrameworkCore.MemoryJoin (name might be confusing, but it supports both EF6 and EF Core). As stated in author's article (me) it modifies SQL query passed to server and injects VALUES construction with data from your local list. And query is executed on DB server.

So in your case you may try the following:

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

// change 1: no need to use AsEnumerable now
var employees = query;

// change 2: get IQueryable representation using EntityFrameworkCore.MemoryJoin
var offices = context.FromLocalList(_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
};

// change 3 (suggested), let's return result list instead of IQueryable
return employeeData.ToList();


Using code above, query will be done on DB side. 2500 records should be ok to process (I used with 20k), but of course need to ensure this works fine for you.

• join locally - this isn't necessarily a good advice :-\ is there any reason you won't let the SQL Server do the job? – t3chb0t Mar 24 '18 at 13:31
• @t3chb0t There are different views about that going around. One says the DB should deliver as little data as possible and clean it's own data, the other says the DB should do as little as possible and if it's computationally cheaper to off-load more data, do just that. It's CPU vs bandwith basically. – Mast Mar 24 '18 at 14:34
• It's not hard to figure out that you're the author of this library. That doesn't matter, but you should add a disclaimer, for example looking like the one this user consistently adds to his posts. For the record, out of curiosity I tried your NuGet package, but doing something similar to the example in your blog, I couldn't get it working with EF-core (2.02) and EF 6.2.0 due to various exceptions. I realize that doesn't give you much to look into, but a comment is too short to give any more detail. – Gert Arnold Mar 24 '18 at 22:11
• OK, got it working in EF6. In EF-core it doesn't seem to recognize the EF-core DbContext type. Nevertheless, the first results look promising. The only thing that worries me a bit is the possible performance hit of generating value tuples when there are "many" of them. Did you do any benchmarking on that? Also, maybe you could add an answer to this question and have my vote - again. – Gert Arnold Mar 24 '18 at 22:48
• @t3chb0t Sorry, your comment confused me a lot. 1) What do you mean saying let the SQL Server do the job? How will SQL Server join data from DB to data from third party API? 2) Why do you think join locally IS my advice? I just suggested some solutions and both may work in some scenarios. (personally I prefer #2 btw) 3) Yes local join is not always a good idea, BUT sometimes EF basically CAN'T generate efficient query, so doing some job locally may be good. And btw EF Core does some job locally automatically.... etc – Tony Mar 26 '18 at 13:38