Update a column in a datatable using LINQ

[yash.choursia]

Hi Team,

Above is my column X, i have a condition that if the value of this column is greater than -1 and less than 1 , then i will replace the current value with 0 otherwise leaving the value as it is.

[yash.choursia]

@supermanPunch any help?

[rlgandu (Rajyalakshmi Gandu)]

@yash.choursia

updatedDataTable = (From row In dataTable.AsEnumerable()
Let value = Convert.ToDouble(row(“Column_X”))
Let updatedValue = If(value > -1 AndAlso value < 1, 0, value)
Select dt.Clone().Rows.Add(row.ItemArray)).CopyToDataTable()

[postwick (Paul)]

How to Update Data Column Values of a Data Table | Community Blog

[Dinesh_Guptil]

Hi @yash.choursia

write the new dt based on condition using below linq

syntax : DT.AsEnumerable.Where(Function(r) if(double.parse(r(column name).ToString) < 1 andalso double.parse(r(columnname).ToString) > -1 ,True,False)).CopyToDataTable

and write the new dt values as zero using below linq

syntax : New DT.AsEnumerable.select(Function(r) New DT.Clone.LoadDataRow(New Object(){r(0).ToString,“0”},false)).copytodatatable

[pravallikapaluri]

Hi @yash.choursia

Assign activity:
updatedColumnX = columnX.Select(Function(x) If(Double.TryParse(x.ToString(), outValue), 
                                                 If(outValue >= -1 AndAlso outValue <= 1, 0, outValue), 
                                                 x)).ToArray()

[Moulika_Kaviti]

Use invoke code activity

For Each r As DataRow In in_dt.AsEnumerable
r(“Difference between Before Tax Amount”)=If((Math.Abs(CDbl(r(“Difference between Before Tax Amount”).ToString)))<1, “0”, r(“Difference between Before Tax Amount”).ToString)
Next r

[ppr (Peter Preuss)]

Cross Reference:

Linq If Condition

Hi Team, Attaching an excel, i want that if column R is lessthan 1 or greater than -1 then replace it with 0. I want it to do it with the help of LINQ. Please help. OutputFile.xlsx (1.3 MB)

[supermanPunch (Arpan)]

Hi @yash.choursia ,

An Alternate with Data Column Expression (As it is an easier/simpler operation) :

Implementation :

Here, Notice the use of Add Data Column to add another column for updating the newer values.

We then use the Data Column Expression using Assign in the below Way :

DT.Columns("Updated Column").Expression = "IIF(CONVERT([Difference],'System.Double')>-1 And CONVERT([Difference],'System.Double')<1,0,CONVERT([Difference],'System.Double'))"

We can also simply remove the Older Column with DefaultView.ToTable() and rename the new column to the old one. (We do see additional steps involved)

Debug Panel :