1

I have checked this answered question and other articles on Stack Overflow. I prefer the Skip method. However, they are all for single record. Now assume I want to take 20 random records from a table, how can I do that?

I am trying two possibilities:

  1. Generate an array of indexes and use Skip for each. This, however, results in 20 queries (and each is ordered by Id too).

  2. Fetch the list of all Ids and pick randomly into an array and perform a 2nd query for detailed info all selected Ids.

  3. Or just use OrderBy as the other post suggested. I think this can be bad because the entire table is ordered?

Please tell me if there is any better solution.

Luke Vo
  • 17,859
  • 21
  • 105
  • 181
  • 1
    the answer link you shared in question, it has `take(5)` so can't you use that as `take(20)`, or I didn't get your exact requirement? – Syed Ali Taqi Oct 01 '18 at 04:36
  • If I use `Take(5)`, it would take 5 consecutive records, not random. – Luke Vo Oct 01 '18 at 04:43
  • wouldn't they already be randomized when you do `OrderBy(r => Guid.NewGuid())`? – Syed Ali Taqi Oct 01 '18 at 04:45
  • @SyedAliTaqi As my 3rd point, I think `OrderBy` will mess up the whole table, so I think using `Skip` would be better. When using `Skip` I only need to use `OrderBy` on Id instead. (please check the 2nd answer, not the marked answer) – Luke Vo Oct 01 '18 at 04:50
  • can you use [this](https://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql)? – Syed Ali Taqi Oct 01 '18 at 05:05
  • @SyedAliTaqi Yes, as the answer of this question you see below, I can use pure SQL approach if I cannot find anything else, but I prefer using EF if possible (maybe using [EF Database scalar function mapping](https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-2.0#modeling)). – Luke Vo Oct 01 '18 at 05:13
  • I know its writing pure SQL at the bottom line but something like [this](https://stackoverflow.com/a/35184536/3621001) can be used too. – Syed Ali Taqi Oct 01 '18 at 05:20
  • 1
    You can make more complicated form of randomness by sorting elements by order by id % cast(rand() * 1000 as decimal) clause for example and take top(20) of them. – Amin Mozhgani Oct 01 '18 at 05:22
  • can you generate a random number with in range of your table's ids and then use `Find or Single`? – Syed Ali Taqi Oct 01 '18 at 05:41
  • @SyedAliTaqi sadly the ids are not continuous and would not work. Currently I am using what you are suggesting (my 2nd point). – Luke Vo Oct 02 '18 at 03:48
  • right. I believe a stored procedure would be optimal in your case then. – Syed Ali Taqi Oct 02 '18 at 05:29

2 Answers2

0

Can you not use a Stored Proc to do this for you?

So your StoredProcs job is to return 20 records to the caller (I guess this is some Online Exam :D)

on the sql side you can use SQL Rand() to generate random numbers for all rows in your Table. Then Order them by Asc/Desc values of this Random column and Pick top 20.

Prateek Shrivastava
  • 1,877
  • 1
  • 10
  • 17
  • Yeah I think this would be the best way. Though I am a C# developer and not really good at writing SQL Server Stored Procedure, will try this one as last resort. – Luke Vo Oct 01 '18 at 04:44
0

Ordering the table by randomized guid will not leave any effects on the table. The Linq solution achieves what you want to do.

private IEnumerable<MyClass> GetRandomItems(int n)
{
    return _dbContext.MyClass.OrderBy(x => Guid.NewGuid()).Take(n);
}
kaffekopp
  • 2,551
  • 6
  • 13
  • This I do not know. Are you sure, is there any stat/test for this for a huge table? – Luke Vo Oct 01 '18 at 06:42
  • Aha, now I see your point. You are right, it will have negative impact on performance if used on a big table. You seem to already have got to the conclusion of using a stored procedure, and I agree that it would be the best approach (followed by the 20-query version). – kaffekopp Oct 01 '18 at 08:53