Get MULTIPLE random records using Entity Framework?

Question

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:

Generate an array of indexes and use Skip for each. This, however, results in 20 queries (and each is ordered by Id too).
Fetch the list of all Ids and pick randomly into an array and perform a 2nd query for detailed info all selected Ids.
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.

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
– Syed Ali Taqi, Commented Oct 1, 2018 at 4:36
If I use Take(5), it would take 5 consecutive records, not random. — Luke Vo
– Luke Vo, Commented Oct 1, 2018 at 4:43
wouldn't they already be randomized when you do OrderBy(r => Guid.NewGuid())? — Syed Ali Taqi
– Syed Ali Taqi, Commented Oct 1, 2018 at 4: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
– Luke Vo, Commented Oct 1, 2018 at 4:50
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
– Amin Mozhgani, Commented Oct 1, 2018 at 5:22

Prateek Shrivastava · Accepted Answer · 2018-10-01 04:30:40Z

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.

answered Oct 1, 2018 at 4:30

Prateek Shrivastava

1,9221 gold badge13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Luke Vo Over a year ago

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.

kaffekopp · Accepted Answer · 2018-10-01 06:30:34Z

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);
}

answered Oct 1, 2018 at 6:30

kaffekopp

2,6299 silver badges14 bronze badges

2 Comments

Luke Vo Over a year ago

This I do not know. Are you sure, is there any stat/test for this for a huge table?

kaffekopp Over a year ago

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).

Collectives™ on Stack Overflow

Get MULTIPLE random records using Entity Framework?

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related