Joining to in memory List

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.

I have recently built a simple method to do this entirely in-database, by converting the static data into a query that returns a result set of the data. This produces a standard IQueryable which can be fed into the rest of the query. My usecase was to perform in-database joins, but I'm sure there are many other uses.

The general strategy was to construct a query of SELECTs with provided values, that are concatenated together with UNION ALL. I avoided the VALUES syntax since the syntax is inexplicably different across Postgres, MySql, and MS SQL.

SELECT "Data1A" AS columnA, "Data1B" AS columnB UNION ALL "Data2A", "Data2B" UNION ALL "Data3A", "Data3B" UNION ALL "Data4A", "Data4B" -- and so on, and so forth... ; 

This can then be used as a subquery inside the main query, and joined on just like any other result set.

To use this with EF, the raw SQL is constructed with a StringBuilder and then executed using FromSqlRaw().

In EF 7 and below, the only nice way to execute this SQL is to first create a DbSet in the DbContext that acts as a virtual table representing the data being returned:

public DbSet<StaticDataEntity> StubDbSet { get; set; } // ... // Exclude the DbSet from all migrations, since we don't actually want it to be a real table in the database. builder.Entity<StaticDataEntity>().HasNoKey().ToTable("tmp_stub_table", t => t.ExcludeFromMigrations()); 

EF 8 will enable returning unmapped entities from raw SQL, removing the above ceremony of creating a stub DB set entirely.

Then, you can use this extension method over that DbSet that generates a raw SQL query, giving you an IQueryable to join against.

Example entity class:

public class StaticDataEntity { public string A { get;set; } public string B { get;set; } } 

public static IQueryable<StaticDataEntity> CreateResultSetFromData(this DbSet<StaticDataEntity> dbSet, List<StaticDataEntity> values) { // StaticDataEntity is the entity POCO class. This one has two properties, A and B, corresponding to two columns of data. var columnNames = (A: nameof(StaticDataEntity.A), B: nameof(StaticDataEntity.B)); if (values.Count > 0) { // Create a UNION of SELECT "PredefinedDataA" AS `A`, SELECT "PredefinedDataB" AS `B` // This generates a temporary result set from the input values. string[] staticDataArray = new string[values.Count * 2]; var queryStringBuilder = new StringBuilder(); for (int i = 0; i < values.Count; i++) { int dataIndex = i * 2; if (i == 0) { queryStringBuilder.Append($"SELECT {{{dataIndex}}} AS {columnNames.A}, {{{dataIndex + 1}}} AS {columnNames.B} "); } else { queryStringBuilder.Append($"UNION SELECT {{{dataIndex}}}, {{{dataIndex + 1}}} "); } // Build one-dimensional array of the values to pass into FromSqlRaw as parameters staticDataArray[dataIndex] = values[i].A; staticDataArray[dataIndex + 1] = values[i].B; } return dbSet.FromSqlRaw(queryStringBuilder.ToString(), staticData).AsNoTracking(); } else { // No data, return empty result set return dbSet.FromSqlRaw( $"SELECT NULL AS {columnNames.A}, NULL AS {columnNames.B} WHERE FALSE", null ).AsNoTracking(); } } 

Now, you can do:

// TODO: Populate data list with entries var data = new List<StaticDataEntity>(); // Create static data query IQueryable<StaticDataEntity> dataQuery = dbContext.StubDbSet.CreateResultSetFromData(data); // Join static data column A to in-database "SomeEntities" table column C. // Return static A and B, and database column C and D. var resultQuery = dbContext.SomeEntities.Join( dataQuery, se => se.C, sde => sde.A, (se, sde) => new { sde.A, sde.B, se.C, se.D }); // Execute the query var result = await resultQuery.ToListAsync();