LINQ for Filtering, Performing Calculations, and Storing To New DataTable

[ashwin.ashok (Ashwin Ashok(Speridian Technologies))]

Hi there,

I recently started exploring the world of LINQ and I have to say, it has positively impacted my work. Tasks that usually take close to an hour to finish executing now takes less than a second to get done, thanks to LINQ query.

But there are times when I get stuck, which is when I rely on this great community of ours for solutions.

Here’s an example of the task I wish to achieve through LINQ:
You have a datatable**(dt_raw)** with columns “ID”, “Name”, “Department” and “Working Hours” and the ID column is not unique, i.e.,

ID Name Department Working Hours
1 Timmy IT 11
1 Timmy Accounts 9
2 Anthony IT 8

And you wish to calculate the total working hours without loading certain headers(Department in this case) for each employee like so:

ID Name Working Hours
1 Timmy 20
2 Anthony 8

I know how to achieve this result, but I wish to do so with a single Assign Activity(with LINQ) and assign the result to a new datatable**(dt_result)**, and was hoping if anyone here could help me with this.

Thanks in advance!

[ppr (Peter)]

@ashwin.ashok
lets do following, with a groupby

prepare an empty datatable (Build datatable) with the datacolum structure of the target report
(ID, NAME, WORKINGHOURS) - dtReport

Use an assign acitvity
left side: dtReport
Right side:

(From d In dtOriginVar.AsEnumerable
Group d By k=d("ID").toString.Trim Into grp=Group
Let s = grp.Sum(Function (x) CInt("0" & x("Working Hours").toString.Trim))
Let ra = New Object(){k, grp.First()("Name"),s}
Select dtReport.Rows.Add(ra)).CopyToDataTable

[ashwin.ashok (Ashwin Ashok(Speridian Technologies))]

Hi @ppr ,
Thank you for your quick response, I was trying to implement your solution when I came across an error:

Any idea why this is happening?

[ppr (Peter)]

@ashwin.ashok
sorry had a small issue, refer to the corrected one above

kindly note: dtOriginVar has to be the name of the variable of the datatable holding your input data

[ashwin.ashok (Ashwin Ashok(Speridian Technologies))]

Hi @ppr ,

Thank you for taking the time to resolve this query of mine!
Also, could you please tell me where you learnt LINQ from? Like do you have any material or links that you don’t mind sharing here?
Once again, thank you, I have marked your answer as the solution.