Random row from Linq to Sql

What is the best (and fastest) way to retrieve a random row using Linq to SQL when I have a condition, e.g. some field must be true?

Solution 1:

You can do this at the database, by using a fake UDF; in a partial class, add a method to the data context:

partial class MyDataContext {
     [Function(Name="NEWID", IsComposable=true)] 
     public Guid Random() 
     { // to prove not used by our C# code... 
         throw new NotImplementedException(); 
     }
}

Then just order by ctx.Random(); this will do a random ordering at the SQL-Server courtesy of NEWID(). i.e.

var cust = (from row in ctx.Customers
           where row.IsActive // your filter
           orderby ctx.Random()
           select row).FirstOrDefault();

Note that this is only suitable for small-to-mid-size tables; for huge tables, it will have a performance impact at the server, and it will be more efficient to find the number of rows (Count), then pick one at random (Skip/First).

for count approach:

var qry = from row in ctx.Customers
          where row.IsActive
          select row;

int count = qry.Count(); // 1st round-trip
int index = new Random().Next(count);

Customer cust = qry.Skip(index).FirstOrDefault(); // 2nd round-trip

Solution 2:

Another sample for Entity Framework:

var customers = db.Customers
                  .Where(c => c.IsActive)
                  .OrderBy(c => Guid.NewGuid())
                  .FirstOrDefault();

This does not work with LINQ to SQL. The OrderBy is simply being dropped.

Solution 3:

EDIT: I've only just noticed this is LINQ to SQL, not LINQ to Objects. Use Marc's code to get the database to do this for you. I've left this answer here as a potential point of interest for LINQ to Objects.

Strangely enough, you don't actually need to get the count. You do, however, need to fetch every element unless you get the count.

What you can do is keep the idea of a "current" value and the current count. When you fetch the next value, take a random number and replace the "current" with "new" with a probability of 1/n where n is the count.

So when you read the first value, you always make that the "current" value. When you read the second value, you might make that the current value (probability 1/2). When you read the third value, you might make that the current value (probability 1/3) etc. When you've run out of data, the current value is a random one out of all the ones you read, with uniform probability.

To apply that with a condition, just ignore anything which doesn't meet the condition. The easiest way to do that is to only consider the "matching" sequence to start with, by applying a Where clause first.

Here's a quick implementation. I think it's okay...

public static T RandomElement<T>(this IEnumerable<T> source,
                                 Random rng)
{
    T current = default(T);
    int count = 0;
    foreach (T element in source)
    {
        count++;
        if (rng.Next(count) == 0)
        {
            current = element;
        }            
    }
    if (count == 0)
    {
        throw new InvalidOperationException("Sequence was empty");
    }
    return current;
}

Solution 4:

One way to achieve efficiently is to add a column to your data Shuffle that is populated with a random int (as each record is created).

The partial query to access the table in random order is ...

Random random = new Random();
int seed = random.Next();
result = result.OrderBy(s => (~(s.Shuffle & seed)) & (s.Shuffle | seed)); // ^ seed);

This does an XOR operation in the database and orders by the results of that XOR.

Advantages:-

1. Efficient: SQL handles the ordering, no need to fetch the whole table
2. Repeatable: (good for testing) - can use the same random seed to generate the same random order

This is the approach used by my home automation system to randomize playlists. It picks a new seed each day giving a consistent order during the day (allowing easy pause / resume capabilities) but a fresh look at each playlist each new day.

Solution 5:

if you want to get e.g. var count = 16 random rows from table, you can write

var rows = Table.OrderBy(t => Guid.NewGuid())
                        .Take(count);

here I used E.F, and the Table is a Dbset