score:0

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.

score:0

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.

score:5

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.


Related Query