Using Linq to group, order and concatenate strings

Question

Suppose I have the following data

Key    ID      Data
A      1       Hello
A      2       World
B      2       Bar
B      1       Foo

I am looking to produce the result

A     HelloWorld
B     FooBar

I am struggling to get the syntax quite right - I was trying to use Aggregate, but I wasn't sure if I could (or should) use SelectMany

I'd be grateful of any help.

    Dim data = result.Rows.
                    GroupBy(Function(r) r.Key).
                    Select(Function(g) g.OrderBy(Function(s) s.ID)).
                    Aggregate(New StringBuilder, Function(cur, nxt)                   
                              cur.Append(nxt.First.Data))

Thanks

Simon

Am I correct in understanding that you want the words of the same key joined together in the order of their IDs? — Matt Ellen, Jun 14 '11 at 14:23
@Matt - yes, @Magnus - I don't mind, @Pr0fess0rX - I've included the code, I'll update it to C# in a min — Simon Woods, Jun 14 '11 at 14:28

StriplingWarrior · Accepted Answer · 2011-06-14T16:02:15.037

13

I think this (C#) should work:

var data = from r in result.Rows
           group r by r.Item("Key").ToString() into g
           select new {
               g.Key, 
               Joined = string.Join("", g.OrderBy(s => s.Item("ID"))
                                         .Select(s => s.Item("Data")))
           };

edited Jun 14 '11 at 16:02

answered Jun 14 '11 at 14:35

StriplingWarrior

151,543
27
246
315

I am working with this (and/or @Bala R)s, but it makes no reference to the Data column. What is string.Join acting on or is it done implicitly in some way? – Simon Woods Jun 14 '11 at 15:57
@Simon Woods: Sorry about that. I edited my answer to include the Data column. – StriplingWarrior Jun 14 '11 at 16:02
Brilliant ... thx. This may not be the place but, given its complexity (imv) is there any benefit in this over, say, writing it procedurally and then wrapping it up in a function? – Simon Woods Jun 14 '11 at 16:18
Hi is there a VB.NET equivalent for this? – Unomono Jun 05 '12 at 03:17
1

@Unomono: I'm sure there is. I don't know VB.NET all that well, but you can see if this works for you: http://www.developerfusion.com/tools/convert/csharp-to-vb/ – StriplingWarrior Jun 05 '12 at 15:46
1

@SimonWoods: Sorry, for some reason I didn't notice your other comment until now. There's nothing to prevent you from wrapping this query itself, or various parts of it, in methods, in order to break it down into simpler chunks. If you try writing this procedurally, though, you'll find that the code is far more complex: you'll be creating and populating dictionaries and lists, running through `for` loops and `if` statements all over the place. This is the sort of use case where LINQ really shines. – StriplingWarrior Mar 11 '14 at 17:14

score 2 · Answer 2 · answered Jun 14 '11 at 14:38

2

Dim result = result.Rows.GroupBy(Function(r) r.Key).Select(Function(g) New With { _
    g.Key, _
    String.Join("", g.OrderBy(Function(r) r.ID)) _
})

answered Jun 14 '11 at 14:38

Bala R

107,317
23
199
210

ah, i just noticed the other answer with it pretty much the same thing. I'll leave mine out because it's vb.net and it's using the method syntax (as in the question). – Bala R Jun 14 '11 at 14:42
u saved me!!!!! – elle0087 Dec 16 '21 at 15:08

Enrico Campidoglio · Answer 3 · 2011-06-14T15:20:29.887

Here's an alternative implementation:

var source = new Item[]
{
    new Item { Key = "A", ID = 1, Data = "Hello" },
    new Item { Key = "A", ID = 2, Data = "World" },
    new Item { Key = "B", ID = 2, Data = "Bar" },
    new Item { Key = "B", ID = 1, Data = "Foo" }
};

var results = source
    .GroupBy(item => item.Key)
    .Select(group => group
        .OrderBy(item => item.ID)
        .Aggregate(new Item(), (result, item) =>
            {
                result.Key = item.Key;
                result.Data += item.Data;
                return result;
            }));

score 1 · Answer 4 · answered Jun 14 '11 at 15:06

You don't want to Aggregate the groups. You want to aggregage the elements of each group unto itself.

If you want the query to do it, then

Dim data = result.Rows
   .GroupBy( )
   .Select(Function(g) g
     .OrderBy( )
     .Aggregate( )
   )

If that anonymous function starts getting too hairy to write, just make a method that accepts an IGrouping<int, Row> and turns it into what you want. Then call it like:

Dim data = result.Rows
   .GroupBy( )
   .Select( myMethod )

Using Linq to group, order and concatenate strings

4 Answers4

Linked