Click here to Skip to main content
14,034,935 members
Rate this:
 
Please Sign up or sign in to 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:

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:
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web06 | 2.8.190424.1 | Last Updated 11 Mar 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100