Is there a way to return a random row from a table using LINQToSQL?
A:
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
2009-05-05 22:35:05
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
kronoz
2009-05-05 22:37:09
Really? I assumed people would be able to figure it out... Thanks though...
swilliams
2009-05-05 22:39:22
A:
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
2009-05-05 22:35:14
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
2009-05-05 22:42:35
A:
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
2009-05-05 22:35:45
+3
A:
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
2009-05-05 22:36: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
2009-05-05 22:44:09
Im making a game, with random stages and the stage details are tied to the row I am retreiving.
Drahcir
2009-05-05 22:57:09
Tried it out, currently I have 2 rows in my table but the random function always returns 0
Drahcir
2009-05-08 19:09:16
A:
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
2009-05-05 22:37:45
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
2010-04-10 11:30:12
A:
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
2010-04-10 11:32:12