Royi Namir - 1 year ago 144
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 ?

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

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download