Click here to Skip to main content
15,896,500 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
Ex: if User1[UserID:1021] completes exam on Unitid: 100,101 and 102 AND if user2[userId:1022] completes exam on UnitID 100 and 101.
If im checking rank and avg score of User2 then it should comapre USER1'S completed units that are 100 and 101 not with 102.

What I have tried:

database:

SQL
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






Query:
C#
var scoreCard = dbcontext.Stu_Result
        .Where(u => u.CourseID == CourseID && u.SemID == SemID && u.SubjectID == SubjectID)
        .ToList() 
        .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();
Posted
Comments
Maciej Los 11-Mar-19 17:30pm    
This question is worth of 5!
I have not enough time to keep my attention on it, but this is NOT an easy subject. This should aim to use very similar solution as Enumerable.Intersect method.
I'm gonna give a 5 stars for every one who will find elegant SQL or Linq EF solution.
Note: above linq query is mine, but the description of issue wasn't so clear as it is now.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900