LINQ to concat strings by comma

[Surya_Narayana_Korivipadu]

Hi,

I’ve an input data table called “ModifiedJobsDT” as follows:

ModifiedJobsDT
UiPathProcessName	FullProcessName	Environment Name	RPA Runtime (INPUT)	Runs/ Sessions	ProcessAndEnvironmentName
Folder1_Ext-Process1	Folder1_Ext-Process1-Dispatcher	Env1	0:07:56	7	Folder1_Ext-Process1_Env1
Folder1_Ext-Process1	Folder1_Ext-Process1-Performer	Env1	0:00:04	5	Folder1_Ext-Process1_Env1
Folder1-Process1677	Folder1-Process1677	Env2	0:21:54	9	Folder1-Process1677_Env2
BOE-DeleteOldMails	BOE-DeleteOldMails	Env3	0:00:21	1	BOE-DeleteOldMails_Env3
Process5	Process5	Env4	0:02:35	7	Process5_Env4
Folder1_Ext-Process1	Folder1_Ext-Process1-Performer	Env5	0:02:35	7	Folder1_Ext-Process1_Env5

I need to group data based on last column of above DT and prepare an output DT called “FinalJobsDT” as follows:

FinalJobsDT
UiPathProcessName	FullProcessName	Environment Name	RPA Runtime (INPUT)	Runs/ Sessions	ProcessAndEnvironmentName
Folder1_Ext-Process1	Folder1_Ext-Process1-Dispatcher, Folder1_Ext-Process1-Performer	Env1	00:08:00	12	Folder1_Ext-Process1_Env1
Folder1-Process1677	Folder1-Process1677	Env2	00:21:54	9	Folder1-Process1677_Env2
BOE-DeleteOldMails	BOE-DeleteOldMails	Env3	00:00:21	1	BOE-DeleteOldMails_Env3
Process5	Process5	Env4	00:02:35	7	Process5_Env4
Folder1_Ext-Process1	Folder1_Ext-Process1-Performer	Env5	00:02:35	7	Folder1_Ext-Process1_Env5

I prepared a LINQ as follows:
FinalJobsDT = (From row In ModifiedJobsDT
Group row By pen = row(“ProcessAndEnvironmentName”).ToString
Into grp = Group
Let fpn = String.Join(“,”,(From name In grp Select Convert.ToString(grp(“FullProcessName”))).ToList)
Let runtime = grp.Sum(Function (x) Timespan.Parse(x(“RPA Runtime (INPUT)”))).ToString
Let runs = grp.Sum(Function (x) Convert.ToInt32(x(“Runs/ Sessions”))).ToString
Let result = New Object() {grp(0)(“UiPathProcessName”),fpn,grp(0)(“Environment Name”),runtime,runs,grp(0)(“ProcessAndEnvironmentName”)}
Select FinalJobsDT.Rows.Add(result)).CopyToDataTable

Can you please check and tell if I wrote the correct LINQ? If not, can you please guide me to prepare correct LINQ?

Also I’m getting compilation error when I’m using this LINQ in assign activity:
“Option strict on disallows implict conversions from String to Integer”

I made all columns as String in ModifiedJobsDT and then FinalJobsDT = ModifiedJobsDT.clone and then I’m using this LINQ

@ppr @kumar.varun2 @ermanoj3101 @Shubham_Varshney

[ppr (Peter Preuss)]

@Surya_Narayana_Korivipadu
give a try on

FinalJobsDT = ModifiedJobsDT.Clone
FinalJobsDT =

(From row In ModifiedJobsDT.AsEnumerable
Group row By pen = row(“ProcessAndEnvironmentName”).ToString Into grp = Group
Let fpn = String.Join(",", grp.Select(Function (x) x("FullProcessName").toString))
Let rtt = grp.Sum(Function (x) Timespan.Parse(x("RPA Runtime (INPUT)").toString).Ticks)
Let rts = Timespan.FromTicks(rtt)
Let runs = grp.Sum(Function (x) Convert.ToInt32(x("Runs/ Sessions").toString))
Let uip = grp(0)("UiPathProcessName")
Let env = grp(0)("Environment Name")
Let result = New Object() {uip,fpn,env,rts,runs,pen}
Select FinalJobsDT.Rows.Add(result)).CopyToDataTable

[Surya_Narayana_Korivipadu]

Thank you @ppr

I just added extra .ToString methods to all columns, .ToList to String.Join method and final LINQ is like:
(From row In ModifiedJobsDT.AsEnumerable
Group row By pen = row(“ProcessAndEnvironmentName”).ToString Into grp = Group
Let fpn = String.Join(“,”,grp.Select(Function (x) x(“FullProcessName”).ToString).ToList)
Let rtt = grp.Sum(Function (x) Timespan.Parse(x(“RPA Runtime (INPUT)”).ToString).Ticks)
Let rts = TimeSpan.FromTicks(rtt).ToString
Let runs = grp.Sum(Function (x) Convert.ToInt32(x(“Runs/ Sessions”).ToString)).ToString
Let uip = grp(0)(“UiPathProcessName”).ToString
Let env = grp(0)(“Environment Name”).ToString
Let result = New Object() {uip,fpn,env,rts,runs,pen}
Select FinalJobsDT.Rows.Add(result)).CopyToDataTable

I understood LINQ is not adding Timespan directly. Can you please tell me what mistakes I did apart from Timespan.Parse ?

[ppr (Peter Preuss)]

@Surya_Narayana_Korivipadu

Can you please tell me what mistakes I did apart from Timespan.Parse ?

it was very well started. For your next LINQs keep catch on:

• Use AsEnumerable when working with Datatables
• try to have the code as short as possible
  Let runs = grp.Sum(Function (x) Convert.ToInt32(x(“Runs/ Sessions”))).ToString
  vs.

Let runs = grp.Sum(Function (x) Convert.ToInt32(x("Runs/ Sessions").toString))

@Surya_Narayana_Korivipadu

I understood LINQ is not adding Timespan directly.

we could look on the Aggregate function

From the past experience we encountered that sum method can be rated as more reliable in a lot scenarios compared to Aggregate. So it was just mentionedfor learning purpose

[Surya_Narayana_Korivipadu]

Thank you for suggestions @ppr