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

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