C# - LINQ - Row_Number over (Partition by Column) in LINQ

 

ID	INST	NAME
1	Guitar	John
2	Guitar	George
3	Guitar	Paul
4	Drums	Ringo
5	Drums	Pete
ID	INST	NAME	RN
1	Guitar	John	1
2	Guitar	George	2
3	Guitar	Paul	3
4	Drums	Ringo	1
5	Drums	Pete	2

 SQL 

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

FROM Beatles

 LINQ

class Beatle{

    int id;

    string inst;

    string name;

}

 

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

}