LINQ in Action - THE LINQ book

Yesterday, a developer asked in the LINQ in Action forum and in Microsoft's official LINQ forum how to write a LINQ to SQL query that would return random records from the database.

It's not something built-in. There is no Random query operator provided by Microsoft. In addition, it can't be done simply by using the System.Random class, because everything in a LINQ to SQL query must be translatable to SQL.

Jim replied with several options:

• Use a pass-through query to get the results
• Use a view to return the ID of the record from tblImages from your subselect and map to that view. Then use a LINQ query to join the tblProperties through the Randomize view and then to the tblImages
• Use a scalar TSQL Function to get a random picture and include that in your LINQ query.
• Use a stored procedure to return the results.

As Jim points out, we discuss consuming pass-through queries, functions and stored procedures in chapter 8 of LINQ in Action.

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

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;

Update: Here is a similar solution that doesn't require a view and a custom SQL function.