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
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 just be simpler to compare the 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 => ids.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.