Picking random record from Entity Framework database without OrderBy

Question

I try to get random record from database:

 personToCall = db.Persons.Skip(toSkip).Take(1).First();

but I get exception which tells me:

{"The method 'Skip' is only supported for sorted input in LINQ to Entities. The method 'OrderBy' must be called before the method 'Skip'."}

Can I do it without OrderBy? Sorting data structure (O(nlogn)) to pick random element(which should be constant) doesn't look wise.

EDIT: I use Entity Framework 6.1.1.

@Lukazoid Didn't hear about them. The model looks like `public class Person { public int Id { get; set; } etc....}` and Entity Framework created table basing on this code. — Yoda, Sep 14 '14 at 16:09
Even if you do I don't see how that would help. If they were guaranteed to be sequential and without gaps you could find the max and min with an efficient index operation then select a random number in that range with a seek. But they aren't guaranteed to be. — Martin Smith, Sep 14 '14 at 16:12

score 32 · Accepted Answer · answered Sep 14 '14 at 16:03

32

You can have something like :

personToCall = db.Persons.OrderBy(r => Guid.NewGuid()).Skip(toSkip).Take(1).First();

You should use FirstOrDefault to be mode defensive.

Here the dark lord teaching the force to yoda! what is the world coming to!

answered Sep 14 '14 at 16:03

DarthVader

52,984
76
209
300

1

Unfortunately, this is dependent on the version of Entity Framework that the OP is using. – Justin Niessner Sep 14 '14 at 16:04
@JustinNiessner I use EF 6.1.1. – Yoda Sep 14 '14 at 16:07
DarthVader will this LINQ query make any changes to my table? – Yoda Sep 14 '14 at 16:11
Thanks. Does it make any difference in efficiency if I write: `db.Persons.OrderBy(r => r.Id).Skip(toSkip).Take(1).First();` instead of NewGuid version? – Yoda Sep 14 '14 at 16:19
It could avoid a sort if the id is indexed. But to get a truly random appropriate value to use for toSkip you would need to count the rows first. So that would take 1.5 passes through the data on average. – Martin Smith Sep 14 '14 at 16:21
10

Haha, the last line is just too funny – wingerse Oct 13 '15 at 19:56
1

this is a good solution if you have ~1000 records or don't care about your CPU and time – Toolkit Mar 28 '17 at 17:14

score 6 · Answer 2 · edited Jan 14 '21 at 05:00

6

First you need to get the random number from 1 to max record, see this

Random rand = new Random();
int toSkip = rand.Next(0, db.Persons.Count());

db.Persons.Skip(toSkip).Take(1).First();

with order by you can use the Guid.NewGuid()

db.Persons.OrderBy(x=>x.Guid.NewGuid()).Skip(toSkip).Take(1).FirstOrDefault();

edited Jan 14 '21 at 05:00

Timwi

65,159
33
165
230

answered Sep 14 '14 at 16:21

Ali Adravi

21,707
9
87
85

2

This is simple and good, but it should be rand.Next(0, ...) or the first record will never be included – Fredrik Stolpe Mar 28 '15 at 20:09
1

However, I noticed now that this becomes slow when the number of records increases, I now have approx 30,000 and it's too slow so I have to give up on this method – Fredrik Stolpe Apr 24 '15 at 08:29
A set of parenthesis are needed after `Count` on the second line of the example code. – Mike Grove aka Theophilus Feb 09 '18 at 16:16

score 5 · Answer 3 · answered Sep 14 '14 at 16:06

There is no way to do this without an ordering clause.

personToCall = db.Persons.OrderBy(r => Random.Next()).First();

That could be slow depending on the size of your Persons table, so if you wanted to make this fast, you'd have to add a column to Person, or join it to a dictionary of random numbers and Person keys, then order by that. But that is rarely a wise solution.

Better to ask a higher level question about the overall task at hand.

MrGadget · Answer 4 · 2016-01-09T05:33:41.457

-1

To avoid the OrderBy, dump to a List and random pick against the Index:

VB

With New List(Of Persons)
    .AddRange(db.Persons)
    PersonToCall = .Item(New Random().Next(0, .Count - 1))
End With

C#

var people = new List<Persons>();
people.AddRange(db.Persons);
personToCall  = people.Item(new Random().Next(0, people.Count - 1));

edited Jan 09 '16 at 05:33

answered Jan 09 '16 at 05:01

MrGadget

1,258
1
10
19

1

If fetching the whole table doesn't scale for your purposes, another thought might be to have a View in your model on the database side: SELECT TOP 1 * FROM table ORDER BY NEWID() – MrGadget Jan 09 '16 at 05:14

Picking random record from Entity Framework database without OrderBy

4 Answers4

Linked