Random LINQ to SQL records w/ "int" primary keys

Question

How do I get a 5 random records from a LINQ to SQL Table[T]?

All the examples I've found via google use uniqueidentifier as the primary key

http://www.davidmuto.com/Blog.muto/View/random-records-with-linq-to-sql

Is there a way to do this when the primary key is an auto-incrementing integer?

Some records can be deleted too, so the min to max ID set will have holes.

Any help is greatly appreciated.

-CV

Answer 1

To start you could take a look at this post which appears to have a way to do what you are seeking: http://weblogs.asp.net/fmarguerie/archive/2008/01/10/randomizing-linq-to-sql-queries.aspx

Let's detail the solution that uses a SQL user-defined function. The most common way to sort records randomly is to use the NEWID SQL Server function. This is what this solution uses.

First, create the following view:

CREATE VIEW RandomView AS SELECT NEWID() As ID Then create the following function that uses the view:

CREATE FUNCTION GetNewId ( ) RETURNS uniqueidentifier AS BEGIN RETURN (SELECT ID FROM RandomView) END

The view is required because it's not possible to directly use NEWID in a scalar function.

You can then map the GetNewId user-defined function using LINQ to SQL's Function attribute. Again, see chapter 8 for the details.

That's it! You can now write LINQ queries as usual. Here is an example to pick a random object:

var tool = db.Tools.OrderBy(t => db.GetNewId()).First()

Here is another example that uses GetNewId to sort results randomly:

var tools = from tool in db.Tools
orderby db.GetNewId() select tool.Name;

There is also this post on SO: http://stackoverflow.com/questions/648196/random-row-from-linq-to-sql