LINQ to SQL: How to get a Random Record from a Table inside the SQL Server Database

I wanted to get a random record from a particular table on the SQL Server database. For my case, I had a table containing quotes, and I wanted to randomly pick on to show on one corner of my web site. I am also using LINQ to SQL for this web site, so the solution here will be using LINQ, although the approach is pretty simple and flexible.

The idea centers on ordering the records by a random parameter an then getting the TOP n number of records. The NEWID() function from SQL Server makes a great candidate for getting a random parameter since this method generates a GUID.

In SQL Server, try out this query.

SELECT * FROM Quote ORDER by NEWID();

SELECT TOP 1 * FROM Quote ORDER by NEWID();

Now for our code, we need to duplicate this behavior on .NET. I needed to get an equivalent of NEWID() on my LINQ to SQL DBML. Note that we cannot use NEWID() directly on a stored procedure or function since SQL Server will complain with an “Invalid use of side-effecting or time-dependent operator in 'newid' within a function” error. The workaround is to create a view that uses the NEWID() and then a function that selects from the view.

CREATE VIEW [dbo].[RandomView]
AS
SELECT NEWID() As ID
GO
CREATE FUNCTION [dbo].[GetNewId]
(
)
RETURNS uniqueidentifier
AS
BEGIN
RETURN (SELECT ID FROM RandomView)
END
GO

On our DBML, we simple drag and drop the Quote table and the GetNewID function to our designer. The code below takes a random record from the Quotes table.

DataContext db = new QuoteDataContext();
var quote = db.Quote.OrderBy(q => db.GetNewId()).First();

Actually, once the objects are in place, we can use them to get x number of random records from any table.