3

Is there a way to return a random row from a table using LINQToSQL?

Rui Jarimba
  • 11,166
  • 11
  • 56
  • 86
Drahcir
  • 12,311
  • 19
  • 63
  • 76
  • 2
    Duplicate of http://stackoverflow.com/questions/648196/random-row-from-linq-to-sql . The first answer has two good methods, the second (using Count) is probably better. – Matthew Flaschen May 05 '09 at 22:36

6 Answers6

6

Yes, generate a random number in the range of the table's count and Skip() that number, Take(1) then return the first element of the resulting sequence. Something like

var R = new Random();
var ToSkip = R.Next(0, ctx.Customers.Count);

return ctx.Customers.Skip(ToSkip).Take(1).First();

But it's a bit ugly. Is there an actual requirement that calls for getting a random row in a SQL table?

Denis Troller
  • 7,411
  • 1
  • 23
  • 36
  • There could be, say for example in a flash-card type application that asks a user random questions. Although tbh I'd load the questions up, then sort them in a random order instead. Depends how many rows there are. – Neil Barnwell May 05 '09 at 22:44
  • Im making a game, with random stages and the stage details are tied to the row I am retreiving. – Drahcir May 05 '09 at 22:57
  • Tried it out, currently I have 2 rows in my table but the random function always returns 0 – Drahcir May 08 '09 at 19:09
  • Worked, had to remove the "-1" – Drahcir May 08 '09 at 19:21
  • If you're using the First() extension method, then Take(1) is redundant. – Jim G. Sep 03 '09 at 14:26
0

You can get LinqToSQL to generate SQL which uses the SQL Server NEWID() function. Here is my implementation:

namespace Data    // change to whatever the namespace of your DataContext is, or remove
{
    /// <summary>
    /// Add RANDOM() extension to the Data context...
    /// </summary>
    partial class DefaultDataContext  // change to the name of your DataContext
    {
        [System.Data.Linq.Mapping.Function(Name = "NEWID", IsComposable = true)]
        public Guid Random()
        {
            // this code is not actually executed, it simply provides a way to access 
            // T-SQL "NEWID()" function from Linq to SQL
            throw new NotImplementedException();
        }
    }
}

Example of usage, to pull a random product from the database:

var product = (from p in db.Products     // our DataContext instance is called db here
               orderby db.Random()
               select p).FirstOrDefault()
Adam Nofsinger
  • 4,004
  • 3
  • 34
  • 42
0

You can use a random number and Skip() to get to that row:

int randomNum = 10; // <-- determined by your favorite RNG
var row = mydb.Objects.Skip(randomNum).Take(1);
swilliams
  • 48,060
  • 27
  • 100
  • 130
  • perhaps you should eliminate the integer literal assignment for randomNum there to avoid confusion... reminds me of that xkcd article something like x = 6; // guaranteed random, decided by dice roll – ljs May 05 '09 at 22:37
0

Never tried it, but i suspect the following will get the job done

Random rnd = new Random();

int count = db.MyTable.Count();
int skip = rnd.Next(count);

var row = (from r in db.MyTable
           select r).Skip(skip).Take(1);
ScottS
  • 8,455
  • 3
  • 30
  • 50
  • Nah, that won't work unfortunately - unless you resolve the query before the .Skip call (with .ToList or .AsEnumerable or similar). LINQ to SQL's LINQ provider will choke on the call to rnd.Next saying it can't be turned into a proper SQL statement. – Erik Forbes May 05 '09 at 22:42
  • revised to determine random number before executing the query. – ScottS May 05 '09 at 23:01
0

I'd try something like:

query.Skip(rand.Next()).Take(1).FirstOrDefault();

With rand being initialised as returning a number between a specific range.

Be sure to check for null as the return value in case the random number generated was greater than the number of rows (of course you could initialise it's upper bound as the number of rows, too).

Neil Barnwell
  • 41,080
  • 29
  • 148
  • 220
0

technically:

var randomRow = (from rows in db.Table
  select shipper).First();

because you can't ever be sure of the row order, unless you specify the order by clause.

scottm
  • 27,829
  • 22
  • 107
  • 159
  • 1
    LOL, yah, but the customer is not going to be happy when the same 4 products keep showing up in the "random" product highlight box. – Adam Nofsinger Apr 10 '10 at 11:30