我有一个数据表具有这种结构和数据:
ID |研究所|名称 ------------------------ 1 |吉他|约翰· 2 |吉他|乔治 3 |吉他|保罗 4 |鼓|林戈 5 |鼓|皮特
我可以检索的记录是这样的:
的IEnumerable<披头士> ...
一流的披头士
{
INT ID;
串研究所;
字符串名称;
}
我想获得那些谁玩不同乐器的内部秩序。在MSSQL中我会使用
SELECT
*
,ROW_NUMBER()OVER(PARTITION BY研究所ORDER BY ID)为Rn
从甲壳虫
该查询返回
ID |研究所|名称| RN ----------------------------- 1 |吉他|约翰| 1 2 |吉他|乔治| 2 3 |吉他|保罗| 3 4 |鼓|林戈| 1 5 |鼓|皮特| 2
我
如何才能做到这一点在的LINQ
编辑。(接受后回答)
全部工作code:
VAR甲壳虫=(新[] {新{n = 1,研究所=吉他,名称=约翰},
新{n = 2,研究所=吉他,名字=乔治},
新{n = 3,研究所=吉他,名字=保罗},
新{n = 4,研究所=鼓,名字=林檎},
新{n = 5,研究所=鼓,名字=皮特}
});
变种O = beatles.OrderBy(X => x.id).GroupBy(X => x.inst)
。选择(G =>新建{克,算上= g.Count()})
.SelectMany(T => tgSelect(B => B).ZIP(Enumerable.Range(1,t.count),(J,I)=>新建{j.inst,j.name,RN =一世 }));
的foreach(邻变种I)
{
Console.WriteLine({0} {1} {2},i.inst,i.name,i.rn);
}
解决方案
试试这个单行:
变种O =甲壳虫
.OrderBy(X => x.id)
.GroupBy(X => x.inst)
。选择(组=>新建{组=组数= g.Count()})
.SelectMany(groupWithCount =>
groupWithCount.Group.Select(B => B)
。拉链(
Enumerable.Range(1,groupWithCount.Count),
(J,I)=>新{j.inst,j.name,RowNumber = I}
)
);
的foreach(邻变种I)
{
Console.WriteLine({0} {1} {2},i.inst,i.name,i.RowNumber);
}
输出:
吉他约翰·1
吉他乔治·2
吉他保罗3
鼓林戈1
鼓皮特2
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);
}
解决方案
Try this one liner:
var o = beatles
.OrderBy( x => x.id )
.GroupBy( x => x.inst )
.Select( group => new { Group = group, Count = g.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