How to select values in list that are NOT IN a Table using EF Core?

Question

I have a quite big list of strings (30k+) and I need to check which ones do not exist on a table using Entity Framework Core.

Something like this but without sending a request per item to check:

var notFoundItems = hugeList.Where(c => !tableToCheck.Any(x => x.Id == c)).ToList();

I found an answer but using T-SQL

Answer 1

T-SQL can be a good approach, but in your case you would have to create a temporary table and make a join. 30k is not too many records, so it will probably be easier to compare records on the application side. In that case, you could do this:

var idList = tableToCheck.Select(x => x.id).ToList();
var notFoundItems = hugeList.Where(item => idList.All(id => id != item));

Since your strings from the database are IDs, you can do even better and use HashSet and Contains method, which has complexity of O(1):

var idSet = tableToCheck.Select(x => x.id).ToHashSet();
var notFoundItems = hugeList.Where(item => !idSet.Contains(item));

Ultimately, the performance depends on the size of the data set in the DB. If the DB table is huge and you would have to fetch millions of IDs, then the T-SQL approach will be faster.