ASP.NET Hosting

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

22 Comments

  • Would using Guid.NewGuid() work?

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

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

  • Hi,

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

    IEnumerable Query = db.ExecuteQuery(@"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.

  • 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.

  • 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

  • Works beautifully!
    = )

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

  • Raf, see here: http://www.codeproject.com/KB/linq/linqrandomsample.aspx

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

  • 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.

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

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

  • 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)

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

  • Thanks for the example, works great!

  • Thanks for the example, works great!

  • 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(); }

  • 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.

  • 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.

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

  • 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.

  • 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

Comments have been disabled for this content.