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

Since you already have a T-SQL solution, this is a good case for using a Raw SQL Query. There will certainly be cases where a SQL query cannot be expressed in LINQ or the LINQ statement doesn't generate an optimized query.

Answer 2

You can make a select that search all the strings that exists on the table and put in a list. After you check what strings that not exists.

Example:

var foundItems = tableToCheck
                .Where(x => hugeList.Contains(x.id))
                .Select(x => x.id)
                .Distinct()
                .ToList();

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

This way you do just one request to the db.

Answer 3

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.