I have a table in the following structure:
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
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);
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?