How to compare the two rows in single datatable using LINQ query?

Hi Team,

I need to filter the datatable using common data in two columns using below link query, but its not working properly. Could you please suggest.

Dt1=Dt1.AsEnumerable.Where(Function(row) (row(“Col1name”).ToString.trim).Equals( row(“COl2name”).ToString.trim)).CopyToDataTable

(From row in datatable.asenumerable() let ia=row.item(“column1”).tostring let ib=row.item(“column2”).tostring where(ia.equal(ib)) select row).copytodatable

This will compare each row column 1 and column 2 If it equal it will copy only that rows.

Can you please tell us what the exact issue with the output. also show us the data table’s data as excel sheet.

Hi,

Your expression seems no problem. You might need to check data in details.
For example, there is possibility there are different type whitespaces in each column.
If so, the following might work.

Dt1 = Dt1.AsEnumerable.Where(Function(row) (System.Text.RegularExpressions.Regex.Replace(row("Col1name").ToString,"\s+"," ")).Equals(System.Text.RegularExpressions.Regex.Replace(row("Col2name").ToString,"\s+"," "))).CopyToDataTable()

Regards,

1 Like

image

Am trying to compare the columns as mentioned in the image but this using filter activity am not able to get the output as it having data in dt, instead planning to replace the LINQ query. Could you please help

Hi, you can follow the above given solution by @Yoichi .
You cannot put column name in the value section like that, it requires the value not the column name.