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

c# ef-core-2.1 entity-framework entity-framework-core linq

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

1
3
11/7/2018 6:12:40 PM

Popular Answer

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.

1
1/28/2020 5:04:27 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow