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?

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

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.

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;
}

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.

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

If the purpose of getting random rows is sampling, I have talked very briefly here about a nice approach from Larson et al., Microsoft Research team where they have developed a sampling framework for Sql Server using materialized views. There is a link to the actual paper also.

I have random function query against DataTables:

var result = (from result in dt.AsEnumerable()
              order by Guid.NewGuid()
              select result).Take(3); 

Came here wondering how to get a few random pages from a small number of them, so each user gets some different random 3 pages.

This is my final solution, working querying with LINQ against a list of pages in Sharepoint 2010. It's in Visual Basic, sorry :p

Dim Aleatorio As New Random()

Dim Paginas = From a As SPListItem In Sitio.RootWeb.Lists("Páginas") Order By Aleatorio.Next Take 3

Probably should get some profiling before querying a great number of results, but it's perfect for my purpose

Using LINQ to SQL in LINQPad as C# statements look like

IEnumerable<Customer> customers = this.ExecuteQuery<Customer>(@"SELECT top 10 * from [Customers] order by newid()");
customers.Dump();

The generated SQL is

SELECT top 10 * from [Customers] order by newid()

The example below will call the source to retrieve a count and then apply a skip expression on the source with a number between 0 and n. The second method will apply order by using the random object (which will order everything in memory) and select the number passed into the method call.

public static class IEnumerable
{
    static Random rng = new Random((int)DateTime.Now.Ticks);

    public static T RandomElement<T>(this IEnumerable<T> source)
    {
        T current = default(T);
        int c = source.Count();
        int r = rng.Next(c);
        current = source.Skip(r).First();
        return current;
    }

    public static IEnumerable<T> RandomElements<T>(this IEnumerable<T> source, int number)
    {
        return source.OrderBy(r => rng.Next()).Take(number);
    }
}

i use this method for take random news and its work fine ;)

    public string LoadRandomNews(int maxNews)
    {
        string temp = "";

        using (var db = new DataClassesDataContext())
        {
            var newsCount = (from p in db.Tbl_DynamicContents
                             where p.TimeFoPublish.Value.Date <= DateTime.Now
                             select p).Count();
            int i;
            if (newsCount < maxNews)
                i = newsCount;
            else i = maxNews;
            var r = new Random();
            var lastNumber = new List<int>();
            for (; i > 0; i--)
            {
                int currentNumber = r.Next(0, newsCount);
                if (!lastNumber.Contains(currentNumber))
                { lastNumber.Add(currentNumber); }
                else
                {
                    while (true)
                    {
                        currentNumber = r.Next(0, newsCount);
                        if (!lastNumber.Contains(currentNumber))
                        {
                            lastNumber.Add(currentNumber);
                            break;
                        }
                    }
                }
                if (currentNumber == newsCount)
                    currentNumber--;
                var news = (from p in db.Tbl_DynamicContents
                            orderby p.ID descending
                            where p.TimeFoPublish.Value.Date <= DateTime.Now
                            select p).Skip(currentNumber).Take(1).Single();
                temp +=
                    string.Format("<div class=\"divRandomNews\"><img src=\"files/1364193007_news.png\" class=\"randomNewsImg\" />" +
                                  "<a class=\"randomNews\" href=\"News.aspx?id={0}\" target=\"_blank\">{1}</a></div>",
                                  news.ID, news.Title);
            }
        }
        return temp;
    }

If you use LINQPad, switch to C# program mode and do this way:

void Main()
{
    YourTable.OrderBy(v => Random()).FirstOrDefault.Dump();
}

[Function(Name = "NEWID", IsComposable = true)]
public Guid Random()
{
    throw new NotImplementedException();
}

var cust = (from c in ctx.CUSTOMERs.ToList() select c).OrderBy(x => x.Guid.NewGuid()).Taket(2);

Select random 2 row

To add to Marc Gravell's solution. If you are not working with the datacontext class itself (because you proxy it somehow e.g. to fake the datacontext for testing purposes), you cannot use the defined UDF directly: it will not be compiled to SQL because you're not using it in a subclass or partial class of your real data context class.

A workaround for this problem is to create a Randomize function in your proxy, feeding it with the query you want to be randomized:

public class DataContextProxy : IDataContext
{
    private readonly DataContext _context;

    public DataContextProxy(DataContext context)
    {
        _context = context;
    }

    // Snipped irrelevant code

    public IOrderedQueryable<T> Randomize<T>(IQueryable<T> query)
    {
        return query.OrderBy(x => _context.Random());
    }
}

Here is how you'd use it in your code:

var query = _dc.Repository<SomeEntity>();
query = _dc.Randomize(query);

To be complete, this is how to implement this in the FAKE datacontext (which uses in memory entities):

public IOrderedQueryable<T> Randomize<T>(IQueryable<T> query)
{
    return query.OrderBy(x => Guid.NewGuid());
}