Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 704
  • Last Modified:

LINQ Grouping Issue

I have a table in the following structure:

TestSubmissionID
TestUserID
Score
TotalTime
DateSubmitted

I am trying to group all users who have taken the test and only get the highest score for each. This table contains the score of all attempts a user has taken to complete the test.

Here is my LINQ:

var scores = (from qs in dbContext.TestSubmissions
                              group qs by qs.TestUserID
                                  into g
                                  select new
                                  {
                                      TestUserID= (from u in g select u.TestUserID).FirstOrDefault(),
                                      Score = (from s in g select s.Score).Max().Value,
                                      DateSubmitted= (from s in g select s.DateSubmitted).FirstOrDefault()
                                  }).OrderByDescending(o => o.Score);

Open in new window


The grouping is working correctly. But I'm finding that the "DateSubmitted" column is not showing the correct value for the user record. It seems to be getting a random value from the list of data. All other data is correct.

What could be causing this to happen?
0
R1ND3R
Asked:
R1ND3R
1 Solution
 
käµfm³d 👽Commented:
What is the primary key of the table?
0
 
Fernando SotoRetiredCommented:
Hi R1ND3R;

Seeming you are grouping the data more then one row may be in each group. If so then you may be selecting the wrong date. The following will sort the groups by date order and then select the most recent date.

var scores = (from qs in dbContext.TestSubmissions
              group qs by qs.TestUserID into g
              select new
              {
                  TestUserID= (from u in g select u.TestUserID).FirstOrDefault(),
                  Score = (from s in g select s.Score).Max().Value,
                  DateSubmitted= (from s in g order s by s.DateSubmitted select s.DateSubmitted).FirstOrDefault()
              }).OrderByDescending(o => o.Score);

Open in new window

0
 
R1ND3RAuthor Commented:
FernandoSoto, the LINQ you have provided is failing on this line due to the way the query is structured:

DateSubmitted= (from s in g order s by s.QuizSubmitted select s.DateSubmitted).FirstOrDefault()

Open in new window


kaufmed, the primary key will be TestSubmissionID.
0
 
Jean Marie GeeraertsApplication EngineerCommented:
Here's my suggestion.

var bestScores = (from score in dbContext.TestSubmissions
                              group score by score.TestUserID
                                  into scoresPerUser
                                  select new
                                             {
                                                 TestUserID = Key,
                                                 Score = scoresPerUser.Max(s => s.Score),
                                                 DateSubmitted = scoresPerUser.Where(us => us.Score == scoresPerUser.Max(s => s.Score)).Min(d => d.DateSubmitted)
                                             }).OrderByDescending(o => o.Score);

Open in new window


Basically what this linq query does:
1. Group the TestSubmissions by TestUserID
2. Per grouped entity, return they key (=TestUserID), Score as maximum of the grouped result, DateSubmitted as the minimum of the grouped result
3. Order on score descending

So the end result will give you per TestUserID the highest score with the first date on which the TestUser scored this result and sort it in descending order on score.
0
 
Fernando SotoRetiredCommented:
Hi R1ND3R;

Looking at the question again I believe that this should give you what you are looking for.

var scores = (from qs in dbContext.TestSubmissions
              group qs by qs.TestUserID into g
              let selValues = (from s in g orderby s.Score descending select s).First()
              select new
              {
                  TestUserID= g.Key,
                  Score = selValues.Score,
                  DateSubmitted = selValues.DateSubmitted, 
              }).OrderByDescending(o => o.Score);

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now