CODEWITHSUNDEEP
linq
vick
vick

Reputation:

Linq query ("not in ") on datatable

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 is from Excel and TableB is from a Database

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

Upvotes: 1

Views: 5221

Answers (3)

Chintan Udeshi
Chintan Udeshi

Reputation: 11

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();

Upvotes: 1

gfrizzle
gfrizzle

Reputation: 12609

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

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1500855

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.)

Upvotes: 4

Related Questions