LINQ2SQL Get random record

Question

I found this handy query;

SELECT TOP 1 * FROM Advertising ORDER BY NEWID()

but need to turn it into a LINQ query.

I tried doing something like;

var a = (from record in Advertising
        select record)

but can't figure out how to do the ordering so that a random record is returned.

possible duplicate: http://stackoverflow.com/questions/648196/random-row-from-linq-to-sql — Polity, Nov 22 '11 at 04:12
also see: http://weblogs.asp.net/zeeshanhirani/archive/2008/07/23/returning-random-products-using-linq-to-sql.aspx — Polity, Nov 22 '11 at 04:13
With regards to the possible duplicate, I can't get .Random() to work — griegs, Nov 22 '11 at 04:16

score 4 · Accepted Answer · answered Nov 22 '11 at 04:22

4

It is probably easiest to just execute the command directly.

var a = ctx.ExecuteQuery<Advertising>("select top 1 * from Advertising order by NEWID()").First();

You could also do it with an sproc, or if you want a totally generic way to do it you can query the count, then generate a random number in that range and skip. (As others have noted while I was typing this)

answered Nov 22 '11 at 04:22

Chris Sainty

9,086
1
26
31

I thought there might be a purely LINQ way to do this but seems I might have been wrong so have gone with this – griegs Nov 22 '11 at 04:26
@griegs - Look at the link given earlier, http://weblogs.asp.net/zeeshanhirani/archive/2008/07/23/returning-random-products-using-linq-to-sql.aspx Its definitely possible to use LINQ – Polity Nov 22 '11 at 05:19
@Polity, yeah I saw that but looking at it it looked a little confusing in that I think another person would look at it and say WTF? So i dis-regarded it because of that – griegs Nov 22 '11 at 05:21

TheEvilPenguin · Answer 2 · 2011-11-22T04:22:14.460

3

This won't generate the SQL you're looking at, but should return a random result without having to retrieve all records:

Random r = new Random();
var record = r.Next(Advertising.Count());

var randomRecord = Advertising.Skip(record).FirstOrDefault(1);

Edit: It should also be more efficient than a random sort (unless SQL Server optimizes the sort away)

Edit 2: Take(1) should be FirstOrDefault() to return a record, instead of a list 1 record long.

edited Nov 22 '11 at 04:22

answered Nov 22 '11 at 04:13

TheEvilPenguin

5,634
1
26
47

@MichaelStum Does it really matter if it's two queries? – Andrew Barber Nov 22 '11 at 04:16
It may not be ideal, but it will get the desired result, and I'm not sure how to do it in a single query. It's complicated by the fact that only certain C# methods have mappings to SQL. Hopefully someone else will come along a better solution if that's a requirement. – TheEvilPenguin Nov 22 '11 at 04:17
this is a tiny table so no, two queries does not matter – griegs Nov 22 '11 at 04:18
1

@AndrewBarber Maybe yes, maybe not. I don't know the situation the user is in, and whether or not a raw sql query using DataContext.ExecuteQuery is a better option. Just pointing it out. – Michael Stum Nov 22 '11 at 04:18
It's an important consideration, but if performance isn't known to be an issue, I'd go with whichever the coder understands the best. If performance is shown to be an issue, then (properly documented) optimizations can be made. – TheEvilPenguin Nov 22 '11 at 04:27

score 0 · Answer 3 · answered Nov 22 '11 at 04:21

here is my get random method

Public Shared Function GetSingleRandom(Of T)(ByVal target As IEnumerable(Of T)) As T
        Dim r As New Random(DateTime.Now.Millisecond)
        Dim position As Integer = r.Next(target.Count)
        Return target.ElementAt(position)
    End Function

LINQ2SQL Get random record

3 Answers3