Help converting an sql query into LINQ

Question

Hey,

I'm trying to convert some SQL I've patched together into a LINQ query.

The context is a simple quiz app. A quiz has quizrounds, contestants are one to one attached to quizrounds, rounds have registered answers.

The case is that I have a one to many relationship between tables 'quizround' and 'answer'. I want to do a ranking of all the quizrounds by counting the number of correct answers and the time spent answering all the -correct- answers.

I've partly succeeded in getting this behaviour 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 be able to do this, but as part of the Linq query I need to be able to only do the ranking against rounds that have answered an equal number or more questions.

So, let's say a round is done when it has 4 answers. Round x has answers on the 23rd and the 24th, but round y has answers on the 21st, 22nd, 23rd and 24th. Round x will answer questions on the 25th and 26th, but has not done it yet.

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

This is the linq I have so far, and it seems to work. I just really don't like the look of it and hoped there was a prettier way to accomplish 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 bother to read all this and give input.

Disclaimer: I didn't come up with this concept.

Answer 1

I do not fully understand the logic, but my advice is not to try to get all the answer in one magic query.

One month after it was coded, you will forget how it works and will spend more time if you have to do a small change.

First bring a list of reasonable amount of records to work from the DB, and then in subsequent queries of the same list, refine and complete the result.