How to write LINQ query row numbers in C#?

Question

SQL:

Select top percent a,b,ROW_NUMBER() over(PARTITION BY a,b order by a,b) as rowsnumber
from Students as s
group by a,b

LINQ:

var students=from p in Students
group p by new{p.a,p.b} into grp
order by grp.Key.a,grp.Key.b
.Select(s=>s.a,s.b).ToList();

I want to this SQL query convert to LINQ but not convert.

Why do you need it? If you are wanting to partition your results for paging you can use take/skip. Maybe you have another reason though? — Igor, Oct 24 '19 at 19:29
@lgor it doesn't have to be the same as how I can make this query better. — user10181472, Oct 24 '19 at 20:09
Definitely an [XP Problem](https://meta.stackexchange.com/a/66378/171858). That is to say that you believe having a row number (Y) solves some other problem (X) you haven't explained, however it may be possible to Y without solving X. — Erik Philips, Oct 24 '19 at 22:01

score 0 · Answer 1 · answered Oct 24 '19 at 21:20

0

var list = FileList.Select((file, index) => new { Index=index, Filename=file });

Maybe so for example.

answered Oct 24 '19 at 21:20

Jamez Fatout

402
4
13

score 0 · Answer 2 · answered Oct 24 '19 at 21:40

Like the other answer indicates, the Select method has a handy overload with the index parameter. It requires the method LINQ syntax though (and not the query syntax) So, the following query should do the trick. Note that it combines both: query and method syntax as I tried to keep it close to the code in the question above.

    var studentGroups=
    from p in Students
    group p by new { p.a, p.b };

    var students =
    from gr in studentGroups
    from st in Students
               .Where(s => s.a == gr.Key.a && s.b == gr.Key.b)
               .Select((s,i) => new { s.a, s.b, s.c, s.d, rn = i + 1 }) 
    orderby st.a, st.b
    select st;

score 0 · Answer 3 · answered Oct 25 '19 at 05:24

I convert your sql query to lambda.

   var students = context.GroupBy(g => new { g.A, g.B }).Select((s, index) => new {
        s.Key.A,
        s.Key.B,
        rowsnumber = index + 1
    }).Take(10).ToList();

Group by is working like partition by.
Index start from 0, so converting to row number to need add 1.
Take is select TOP 10 rows.

How to write LINQ query row numbers in C#?

3 Answers3