select n random rows via linq2sql

Question

i know of using the

ORDER BY NEWID()

feature when running a regular sql command. I'm wanting to do the same thing, but via linq2sql.

I'd prefer not to select the whole range, add in a random number via rnd.Next(), and then sort by that...

Answer 1

Try the Take extension method:

.OrderBy(x=>x.NewId)
.Take(randomNumber);

What we're doing here is:

1. Order by NewId field in ASC order.
2. Select the N first row, where N is the random number.

In case that you want the order to be random, try this:

.OrderBy(x => Guid.NewGuid())
.Take(20);

What we're doing here is:

1. Order the list in a random order.
2. Take the top 20 rows.

Answer 2

Marc Gravell posted a solution here that allows you to define a function that uses NEWID in a partial class for the DataContext. Don't place it in the generated DataContext class otherwise future updates would wipe out what you've added.

Marc shows how to use it with the query expression syntax. Alternately, with dot notation, you could write:

var results = dc.Table.OrderBy(r => dc.Random()).Take(n);

In case you're not familiar with creating a partial class, just add a new class to your solution. Its name doesn't matter as long as the class definition uses the DataContext class name with the partial keyword. For example, if your DataContext is named XYZDataContext you can add a new class named XYZDataContextPartial.cs and define it as:

namespace YourNamespace
{
    public partial class XYZDataContext
    {
    }
}