Random Orderby on subquery in LINQ

Question

I have three entity framework objects, Quiz, Question, and Option. A Quiz has a collection of Question objects, and a Question has a collection of Option objects. I would like to return from the DBContext a randomized list of questions for a specified quiz and each question should include a randomly sorted collection of associated Options.

So far, I have been able to get the random list of questions out successfully, but I am having trouble randomizing the options for the question.

Note: I have a couple of different shuffling extension methods I have written, in this example I am using ordering by a Guid for sake of simplicity.

var questions = db.Questions.Where(q => q.QuizId == quizId).Include(q => q.Options).OrderBy(a => Guid.NewGuid());

How can I randomly shuffle the Options?

I have a couple of different shuffle extensions I have written, but they have proven to be too slow. That is another topic, but I will update the question to reflect that the method of shuffling is not important — Crake, Jan 18 '13 at 21:11
Shuffling the structures in memory, once they get to the client, is easy enough. It may or may not be feasible to shuffle them on the DB end through the ORM. — Servy, Jan 18 '13 at 21:14
@Servy Agreed, but I am not the client in this case, nor am I writing the code on the client side. — Crake, Jan 18 '13 at 21:15
"Client", in this context, is wherever your C# code is running, as in, "not on the database". So yes, you are the client. — Servy, Jan 18 '13 at 21:24
Ah ok, well in that case I am open to an answer that still provides a List as the output, as that is the contract. I guess I was hoping to avoid a foreach, but I am not certain that would be any faster/slower than having the DB do it. — Crake, Jan 18 '13 at 21:38

score 0 · Answer 1 · edited May 23 '17 at 10:34

I'm not sure LINQ-to-SQL will support conversion of such a specific feature. In your boots, if I had access to the database, I'd create a stored procedure like described here to fetch random rows on the database level. Though, this is also a not-optimal solution (see accepted answer to this question)

You may go for the following approach (assuming you have less than MAXINT questions):

Random random;
var c = Questions.Count();

var randomNumbers = new List<int>();
var fetchedQuestions = new List<Question>();
var randomNumber = random.Next(1,c);

for (int i = 0; i < maxQuestions; i++) {
    while (randomNumbers.Contains(randomNumber))
        randomNumber = random.Next(1,c);
    randomNumbers.Add(randomNumber);
    fetchedQuestions.Add(Questions.ElementAt(randomNumber));
}
return fetchedQuestions;

I.e. just generate some random unique question row numbers and fetch corresponding rows.

WARNING! Optimization needed before using - this is a dirty code prototype.

Random Orderby on subquery in LINQ

1 Answers1