LINQ "not in" datatable

[ajh]

Hi,
How can I return datatable rows which are not matching in another datatable?

Return all rows from DT1 which are not found at DT2 using column DT1.Manufacturer values not in DT2.MFr

Schema is different on both datatables.

[ashwin.ashok ((TheCodingTheory))]

Hi @ajh ,

If the Tables share the exact same schema, then this could you try this?

DT1.AsEnumerable().Except(DT2.AsEnumerable(), DataRowComparer.Default).CopyToDataTable()

If you want to do so on the basis of the two columns, then could you give this a try?

(From r1 In DT1.AsEnumerable()
Group Join r2 In DT2.AsEnumerable()
On r1("Manufacturer").ToString.Trim Equals r2("MFr").ToString.Trim Into gj = Group
From g In gj.DefaultIfEmpty
Where IsNothing(g)
Select r1).CopyToDataTable()

Kind Regards,
Ashwin A.K

[Srini84 (Srinivas Kadamati)]

@ajh

Check below for your reference

How to compare 2 data tables and get only unmatched rows

Hi All, I have a data table and iam using for each loop to check differnt conditions. One of the condition i will get out put as one more datatable how to compare this datatable with the one original datatable. Regards, Hima

Hope this will help you

Thanks

[Mr_JDavey (Joshua Davey)]

Hi there @ajh,
I trust you are well!

Are you able to provide the schema for the Tables/confirm they also contain the “Manufacturer” field?

Thanks once again for your continued support,
Josh

[ppr (Peter Preuss)]

we can implement like:

drUnmatched | List(Of DataRow)
We do defensively use the CopyToDataTable depending if we got returned data rows in order to avoid a no rows … exception

LINQ:

(From d1 In dt1.AsEnumerable
Where  Not dt2.AsEnumerable.Any(Function (d2) d2(0).toString.Trim.Equals(d1(0).toString.Trim))
Select r=d1).toList

About the other approaches:

Set Operation Except:
It will handle all columns and will remove duplicated rows from the result. This can lead to negative side effects

Left Join (LINQ: Group Join)
In case of more rows matching the join condition will occur, then additional rows will be returned as in the nature of a join all matching rows will be paired together.
dt1: A1|1, A2|2
dt2: A1|*, A1|+, A3|$

will have a left join result of
A1|1,A1|*
A1|1,A1|+
A1|1,A1|*
A1|1,A1|+

as first A1 will be joined with both right A1
and second A1 will be joined with both rigth A1

Find starter help here:
GetLeftWhenMatchRight_1Col_AnyApproach.xaml (14.8 KB)

[ajh]

Schema is completely different between datatables, I will try ashwin.ashok two columns method

[Ramya_K]

@ajh,

You can also use the below LINQ query to get unmatched rows.

Please try if useful.

DT1.AsEnumerable.Where(Function(sourceRow) Not DT2.AsEnumerable.Any(Function(destRow) sourceRow(“Manufacturer”).ToString = destRow(“MFr”).ToString)).CopyToDataTable