Random row from Linq to Sql


Question

What is the best (and fastest) way to retrieve a random row using Linq to SQL when I have a condition, e.g. some field must be true?

1
110
12/27/2016 3:20:12 PM

Accepted Answer

You can do this at the database, by using a fake UDF; in a partial class, add a method to the data context:

partial class MyDataContext {
     [Function(Name="NEWID", IsComposable=true)] 
     public Guid Random() 
     { // to prove not used by our C# code... 
         throw new NotImplementedException(); 
     }
}

Then just order by ctx.Random(); this will do a random ordering at the SQL-Server courtesy of NEWID(). i.e.

var cust = (from row in ctx.Customers
           where row.IsActive // your filter
           orderby ctx.Random()
           select row).FirstOrDefault();

Note that this is only suitable for small-to-mid-size tables; for huge tables, it will have a performance impact at the server, and it will be more efficient to find the number of rows (Count), then pick one at random (Skip/First).


for count approach:

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

int count = qry.Count(); // 1st round-trip
int index = new Random().Next(count);

Customer cust = qry.Skip(index).FirstOrDefault(); // 2nd round-trip
167
8/24/2010 7:16:24 PM

Another sample for Entity Framework:

var customers = db.Customers
                  .Where(c => c.IsActive)
                  .OrderBy(c => Guid.NewGuid())
                  .FirstOrDefault();

This does not work with LINQ to SQL. The OrderBy is simply being dropped.


Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon