LINQ to concat strings by comma

Help Something Else
1

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

2

@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

grafik

2 Likes
3

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 ?

4

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

we could look on the Aggregate function

grafik
grafik699×288 73.8 KB

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

1 Like
5

Thank you for suggestions @ppr

6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.