Linq query ("not in") on datatable

I would like to return all rows from a table TableA

that does not exist in another table.


select bench_id from TableA where bench_id not in (select bench_id from TableB )


Could you please help me write an equivalent LINQ query. Here TableA

is from Excel, and TableB

- from the database

I load the data in an Excel sheet DataTable

, TableA

. TableB

I am loading from a database. In short, TableA

and TableB

- typeDataTable


source to share

3 answers

So if Table A is from Excel, do you load the data into memory first? If this is the case (i.e. you are using LINQ for objects) then I suggest you load the ids in table B into a set, then use:

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


If this does not fit, please provide more details.

Converting to a set is probably best done simply by using the HashSet constructor that takesIEnumerable<T>

. For example:

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


(If the IDs don't really differ from each other, you can add a call to Distinct () at the end.)



var lPenaltyEmployee = from row1 in tBal.getPenaltyEmployeeList().AsEnumerable()
                                   select row1;

var PenaltyEmp = new HashSet<string>(lPenaltyEmployee.Select(Entry => Entry.Emsrno);

DataTable lAbsentEmp = (from row in tBal.getAbsentEmployee(txtFromDate.Text).AsEnumerable()
                                    where !(PenaltyEmp).Contains(row["Emsrno"].ToString())
                                    select row).CopyToDataTable();




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




All Articles