GroupBy with linq to DataTable

Question

I have the datatable below:

DefectTypeId   EntityId      DefectId     Remark
------------------------------------------------
1              29000         100          Defect
2              29000         100          Defect
3              29000         200          Ok
1              30000         100          Defect
2              30000         150          
9              31000         100          Defect
10             31000         100          Defect 
12             31000         200          Ok

How to get this table or list with linq?

EntityId   Remark
------------------------
29000      Defect, Ok
30000      Defect
31000      Defect, Ok

Thanks in advance

EDIT

I tried this but it doesn't work (Rows are not grouped):

Dim query = dt.Select("Remark IS NOT NULL").AsEnumerable().GroupBy(
            Function(r) New With {
                .EntityId = r.Field(Of Integer)("EntityId"),
                .DefectId = r.Field(Of Integer)("DefectId"),
                .Remark = r.Field(Of String)("Remark")
            }).[Select](
            Function(g) New With {
                g.Key.EntityId,
                .Remark = String.Join(",", g.Select(Function(i) i("Remark")))
            })

Then I tried this:

Dim query = (From row In dt.Select("Remark IS NOT NULL")
             Group By EntityId = row.Field(Of Integer)("EntityId"),
                 DefectId = row.Field(Of Integer)("DefectId"),
                 Remark = row.Field(Of String)("Remark") Into g = Group) _
            .Select(Function(i) New With {
                i.EntityId,
                .Remark = String.Join(",", i.Remark)
            })

It is better but it is not yet the expected result. Here is the result:

EntityId   Remark
------------------------
29000      Defect
29000      Ok
30000      Defect
31000      Defect
31000      Ok

I could get what I want using a foreach after but would like to know if it's possible to get the goal (one row per Entity) in a single Linq instruction. Thank you.

you want a solution with linq only ? i was thinking of posting a solution outside linq — Software Dev, Apr 10 '18 at 15:01
What have you tried? Where are you having trouble? Do you not know how to use LINQ on a datatable? Do you not know how to do grouping in linq? Do you not know how to aggregate your data to create the Remark field? Something else? — Chris, Apr 10 '18 at 15:06
https://stackoverflow.com/questions/18203169/field-concatenation-based-on-group-in-linq — Ctznkane525, Apr 10 '18 at 15:37
Possible duplicate of [Field concatenation based on group in LINQ](https://stackoverflow.com/questions/18203169/field-concatenation-based-on-group-in-linq) — NetMage, Apr 10 '18 at 18:36
It is not duplicate - lambda functions c# syntax is different from vb especially for beginner. — IvanH, Apr 10 '18 at 20:34
Without Linq is also a possible solution. I think with linq it would be more concise. — Sébastien, Apr 11 '18 at 07:55
@IvanH actually lambda syntax is much more similar to VB than query comprehension syntax, which is significantly different in VB. — NetMage, Apr 11 '18 at 22:17

NetMage · Answer 1 · 2018-04-11T22:39:53.890

Starting with your answer and working backwards, you want to group by EntityId and join the distinct Remarks together. Using your existing lambda query as a basis, just simplify it (though I would do the "Remark IS NOT NULL" in LINQ normally):

Dim Ans = dt.Select("Remark IS NOT NULL") _
            .GroupBy(Function(r) r.Field(Of Integer)("EntityId"), Function(r) r.Field(Of String)("Remark")) _
            .Select(Function(rg) New With {
                .EntityId = rg.Key,
                .Remark = String.Join(", ", rg.Distinct())
            })

Using some of the advantages of VB Query Comprehension, you could also do:

Dim Ans2 = From r In dt.Select("Remark IS NOT NULL")
           Group r.Field(Of String)("Remark") By Key = r.Field(Of Integer)("EntityId") Into Group
           Select New With {.EntityId = Key, .Remark = String.Join(", ", Group.Distinct())}

Thank you. I did it without linq. But I'll try your code next week. — Sébastien, Apr 13 '18 at 14:09

GroupBy with linq to DataTable

1 Answers1