Linq query ("not in") on datatable
I would like to return all rows from a table TableA
that does not exist in another table.
eg.
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
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.)
source to share
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();
source to share