Unmatched records using LINQ

[hasib08 (Motiwala Hasib)]

Need to fetch unmatched records from 2 data tables using LINQ query.

[kadiravan_kalidoss (Kadiravan K)]

Get Unmatched Records in DT1: @hasib08,

Dt1.AsEnumerable().Where(Function(row) NOT Dt2.AsEnumerable.Any(Function(x) x(“Column1”).ToString=row(“Column1”).ToString And x(“Column2”).ToString=row(“Column2”).ToString And x(“Column3”).ToString=row(“Column3”).ToString)).CopyToDatatable

Thanks!

[hasib08 (Motiwala Hasib)]

I have join 2 tables and storing the data into 3rd table.
So how to find the unmatched values.
@kadiravan_kalidoss

[Palaniyappan (Forum Leader)]

Hi
Kindly have a view on this thread

Compare Datatables one specific column with other specific Column and Get Matched and Not Matched Records`

Hi Aksh, this is great, thanks for sharing Would it be possible to use a similar formula to find partial string matches between sheets? So it would look in a column in DT1 to see if the string contains any of the strings in a column in DT2. e.g. “brand product type description name” in DT1 contains “product type” in DT2. I’m hoping for it to return the string and the cell address of the matched cell in DT2.

Cheers @hasib08

[hasib08 (Motiwala Hasib)]

Didn’t get this code bro.

Code im using for getting matched record
(From a In DT01.AsEnumerable() Join b In DT02.AsEnumerable()
On a(“number”).ToString Equals b(“number”).ToString
Select DT04.LoadDataRow (New Object() {
b.Field(Of Object)(“number”),
b.Field(Of String)(“amount”)
},False)).CopyToDataTable

So, how to modify it to get unmatched records.

@Palaniyappan @kadiravan_kalidoss @aksh1yadav

[Palaniyappan (Forum Leader)]

Did we try like this
(From a In DT01.AsEnumerable() Join b In DT02.AsEnumerable()
On a(“number”).ToString != b(“number”).ToString
Select DT04.LoadDataRow (New Object() {
b.Field(Of Object)(“number”),
b.Field(Of String)(“amount”)
},False)).CopyToDataTable

Cheers @hasib08

[hasib08 (Motiwala Hasib)]

@Palaniyappan

From a In DT01.AsEnumerable() Join b In DT02.AsEnumerable()
On NOT a(“number”).ToString Equals b(“number”).ToString
Select DT04.LoadDataRow (New Object() {
b.Field(Of Object)(“number”),
b.Field(Of String)(“amount”)
},False)).CopyToDataTable

Error

[Palaniyappan (Forum Leader)]

Sorry I made changes in that kindly try with this
(From a In DT01.AsEnumerable() Join b In DT02.AsEnumerable()
On a(“number”).ToString != b(“number”).ToString
Select DT04.LoadDataRow (New Object() {
b.Field(Of Object)(“number”),
b.Field(Of String)(“amount”)
},False)).CopyToDataTable

Cheers @hasib08

[hasib08 (Motiwala Hasib)]

@Palaniyappan

Sorry I made changes in that kindly try with this
(From a In DT01.AsEnumerable() Join b In DT02.AsEnumerable()
On a(“number”).ToString != b(“number”).ToString
Select DT04.LoadDataRow (New Object() {
b.Field(Of Object)(“number”),
b.Field(Of String)(“amount”)
},False)).CopyToDataTable

Error
Identifier expected
Need to use LINQ

[Palaniyappan (Forum Leader)]

Hi
Let’s try like but I havent tried this
Just a thought
once after getting the matched datatable from the original one now use a assign activity like this
Newdatatable = originaldatatae.AsEnumerable().Except(dtCompareData.AsEnumerable()

Cheers @hasib08

[hasib08 (Motiwala Hasib)]

Explain this ?
How to modify my code

@Palaniyappan

[Palaniyappan (Forum Leader)]

no need of modification with already existing one
but
once after getting the matched records with a linq query like lets say you have stored that in a variable named dt_1
and let the original datatable with all records be named as dt

now we need the unmatched records
dt_2 = dt.AsEnumerable().Except(dt_1.AsEnumerable())

where dt_2 is a variable of type datatable which will have the unmatched datarows in it

Cheers @hasib08

[ppr (Peter Preuss)]

@hasib08
PFA demo xaml showcasing the find matched / unmatched rows from Dt1 to Dt2
hasib08.xaml (9.4 KB)

And with variations here:
hasib08_II.xaml (10.7 KB)

In general above suggested approaches were ok, but had some syntax issues. Therefore you received some minor errors. So run the demo xaml standalone and then incorporate the building blocks to your implementation.

Kindly Note: Any CopyToDataTable will throw an exception in case of no rows are returned from a statement. If this risk has to handle, so incorporate some checks before executing CopyToDataTable

Let us know your feedback

[hasib08 (Motiwala Hasib)]

Error

@ppr @Palaniyappan

[ppr (Peter Preuss)]

@hasib08
Thats the Case i was telling. There is no row returned, so Copy to Datatable is throwing Error.

You can fix IT by Let returning the Statement a list of datarows. If the Count of the list is >0 then execute CopyToDataTable

[Palaniyappan (Forum Leader)]

does DT01 and DT02 has records in it
to validate that before to this assign activity use a WRITELINE activity and mention like this
DT01.Rows.Count.ToString
and another writeline with
DT02.Rows.Count.ToString

so that we will be able to see the row counts
@hasib08

[Harshithh (Harshith Adyanthaya)]

To Get Matched Records

Datatable Out_Matched_Data = In_DataTable1.AsEnumerable().Where(function(row) In_DataTable2.AsEnumerable().Select(function(r) r.Field(Of Int32)(In_DT2_ColName_To_Match.ToString)).Any(function(x) x = row.Field(Of Int32)(In_DT1_ColName_To_Match.ToString))).CopyToDataTable()

To Get Not Matched Records

Datatable Out_NonMatched_Data = In_DataTable1.AsEnumerable().Where(function(row) Not In_DataTable2.AsEnumerable().Select(function(r) r.Field(Of Int32)(In_DT2_ColName_To_Match.ToString)).Any(function(x) x = row.Field(Of Int32)(In_DT1_ColName_To_Match.ToString))).CopyToDataTable()

[ppr (Peter Preuss)]

@hasib08

see base handling the no records case here:
hasib08_II_0.2.xaml (12.1 KB)

[hasib08 (Motiwala Hasib)]

@Palaniyappan

DT01.Rows.Count.ToString

DT01 has 10 rows and DT02 has 12 rows

I want that 2 unmatched records to be stored in DT03 using LINQ

@ppr

[Palaniyappan (Forum Leader)]

then it should be like ths
like Dt02 should come first

Dt02.AsEnumerable().Except(Dt01.AsEnumerable())

Cheers @hasib08