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屋!