Get what exists in one DataTable but not another using LINQ

c# linq datatable

11,127

Solution 1

This will do the trick for you:

var rowsOnlyInDt1 = dt1.AsEnumerable().Where(r => !dt2.AsEnumerable()
                    //make sure there aren't any matching names in dt2
                    .Any(r2 => r["crs_name"].Trim().ToLower() == r2["crs_name"].Trim().ToLower() && r["name"].Trim().ToLower() == r2["name"].Trim().ToLower()));

or if you prefer query syntax:

var rowsOnlyInDt1 = from r in dt1.AsEnumerable()
                    //make sure there aren't any matching names in dt2
                    where !dt2.AsEnumerable().Any(r2 => r["crs_name"].Trim().ToLower() == r2["crs_name"].Trim().ToLower() && r["name"].Trim().ToLower() == r2["name"].Trim().ToLower())
                    select r;

You can then put the results into a DataTable by using the CopyToDataTable function:

DataTable result = rowsOnlyInDt1.CopyToDataTable();

Solution 2

You want to use the Except extension.

Here is the link to the linq on MSDN.

From what I get from the question somthing like.

var theNonIntersect = 
    dt1.AsEnumerable().select(r => r.Field<string>("crs_name"), r.Field<string>("name"))
        .Except(dt2.AsEnumerable().select(r => r.Field<string>("crs_name"), r.Field<string>("name")));

11,127

Author by

Anyname Donotcare

Updated on June 28, 2022

Comments

Anyname Donotcare almost 2 years
I have two DataTables. I want to get what exists in the first one but does not exist in the second one. I would like the results in another DataTable. I would like to use LINQ.
- The First datatable:
```
DataTable dt1 = cc1roleDAL.GetAll(x, 0);
```
- The second datatable:
```
DataTable dt2 = cc1roleDAL.GetSpecific(x);
```
Note: the column names I return from the the two datatables:
1. crs_name
2. name
Anyname Donotcare almost 13 years

can't convert lambda expression to string because it's not a delegate
Anyname Donotcare almost 13 years

hmmm, I'm not use LINQ to sql .just wanna a LINQ answer to my question.I don't know what is db?and the comparsion should be among a.crs_name and b.crs_name a.name and b.name
Jodrell almost 13 years

try that, added the Linq to DataTable extensions
Anyname Donotcare almost 13 years

hmmmm .no syntax error but sorry it doesn't work. i get the same records in the first datatable .
Anyname Donotcare almost 13 years

the comparsion should be among a.crs_name and b.crs_name a.name and b.name.. Not between crs_name and name.
Anyname Donotcare almost 13 years

hmmm, r.Field<string>("name")syntax error here . r doesn't exist n the current context
Anyname Donotcare almost 13 years

i mean there are two fields with the same name in the two datatables: dt1:crs_name,name AND dt2:crs_name,name.
Anyname Donotcare almost 13 years

i wanna to compare the first field with the first and the second with the second.
Doctor Jones almost 13 years

Aha! Ok I understand now. Give me a sec and I'll sort it
Doctor Jones almost 13 years

I've updated my answer, is that more along the lines of what you want?
Anyname Donotcare over 12 years

Thanks, but when i trace the code :rowsOnlyInDt1 is two records the same as dt1. although:dt2 has one record where crs_name in the dt2 = crs_name in dt1 and name in dt2 = name in dt1. i don't know what is the problem!!!
Doctor Jones over 12 years

is it a whitespace or casing issue? I've added calls to Trim and ToLower to my answer, does that fix it?
Anyname Donotcare over 12 years

r["crs_name"].ToString().TrimEnd() == r2["crs_name"].ToString().TrimEnd() && r["name"].ToString().TrimEnd() == r2["name"].ToString().TrimEnd()));