Royi Namir Royi Namir - 2 months ago 22
C# Question

Row_number over (Partition by xxx) in Linq?

I have a DataTable which has this 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 like this:

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


How can I do that in Linq ?

Edit.(after accepted answer)

Full working code :

var beatles = (new[] { new { id=1 , inst = "guitar" , name="john" },
new { id=2 , inst = "guitar" , name="george" },
new { id=3 , inst = "guitar" , name="paul" },
new { id=4 , inst = "drums" , name="ringo" },
new { id=5 , inst = "drums" , name="pete" }
});

var o = beatles.OrderBy(x => x.id).GroupBy(x => x.inst)
.Select(g => new { g, count = g.Count() })
.SelectMany(t => t.g.Select(b => b).Zip(Enumerable.Range(1, t.count), (j, i) => new { j.inst, j.name, rn = i }));

foreach (var i in o)
{
Console.WriteLine("{0} {1} {2}", i.inst, i.name, i.rn);
}

Answer

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
Comments