Mar 212010
 

There are a number of different reasons that you would want to get your database results in a random order. I’ve had a number of different reasons to do this so far throughout my career, the first of which was in a simple T-SQL statement that we were pulling up a list of items that we wanted in a random order. Turns out that with T-SQL there is a really simple way to get your results in a random order. All you have to do is add the following to the end of your SQL statement.

“ORDER BY NewID()”

Simple isn’t it? I thought so too.

However, more recently I have been playing around more and more with LINQ and in specific LINQ to SQL. I thought that it would be just as easy in LINQ to SQL as it is in normal SQL to get my results in a random order, but turns out it is not quite that simple.

In standard LINQ it’s as easy as doing an order by on a randomized number or new GUID value and it will randomize your results according, or at least should in practice according to many different web sites that I have found. Turns out however that this does not work in LINQ to SQL  because there is no way for it to transfer into SQL.

Now, after some searching I found out that there is a fairly easy way to do it, but it requires some modification to your DataContext class, which isn’t a big deal if you plan on using it multiple times within the same application. The downside is that this snippet will need to be added to your data context class in every project you want to use it. Below is the code snippet you will need to add to your data context class, in my case the file name was {NameOfDataContext}.designer.cs.

partial class DataContext
{
    [Function(Name = "NEWID", IsComposable = true)]
    public Guid Random()
    {
        throw new NotImplementedException();
    }
}

Here is how to actually use this method.
DataContex DataBase = new DataContext();
var Results = (from row in DataBase.Customers
               order by DataBase.Random()
               select row);

This will give you your results in a very random order, now ideally you would have some sort of qualification in here to reduce the number of returns, but that’s obviously not a requirement.

Now if you just need a single random record from a huge set of records then there is a better way to do that using multiple queries, one to get the number of records within the set and then the second to get a certain individual record within the set.  An example of this is as follows:

var qry = from row in DataBase.Customers
          where row.IsActive
          select row;

int count = qry.Count();
int index = new Random().Next(count);

Customer cust = qry.Skip(index).FirstOrDefault();

  2 Responses to “Randomize Result Orders in T-SQL and LINQ to SQL”

  1. You can also do something like this:

    Random n = new Random();

    var results = t.MyTable
    .Select(c => new { c.Column1, c.Column2 }).AsEnumerable()
    .Select(c => new { c.Column1, c.Column2, Order = n.Next(100) });

    foreach (var item in results.OrderBy(c => c.Order))
    {

    }

  2. I’ve found this solution in a bunch of places. For some reason, I can’t figure out how to write the function in vb.net

    [Function(Name = “NEWID”, IsComposable = true)]
    public Guid Random()
    {
    throw new NotImplementedException();
    }

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.