Fabrice's weblog

Tools and Source

News

My .NET Toolbox
An error occured. See the script errors signaled by your web browser.
No tools selected yet
.NET tools by SharpToolbox.com

Read sample chapters or buy LINQ in Action now!
Our LINQ book is also available on AMAZON

.NET jobs

Emplois .NET

Tuneo

ASP.NET Hosting transatlantys

Contact

Me

Others

Selected content

Archives

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;

Update: a similar solution that doesn't require a view and a custom SQL function has been suggested in the comments.


Cross-posted from http://linqinaction.net

Comments

AndrewSeven said:

Would using Guid.NewGuid() work?

var tools =

 from tool in db.Tools

 orderby Guid.NewGuid()

 select tool.Name;

# January 9, 2008 9:51 PM

Fabrice Marguerie said:

Good question! I'll give it a try and see if LINQ to SQL translates this to SQL.

# January 10, 2008 4:42 AM

Fabrice Marguerie said:

Andrew, I tested, and no ORDER BY clause is generated at all in SQL for the query you suggest.

# January 11, 2008 5:39 PM

Cecile said:

Hi,

I've found that the following works for getting a random record:

IEnumerable<Class> Query = db.ExecuteQuery<Class>(@"select top 1 ID from Table order by NewID()");

The only problem is that it only returns IEnumerable. I'm too lazy at the moment to try to figure out how to cast this to IQueryable. This lets me do what I need at the moment anyway.

FYI, I'm new to C# and Linq, so I appreciate all the help I get from blogs like yours.

# January 30, 2008 4:56 PM

Atomiton said:

You're essentially doing the same thing Cecile... The difference is that what Fabrice is doing enables you to to do Random Sorting, random Selection... all within LINQ.  You're essentially just writing SQL instead of letting LINQ do the work for you.  For Simple Queries you should try to use LINQ as it will optimize it very well.  For complex queries use db.ExecuteQuery.

# March 1, 2008 6:08 AM

Scott Brickey said:

The problem with using NewID (not that there's much alternative) is that for large collections, SQL Server must generate random GUIDs for EVERY RECORD in the table, then SORT the results by this (remember it's in memory, not indexed!).

Other solutions I've seen attempt to use some sort of hash mark or calculation (for auto-incrementing counters, not GUIDs) to quickly retrieve one out of many.

I think the solution used at a previous employer was a sproc that would pick a random (integer-based-counter) between MIN and MAX, then test that the record exists... in a simple loop (yes ugly) it would always return a valid result. I don't believe it looped for more than 2 or 3 iterations for the majority of calls.

Best of luck,

-Scott

# April 8, 2008 2:53 PM

Fulvio said:

Works beautifully!

= )

# October 23, 2008 9:34 AM

Raf Sistemleri said:

Can we radom sorting in linq without sql functions and views?

# December 18, 2008 4:04 AM

Fabrice Marguerie said:

# December 18, 2008 6:24 AM

Mike Flynn said:

A better solution would be to create a view that randomizes them, and use it in Linq To Sql.

# March 11, 2009 1:56 PM

Fabrice Marguerie said:

Mike, this would require a specific view each time you want to sort a query.

With the solution I give, you can randomize any LINQ to SQL query without creating anything additional.

# March 11, 2009 4:06 PM

Mike Flynn said:

That is true, but Linq To Entities doesnt support functions.

# March 19, 2009 1:39 PM

Fabrice Marguerie said:

Really? LINQ to Entities seems pretty limited compared to LINQ to SQL.

# March 19, 2009 5:44 PM

Shannon said:

Wouldn't it be easier to make a random number below the total record count, and do a skip on that number, then take(1)

# June 19, 2009 3:48 AM

Fabrice Marguerie said:

Shannon, that could be an option, but how do you know the total count of records without an additional query?

# June 19, 2009 5:16 PM

Droopy said:

Thanks for the example, works great!

# June 24, 2009 6:35 AM

bol said:

Thanks for the example, works great!

# August 1, 2009 5:11 AM

Michael said:

On blog about this topic I saw an example that maps to the sql newid function directly without having to create our own sql view and function. Also has the benefit that if the linq expression is in memory it will just use Guid.NewGuid() instead of going off to the database.

[Function(Name = "NEWID", IsComposable = true)]

public static Guid NewId() { return Guid.NewGuid(); }

# August 19, 2009 12:05 AM

Michael said:

Sorry the method should not be static as it won't work if it is so should be:

[Function(Name = "NEWID", IsComposable = true)]

public Guid NewId() { return Guid.NewGuid(); }

I've just been adding this method to the partial class of the DataContext.

# August 19, 2009 12:36 AM

Fabrice Marguerie said:

Thanks for the tip, Michael!

This is the same technique that is demonstrated on StackOverflow, with the additional advantage of redirecting to Guid.NewGuid() when possible.

# August 19, 2009 9:18 AM

jack.niu said:

we can also use guid, like var sites = db.XmlRpc_Sites.Where(p => p.Status == status).ToList().OrderBy(p => Guid.NewGuid());

# September 7, 2009 1:40 AM

Fabrice Marguerie said:

Jack, this is ok only if you want to sort on the client side (in memory). But when you want to sort on the server, like getting 10 random records from a table, you have to use the solutions given in this post.

# September 7, 2009 5:20 AM

ScoobyDon said:

I found this post when I searching the web for a way to get random records from LINQ... After looking at this for a little bit I thought, there has got to be an easier way where I don't have to do anything on the SQL Server side. If you're using LINQ to SQL Classes, this works just as well, without any server view or function addidtions...

Function RandomRecords As List(Of MyTable)

Dim db As new DataClasses1DataContext 'Has MyTable

Dim Results = db.ExecuteQuery(Of MyTable) _

("SELECT TOP 3 * FROM MyTable ORDER By NEWID()")

Return Results.ToList

End Function

# April 27, 2010 6:17 PM