How to delete the rows of datatable1 having different ID while compare with the datatable2

Question

Given the first data table having master data and the second table having the needed data IDs, I need to delete the rows from the first table which are not present in the 2nd table. I am trying to use LINQ.

DataTable 1:

NO	Name	Position	Group
1	John	Painter	India
2	MArk	Washer	India
3	Peter	Officer	Nepal
4	Angel	Officer	India
5	Bibin	Washer	Nepal
6	Jerry	Painter	Nepal

Datatable 2:

NO
1
2
3

Result Table:

NO	Name	Position	Group
1	John	Painter	India
2	MArk	Washer	India
3	Peter	Officer	Nepal

I tried this query:

 var qry1 = datatable1.AsEnumerable().Select(a => new { NO.ToString() });
 var qry2 = datatable2.AsEnumerable().Select(b => new { NO.ToString() });
 var newTable = qry1.Except(qry2);

but I get the rows which are not there. Many process I tried to get the result.

Answer 1

I am assuming that (1) you wish to select matching rows from your datatables and not actually delete unmatched rows from your source, and (2) that you want entire rows in your results and not just the NO column values.

Perhaps one of the following is what you need:

var query =
    from t1 in datatable1.AsEnumerable()
    join t2 in datatable2.AsEnumerable() on t1.NO equals t2.NO
    select t1;

var query = datatable1.AsEnumerable()
    .Where(t1 => datatable2.AsEnumerable().Select(t2 => t2.NO).Contains(t1.NO));

var query = datatable1.AsEnumerable()
    .Where(t1 => datatable2.AsEnumerable().Any(t2 => t2.NO == t1.NO);

var selectedKeys = datatable2.AsEnumerable().Select(t2 => t2.NO).ToHashSet();
var query = datatable1.AsEnumerable().Where(t1 => selectedKeys.Contains(t1.NO));

If the datatable2 collection is large, I would lean towards the join or HashSet versions for improved efficiency. HashSet lookups are much faster that repeated Enumerable scans.