LINQ to entities Query to randomize row selection.

Question

I am writing an app using .NET, C#, LINQ to entities and SQL Server 2008.

I would like to pick a row randomly from a table. Is there a way to achieve this using the LINQ queries. One approach would be to get a list of rows from the table and then pick one of them randomly, which is very straight forward.

Just curious, if there is a way to include the randomness attribute in the LINQ.

Answer 1

Excuse the Pseudocode:

    static IEnumerable<RowType> RandomRows()
    {
        while (true)
        {
            yield return GetRowByID((new Random).Next(NumberOfRowsInTable));
        }
    }

Answer 2

If you don't have a guaranteed-sequential ID space, you'd need to apply some sort of ordering to the table, count the number of records, skip a random number of them, and take one. So:

var query = 
    from item in db.Items
    orderby item.Id 
    select item;

// a random number in 0..count-1
var rownum = new Random().Next(query.Count() - 1);

var row = query
    .Skip(rownum)
    .Take(1);

Ordering is necessary because LINQ to Entities doesn't support Skip on unordered collections (only makes sense).