Help converting SQL query to LINQ

Question

I am trying to convert some SQL that I have encrypted together into a LINQ query.

Context is a simple quiz app. The quiz has quizrounds, contestants - one to one, attached to quizrounds, rounds have registered answers.

The thing is, I have a one-to-many relationship between the quizround and answer tables. I want to make a rating of all the polls by calculating the number of correct answers and the time spent on answers to all correct answers.

I partially managed to get this behavior with the following SQL query:

select 
quizroundid, SUM(CASE WHEN wascorrect = 1 THEN timeelapsedseconds ELSE 0 END) as [time] ,SUM(CONVERT(int,wascorrect)) as correct
from quizroundanswer
GROUP BY quizroundid
order by correct desc, [time] asc

Now I need to do this, but as part of the Linq query, I need to be able to do only a rating against rounds that answered an equal number or more questions.

So let them say that a round is executed when it has 4 answers. Round x has the answers of the 23rd and 24th, but round y has the answers of the 21th, 22nd, 23rd and 24th. Round x will answer questions 25 and 26, but has not yet done so.

I want to do a round x ranking, but I need to do it against those who played an equal or more number of days (round y), but only against the same number of responses and in the same order. The value of round x has answers from the 23rd and 24th compared to round answers from the 21st and 22nd.

This is linq, which I still have, and it seems to work. I just don't like the look, and I was hoping there was a nicer way to do it.

var rankedRounds = allRounds
  .Where(x => x.Answers.Count >= questionsAnswered)
  .Select(x => new
  {
    x.Id,
    EqualNumberOfAnswers = x.QuizRoundAnswers.OrderBy(y => y.QuizDay.TimeOfDay).Take(daysPlayed)
  })
  .OrderBy(x => x.EqualNumberOfAnswers.Where(z => z.WasCorrect).Sum(y => y.TimeElapsedSeconds))
  .OrderByDescending(x => x.EqualNumberOfAnswers.Count(y => y.WasCorrect))
  .ToList();

I hope someone will read all this and contribute.

Disclaimer: I did not come up with this concept.

Answer 1

, - .

, , , , , .

, , .