How to genereate random numbers from a range when button is used but the numbers cannot repeate

Question

I am creating a quiz and im using a random number from a range of 1 - 20 numbers ( Primary Keys)

Random r = new Random();
int rInt = r.Next(1, 9);

The numbers(primary keys) and then used for a query to select 5 random number but the problem is that I am getting repeated questions because the numbers repeat

 string SQL = "SELECT QuestionText,CorrectAnswer,WrongAnswer1,WrongAnswer2,WrongAnswer3 FROM Question Where QuestionID = " + rInt;

I have tried some methods to fix it but its not working and running out of ideas , anyone have any suggestions?

Answer 1

Just ask the database for it:

string SQL = @"
SELECT TOP 5 QuestionText,CorrectAnswer,WrongAnswer1,WrongAnswer2,WrongAnswer3 
FROM Question 
ORDER BY NewID()";

If/when you outgrow this, there exists a more optimized solution as well:

string SQL = @"
WITH cte AS 
(
    SELECT TOP 5 QuestionId FROM Questions ORDER BY NEWID()
)
SELECT QuestionText,CorrectAnswer,WrongAnswer1,WrongAnswer2,WrongAnswer3
FROM cte c
JOIN Questions q 
  ON q.QuestionId = c.QuestionId 
";

The second query will perform much better (assuming QuestionId is your primary key) because it will only have to read the primary index (which will likely already be in memory), generate the Guids, pick the top 5 using the most efficient method, then look up those 5 records using the primary key.

The first query should work just fine for smaller number of questions, but I believe it may cause a table scan, and some pressure on tempdb, so if your questions are varchar(max) and get very long, or you have tens of thousands of questions with a very small tempdb with some versions of Sql Server, it may not perform great.

Answer 2

Something like this might do the trick for you:

[ThreadStatic]
private static Random __random = null;

public int[] Get5RandomQuestions()
{
    __random = __random ?? new Random(Guid.NewGuid().GetHashCode()); // approx one in 850 chance of seed collision
    using (var context = new MyDBContext())
    {
        var questions = context.Questions.Select(x => x.Question_ID).ToArray();
        return questions.OrderBy(_ => __random.Next()).Take(5).ToArray();
    }
}

Answer 3

Another, server side approach:

private static Random _r = new Random();

...

var seed = _r.NextDouble();

using var context = new SomeContext();

var questions = context.Questions
    .OrderBy(p => SqlFunctions.Checksum(p.Id * seed))
    .Take(5);

_{Note : Checksum is not bullet proof, limitations apply. This approach should not be used to generate quiz questions in life or death situations.}

---

As per request:

SqlFunctions.Checksum will essentially generate a hash and order by it

 CHECKSUM([Id] * <seed>) AS [C1], 
 ...
 ORDER BY [C1] ASC

CHECKSUM (Transact-SQL)

The CHECKSUM function returns the checksum value computed over a table row, or over an expression list. Use CHECKSUM to build hash indexes.

...

CHECKSUM computes a hash value, called the checksum, over its argument list. Use this hash value to build hash indexes. A hash index will result if the CHECKSUM function has column arguments, and an index is built over the computed CHECKSUM value. This can be used for equality searches over the columns.

Note, as mentioned before the Checksum is not bullet proof it returns an int (take it for what it is), however, the chances of a collision or duplicate is extremely small for smaller data sets when using it in this way with unique Id, it's also fairly performant.

So running this only a production database with 10 million records many times, there was no collisions.

In regards to speed, it can get the top 5 in 75ms, however it is slower when generated by EF

The cte solution tendered for NewId, is about 125 ms.

Answer 4

Try selecting all the questions from the db. Say you have them in a collection 'Question', you could then try Questions.OrderBy(y => Guid.NewGuid()).ToList()

Answer 5

The Linq .Distinct() method is too nice to not use here

The easiest way I know of doing this would be like below, using a method to create an infinite stream of random numbers, which can then be nicely wrangled with Linq:

using System.Linq;

IEnumerable<int> GenRandomNumbers()
{
    var random = new Random();

    while (true)
    {
        yield return rand.Next(1, 20);
    }
}

var numbers = GenRandomNumbers()
    .Distinct()
    .Take(5)
    .ToArray();

Though it looks like the generator method will run for ever because of its closed loop, it will only run until it has generated 5 distinct numbers, because of how it yields.