Randomizing LINQ to SQL queries
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
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;
Cross-posted from http://linqinaction.net