What is the easiest Linq query to create an N-length array of different identifiers obtained randomly?

Question

Scenario

I have a database consisting of a table called Problems, which in turn consists of two columns:

• ID
• Problem

I filled the tables with millions of records.

In my application, I want to get a set of N individual records made at random to create a problem sheet. The problem consists of N problems. Randomness should be good enough to make sure that I am not creating a problem sheet that is similar to the previous exam.

What is the easiest way to do this with Linq?

Answer 1

Sort of:

IQueryable<Problem> problems = db.Problems;
int count = problems.Count();
List<Problem> sheet = new List<Problem>(n);
Random rand = new Random();
while(sheet.Count < n) {
    var next = problems.OrderBy(p => p.ID).Skip(rand.Next(count))
        .FirstOrDefault();
    if(next != null && !sheet.Any(p => p.ID == next.ID) {
        sheet.Add(next);
    }
}

?

Answer 2

Problems N - , .

Problems.OrderBy(n => Guid.NewGuid()).Take(N)

, Guid's N.

linq, N [0,Problems.Count()), .

    IEnumerable<int>  GenrateIDs(int max)
    {
        Random rand = new Random();
        HashSet<int> IDs = new HashSet<int>();
        while (IDs.Count < max)
        {
            IDs.Add(rand.Next(max));
        }
        return IDs.AsEnumerable();
    }

Answer 3

Here is the TSQL version to reduce rounding using the LINQ to SQL method ExecuteQuery<T>:

List<int> ids = db.ExecuteQuery<int>(@"
declare @n int = {0}, @count int, @offset int, @id int
declare @ids table (pos int identity(1,1) not null, id int not null)
set @count = (select COUNT(1) from Problems)
if @count < @n set @n = @count
while @n > 0
begin
    set @offset = 1 + CAST(FLOOR(RAND() * @count) as int)
    select @id = (
                    select Id from (select Id,
                    ROW_NUMBER() over (order by Id) as __row
        from Problems) x where x.__row = @offset)
    if not exists (select 1 from @ids where id = @id)
    begin
        set @n = @n - 1
        insert @ids (id) values (@id)
    end
end
select id from @ids order by pos", n).ToList();