Row_number over (Partition by xxx) in Linq?

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?

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

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 })
               );

Another idea is using a view, if possible.

As @The_Smallest points out, row number is not supported by LINQ. Here's how you can get what you're looking for, though:

var grouped = beatles.OrderBy( x => x.id )
  .ToList()   // required because SelectMany below doesn't evaluate to SQL
  .GroupBy( x => x.inst );
var rns = grouped.ToDictionary( x => x.Key, x => 1 );
var result = grouped
  .SelectMany( x => x.Select( 
    y => new { inst = y.inst, name = y.name, rn = rns[y.inst]++ } ) );