How to Add Rownum to GroupBy Linq

Question

I have a complex LINQ Query to extract Top students in my university. Here is the query :

            var query = Db.Students.AsNoTracking().Where(...).AsQueryable();
            var resultgroup = query.GroupBy(st => new
                {
                    st.Student.CourseStudyId,
                    st.Student.EntranceTermId,
                    st.Student.StudyingModeId,
                    st.Student.StudyLevelId
                }, (key, g) => new
                {
                    CourseStudyId = key.CourseStudyId,
                    EntranceTermId = key.EntranceTermId,
                    StudyingModeId = key.StudyingModeId,
                    StudyLevelId = key.StudyLevelId,
                    list = g.OrderByDescending(x => 
                    x.StudentTermSummary.TotalAverageTillTerm).Take(topStudentNumber)
                }).SelectMany(q => q.list).AsQueryable();

This Query give me top n students based on 4 parameters and on their TotalAverageTillTerm.

Now I want to add rownum for each group to simulate Total rank, for example Output is :

Now I want to Add TotalRank as rownumber like Sql. In the picture X1=1,X2=2,X3=3 and Y1=1,Y2=2,Y3=3

If I want to reduce problem. I only work on one group. Code Like this :

                resultgroup = query.GroupBy(st => new
                {
                    st.Student.StudyLevelId
                }, st => st, (key, g) => new
                {
                    StudyLevelId = key.StudyLevelId,
                    list = g.OrderByDescending(x => 
                    x.StudentTermSummary.TotalAverageTillTerm)
                     .Take(topStudentNumber)
                }).SelectMany(q => q.list).AsQueryable();

isn't total rank the index of people in list +1 : `list = g.OrderByDescending(x => x.StudentTermSummary.TotalAverageTillTerm).Take(topStudentNumber) .Select((x,i) => new { prop1 = x.Prop1, rank = i+1 } )` — Drag and Drop, Aug 14 '20 at 12:03
An [mre] may have help. Especially the minimal part. If you reduce the problem to only one grouped group of student the probleme would be reduce to "add a row number in linq", right? If it's the case then this will make a good [dupe target](https://stackoverflow.com/questions/14960563/linq-add-rownumber-column) — Drag and Drop, Aug 14 '20 at 12:09

AnGG · Answer 1 · 2020-08-14T12:49:05.000

1

Do you mean :

   var query = Db.Students.AsNoTracking().Where(...).AsQueryable();
   var resultgroup = query.GroupBy(st => new
                {
                    st.Student.CourseStudyId,
                    st.Student.EntranceTermId,
                    st.Student.StudyingModeId,
                    st.Student.StudyLevelId
                }, (key, g) => new
                    {
                        CourseStudyId = key.CourseStudyId,
                        EntranceTermId = key.EntranceTermId,
                        StudyingModeId = key.StudyingModeId,
                        StudyLevelId = key.StudyLevelId,
                        list = g.OrderByDescending(x => 
                        x.StudentTermSummary.TotalAverageTillTerm)
                        .Take(topStudentNumber)
                        .Select((x, i) => new { Item = x, TotalRank = i /* item number inside group */}),
                        StudentsInGroupCount = g.Count() // count group this items
                    }).SelectMany(q => q).AsQueryable();

To see the results :

foreach (var item in resultgroup.ToList())
{
    item.list.ForEach(s => Console.WriteLine(s.TotalRank));
}

edited Aug 14 '20 at 12:49

answered Aug 14 '20 at 12:04

AnGG

679
3
9

