How to combine 4 counts into one with LINQ

Help Studio
, ,
1

My question is: how can I combine all of this into one LINQ expression that returns a datatable with the FileName and four counts. Here is how I’m doing it now:

I have a table with the columns FileName and Success/Fail. The Success/Fail column can have the following values:

  • Success
  • Fail: Business Exception
  • Fail: System Exception

I want a datatable that looks like this:
image

Currently what I’m doing is looping through unique values of FileName and calculating the counts, then adding a data row

image
image552×600 21.7 KB

The For Each expression is:

emailSummaryDT.DefaultView.ToTable(true,"FileName")

Then the expressions to get each count are:

successcount = emailSummaryDT.Select("[FileName] = '" + CurrentRow("FileName").ToString + "' AND [Success/Fail] = 'Success'").Count
businesscount = emailSummaryDT.Select("[FileName] = '" + CurrentRow("FileName").ToString + "' AND [Success/Fail] = 'Fail: Business Exception'").Count
systemcount = emailSummaryDT.Select("[FileName] = '" + CurrentRow("FileName").ToString + "' AND [Success/Fail] = 'Fail: System Exception'").Count
totalcount = emailSummaryDT.Select("[FileName] = '" + CurrentRow("FileName").ToString + "'").Count
2

Maybe the input sample can be shared

In general we would combine it with a GroupBy

Build DataTable - prepare empty report structure - dtResult

Assign Activity:
dtResult =

(From d in emailSummaryDT.AsEnumerable
Group d by k=d("FileName").toString.Trim into grp=Group
Let sc = grp.Where(Function (g1) g1("Success/Fail").toString.Trim.Equals("Success")).Count
Let bc = grp.Where(Function (g2) g2("Success/Fail").toString.Trim.Equals("Fail: Business Exception")).Count
Let ac = grp.Where(Function (g3) g3("Success/Fail").toString.Trim.Equals("Fail: System Exception")).Count
Let tc = grp.Count
Let ra = new Object(){k,sc,bc,ac, tc}
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

Just to start with a raw snippet which we could also make more beautiful

ADDED: total count

1 Like
3

Just to make sure I understand how LINQ works when grouping, the “Group by…into grp=Group” statement essentially loops over the unique values (ie the group), executing the Let statements for each group?

4

Group by looks for the configured group detector Lambda (our case the FileName) for the group member rows and offers it under grp

With the let statement, we can define memorized values when processing / looping over all groups

In other words: after group by… line the group processing / looping will begin (as we have not written others)

Does it help?

For formal / docu:

with the Link to the Query Syntax Clauses:

1 Like
5

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