Linq Compare 2 DataTable and Get Some Value from other DataTable

[Rpa12343 (Rpa)]

Hi All,

I want to compare 2 data tables and Get Some Value from other DataTable if Value “USER” and “ID” is match

thankyou all

[ppr (Peter Preuss)]

Can be done within a for each row (over dt1) and a lookup to dt2
If there is a lookup result, the dt1 cols can be updated

[Rpa12343 (Rpa)]

hello, it seems ineffective because I will process more than 100k data in each datatable

and in 1 bot more than one case like this

can you help with linq sir?

[mkankatala (Mahesh Kankatala)]

Hi @Rpa12343

→ Use the Read Range workbook activity to read the excel1 and store in a datatable called DT1.
→ Use another Read Range workbook activity to read the excel2 and store in a datatable called DT2.
→ Insert an assign activity and write the below LINQ Expression,

- Assign -> dt_Output = DT1.AsEnumerable.Select(Function(r) DT1.Clone.LoadDataRow({r(0), r(1), r(2), r(3), r(4), r(5), If(DT2.AsEnumerable.Any(Function(r2) r("USER").ToString = r2("ID").ToString), DT2.AsEnumerable.FirstOrDefault(Function(r2) r("USER").ToString = r2("ID").ToString)("AndThis").ToString, ""), If(DT2.AsEnumerable.Any(Function(r2) r("USER").ToString = r2("ID").ToString), DT2.AsEnumerable.FirstOrDefault(Function(r2) r("USER").ToString = r2("ID").ToString)("AndThis2").ToString, "")}, False)).CopyToDataTable()

→ Use the Write Range workbook activity to write the dt_Output to the Excel1.

Hope it helps!!

[ppr (Peter Preuss)]

@Rpa12343

it seems ineffective

maybe on the first look, when the lookup is not modelled in an cost processing saving approach

@Rpa12343

can you help with linq

we encourage to have trained all presented options:
How to Update Data Column Values of a Data Table | Community Blog

And also have understood:

• avoiding black-boxing when using LINQ
• the pay-off of DataTable reconstructions

@Rpa12343

process more than 100k data

have a look here, on how we tuned:

LINQ query running for long hours

Let us introduce the next changes [grafik] we are creating lists with the concatenated strings from dtold, dtnew and creating a dictionary of only the items common in both lists dtNewSet.asEnumerable.Select(Function (x) String.Join(“#”, arrColSet.Select(Function (k) x(k).toString.ToLower.Trim))).Distinct().toList dtOldSet.asEnumerable.Select(Function (x) String.Join(“#”, arrColSet.Select(Function (k) x(k).toString.ToLower.Trim))).Distinct().toList ListDT1.Intersect(ListDT2).ToDictionary(Fun…

Assumptions: no duplicates IDs within dt1,dt2

Similar you can build a lookUp Dictionary, where ID is the key
Assign Activity:
dictLK | Dictionary(Of String, Object() ) =

(From d in dt2.AsEnumerable
Let oa = {"Col1Name","Col2Name","Col3Name"}.Select(Function (cn) d(cn)).toArray
Select t = Tuple.Create(d("ID").toString.Trim, oa)).ToDictionary(Function (t).t.Item1,Function (t).t.Item2)

Now we can loop / parallel loop over dt1

• check if dictLK.Contains(“TheLoopedDt1IFValue”)
• And update the cols

[ppr (Peter Preuss)]

A LINQ Approach with DataTable Reconstruction and ItemArray Rebuild could be based on a Left Join

(From d1 in dt1.AsEnumerable
Group Join d2 in dt2.AsEnumerable
On dt1("ID").toString.Trim Equals dt2("ID").toString.Trim
Into gj = Group
From g in gj.DefaultIfEmpty()
...... rebuild the itemArray 
...... do the DataTable Reconstruction

For the ItemArray rebuild we would have to check, from where dt1, dt2 the value is to take. Therefore we do strong rely to the datastructure