If I Add total rank Where Can I use it? how can I use it in SelectMany? – mahdi moghimi Aug 14 '20 at 12:06
try this : .SelectMany(q => q).AsQueryable(); so you have all the info that you want – AnGG Aug 14 '20 at 12:13
I do but it only give me rank of each group. for example student1,2,3 have rank 1, student 4,5,6 = rank2 ... – mahdi moghimi Aug 14 '20 at 12:16
Select index list = g.OrderByDescending(x => x.StudentTermSummary.TotalAverageTillTerm).Take(topStudentNumber).Select((x, i) => new { item, i }) – AnGG Aug 14 '20 at 12:24
it give me error : the type argument for method .... cannot be inferred from the usage. try specifying the type arguments expelicitly – mahdi moghimi Aug 14 '20 at 12:30
hey. list give me for example to 3 or top n student for each group. so you must work on it not outside of it. I want to create rownum inside of each group. your solution give me rownum outside of each group – mahdi moghimi Aug 14 '20 at 12:32
Yes that on is from the GroupBy overload you choose. Instead of the simple with only the grouping key you took the one with key+ projection. If you drop that part into a proper select it will work. – Drag and Drop Aug 14 '20 at 12:33
its not work for this solution. your answer give me row num foreach group. I dont want it. I want to add rownum inside of each group. that means if i have one group with 3 students, it give me 1,2,3. but your answer give me only 1 for this group if i use foreach (var item in resultgroup){ print item.List , Item.rank } you know that it is wrong – mahdi moghimi Aug 14 '20 at 12:41
Thanks but in console.writeline I want to show rowNum. could you please change your code to write each group and their rownumber inside of each group – mahdi moghimi Aug 14 '20 at 12:49
Check current one – AnGG Aug 14 '20 at 12:52
I try your code, it raise me error : LINQ to Entities does not recognize the method 'System.Collections.Generic.IEnumerable`1[<>f__AnonymousType247`5[System.Guid,System.Guid,System.Guid,System.Guid,System.Int32]] Select[StudentTerm,<>f__AnonymousType247`5](System.Collections.Generic.IEnumerable`1[Hamava.Model.Education.StudentTerm], System.Func`3[Hamava.Model.Education.StudentTerm,System.Int32,<>f__AnonymousType247`5[System.Guid,System.Guid,System.Guid,System.Guid,System.Int32]])' method, and this method cannot be translated into a store expression. – mahdi moghimi Aug 14 '20 at 13:14
Are you using .Net Core 3.1 ? If so you can do the query locally but it may have effect on performance To do this change this to : var query = await Db.Students.AsNoTracking().Where(...).ToListAsync(); And chanege : foreach (var item in resultgroup) – AnGG Aug 14 '20 at 13:19
I use .net not core – mahdi moghimi Aug 14 '20 at 13:26
Try to get the data from db ( ToList or ToListAsync ) and the do the groupby query on the list – AnGG Aug 14 '20 at 13:30

Drag and Drop · Accepted Answer · 2020-08-14T13:30:07.993

1

list was a List of student but I see no sign of student having a rank property so I wrapped it into a annonimous type with rank.

var query = Db.Students.AsNoTracking().Where(...).AsEnumerable();
var resultgroup = query.GroupBy(st => new   {
        st.Student.CourseStudyId,
        st.Student.EntranceTermId,
        st.Student.StudyingModeId,
        st.Student.StudyLevelId
    })
    .SelectMany( g =>       
        g.OrderByDescending(x =>x.StudentTermSummary.TotalAverageTillTerm)
                .Take(topStudentNumber)
                .Select((x,i) => new { 
                    CourseStudyId = g.Key.CourseStudyId,
                    EntranceTermId = g.Key.EntranceTermId,
                    StudyingModeId = g.Key.StudyingModeId,
                    StudyLevelId = g.Key.StudyLevelId,
                    Rank = i+1                  
                    //studentPorperty = x.Prop1, 
                })
    )
    .AsQueryable();

edited Aug 14 '20 at 13:30

answered Aug 14 '20 at 12:28

Drag and Drop

2,672
3
25
37

If that work we can all admit that the question is a dupe: of https://stackoverflow.com/questions/14960563/linq-add-rownumber-column. And we simply fail to isolate the issue first hand. – Drag and Drop Aug 14 '20 at 12:30
Hey your answer give me row num foreach group. I dont want it. I want to add rownum inside of each group. that means if i have one group with 3 students, it give me 1,2,3. but your answer give me only 1 for this group – mahdi moghimi Aug 14 '20 at 12:38
if i use foreach (var item in resultgroup){ print item.List , Item.rank } you know that it is wrong – mahdi moghimi Aug 14 '20 at 12:39
Item as a sub list was your design choice btw. So there is no `item.List , Item.rank`, use auto competion or debug to explore the item if you are lost. I have flattern the Select for something I think more easy. Use the edit history if it doesn't suit. Btw. Im out. Please refere to [mre] and [ask]. It's been working on my side since 30min. Not my job . – Drag and Drop Aug 14 '20 at 12:52
Oh you change your code. ill try and send you feedback – mahdi moghimi Aug 14 '20 at 12:55
I try your code but it is not work. give me error : LINQ to Entities does not recognize the method 'System.Collections.Generic.IEnumerable`1[<>f__AnonymousType247`5[System.Guid,System.Guid,System.Guid,System.Guid,System.Int32]] Select[StudentTerm,<>f__AnonymousType247`5](System.Collections.Generic.IEnumerable`1[Hamava.Model.Education.StudentTerm], System.Func`3[Hamava.Model.Education.StudentTerm,System.Int32,<>f__AnonymousType247`5[System.Guid,System.Guid,System.Guid,System.Guid,System.Int32]])' method, and this method cannot be translated into a store expression. – mahdi moghimi Aug 14 '20 at 13:12
1

@mahdimoghimi, see that `AsQueryable` at the top swap it to `.AsEnumerable()`. Don't know what AsQueryable does there but enumerable to load the student and do the group by in memeory. no need to translate projecttion code to SQL. – Drag and Drop Aug 14 '20 at 13:26

How to Add Rownum to GroupBy Linq

2 Answers2