Linq查询根据b获得排名 [英] Linq query to get rank based on b

问题描述

用户ID 1022完成2个单位（id：100和101）用户ID 1021完成3个单位（id：100,101,102）如果我通过userid（1022）courseid（109）semid（3000）subjectid（2006）然后结果shoild be average是scoredmarks / totalmarks * 100即77.5，User1022的排名是1，用户1021排名将是b 2





< br $>




在下面的代码中，在返回im获取错误而不是返回语句时，我需要一个查询来达到上述标准



我尝试过：

in database userid 1022 has completed 2 units( id:100 and 101) user id 1021 completed 3 units (id:100,101,102) if i pass userid (1022)courseid(109) semid(3000) subjectid(2006) then result shoild be average is scoredmarks/totalmarks * 100 that is 77.5 and rank of User1022 is 1 and user 1021 rank will b 2





In Below code at near return im getting error instead of that return statement i need a single query to achieve above criteria

What I have tried:

public GetCalculatePerform(int? Student_ID, int? CourseID, int? SemID, int? SubjectID)
        {
            var scoreCard = dbcontext.Stu_Result
            .Where(u => u.CourseID == CourseID && u.SemID == SemID && u.SubjectID == SubjectID);
return scoreCard.GroupBy(u => u.UserID).OrderByDescending(g => g.Average(u => u.ScoredMarks / u.TotalMarks * 100)).Select
                 ((g, i) => new UserRankObject
                 {
                     UserId = (int)g.Key,
                     Rank = i + 1,
                     AverageScore = g.Average(u => u.ScoredMarks / u.TotalMarks * 100)
                 }).Single(u => u.UserId == Student_ID);


        }

RID   UserID  CourseID    SemID   SubjectID   UnitID  ScoredMarks TotalMarks  No_Attempts               CreatedDate ModifiedDate
  1     1021       109     3000        2006      100           30       100             1   2019-02-12 00:00:00.000 NULL
  2     1021       109     3000        2006      101           40       100             1   2019-02-18 00:00:00.000 NULL
  3     1021       109     3000        2006      102           85       100             1   2019-02-19 00:00:00.000 NULL
  4     1022       109     3000        2006      101           80       100             1   2019-02-19 00:00:00.000 NULL
  5     1022       109     3000        2006      100           75       100             1   2019-02-19 00:00:00.000 NULL

推荐答案

尝试这个：

Try this:

var scoreCard = dbcontext.Stu_Result
        .Where(u => u.CourseID == CourseID && u.SemID == SemID && u.SubjectID == SubjectID)
        .GroupBy(u => u.UserID)
        .OrderByDescending(grp=>grp.Average(u => u.ScoredMarks))
        .Select((grp, i) =>new
             {
                 UserId = grp.Key,
                 Rank = i+1,
                 AverageScore = grp.Average(u => u.ScoredMarks)
             })
        .ToList();

对于Entity Framework，试试这个：

For Entity Framework, try this:

var scoreCard = dbcontext.Stu_Result
        .Where(u => u.CourseID == CourseID && u.SemID == SemID && u.SubjectID == SubjectID)
        .ToList() //the magic is here!
        .GroupBy(u => u.UserID)
        .OrderByDescending(grp=>grp.Average(u => u.ScoredMarks))
        .Select((grp, i) =>new
             {
                 UserId = grp.Key,
                 Rank = i+1,
                 AverageScore = grp.Average(u => u.ScoredMarks)
             })
        .ToList();

这篇关于Linq查询根据b获得排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！