Linq query ("not in ") on datatable

Question

Hi

I would like to return all rows from TableA table that does not exists in another table.

e.g. select bench_id from TableA where bench_id not in (select bench_id from TableB )

can you please help me write equivalent LINQ query. Here TableA source : Excel and TableB source : Database

I am loading Excel sheet data into DataTable : TableA and TableB I am loading from Database. In short, TableA and TableB is type of DataTable

Thanks in advance.

Regards Vick

Answer 1

So if table A is from Excel, are you loading the data into memory first? If so (i.e. you're using LINQ to Objects) then I suggest you load the IDs in table B into a set and then use:

var query = tableA.Where(entry => !tableBIdSet.Contains(entry.Id));

If this isn't appropriate, please give more details.

Converting into a set is probably best done just by using the HashSet constructor which takes an IEnumerable<T>. For example:

var tableBIdSet = new HashSet<string>(db.TableB.Select(entry => entry.Id));

(If the IDs aren't actually distinct, you could add a call to Distinct() at the end.)

Answer 2

From a in TableA
Group Join b in TableB on a.bench_id Equalsb.bench_id into g = Group
Where g.Count = 0
Select a