Row_number over (Partition by xxx) in Linq?

Question

I have a DataTable which has a structure and data:

id |   inst   |   name ------------------------  1 |  guitar  |  john  2 |  guitar  |  george  3 |  guitar  |  paul  4 |  drums   |  ringo  5 |  drums   |  pete 

I can retrieve the records via:

IEnumerable <Beatle>...  class Beatle {   int id;   string inst;   string name; } 

I'd like to get the internal order of those who play the different instruments. In MSSQL I'd use

SELECT      *     ,Row_Number() OVER (PARTITION BY inst ORDER BY id) AS rn FROM Beatles 

This query returns

id |   inst   |   name  | rn -----------------------------  1 |  guitar  |  john   | 1  2 |  guitar  |  george | 2  3 |  guitar  |  paul   | 3  4 |  drums   |  ringo  | 1  5 |  drums   |  pete   | 2 

Question:
How can I do that in Linq?

Answer 1

Try this one liner:

var o = beatles     .OrderBy( x => x.id )     .GroupBy( x => x.inst )     .Select( group => new { Group = group, Count = group.Count() } )     .SelectMany( groupWithCount =>         groupWithCount.Group.Select( b => b)         .Zip(             Enumerable.Range( 1, groupWithCount.Count ),             ( j, i ) => new { j.inst, j.name, RowNumber = i }         )     );  foreach (var i in o) {     Console.WriteLine( "{0} {1} {2}", i.inst, i.name, i.RowNumber ); } 

Output:

Guitar John 1 Guitar George 2 Guitar Paul 3 drums Ringo 1 drums Pete 2 

Answer 2

B"H

I know this is old. But why isn't the solution simply?

var o = beatles.GroupBy(x => x.inst)                .SelectMany(g =>                    g.Select((j, i) => new { j.inst, j.name, rn = i + 1 })                );