Unmatched records using LINQ

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

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!

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

Hi
Kindly have a view on this thread

Cheers @hasib08

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

1 Like

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

Error

1 Like

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

Error
Identifier expected
Need to use LINQ

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

Explain this ?
How to modify my code

@Palaniyappan

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

1 Like

@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

1 Like

Error

@ppr @Palaniyappan

@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

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

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

1 Like

@hasib08

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

DT01 has 10 rows and DT02 has 12 rows

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

@ppr

then it should be like ths
like Dt02 should come first

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

Cheers @hasib08