Some time ago I was in need of getting random records out of a chunk of database records. One method I found great was using the TSQL NEWID() function. The NEWID() function will create new random unique identifiers which you can use like in the sample TSQL query below:
SELECT TOP 10 * FROM Ads ORDER BY NEWID()
This query will return 10 random ads every time executed. Now, LINQ to SQL doesn’t have an equivalent to the NEWID() function so how do we accomplish the same results? So far I’ve come across two ways of doing it.
1) Use the TSQL NEWID() function
This may sound a bit strange but using a TSQL function from within LINQ to SQL is actually possible. By attaching a function to the LINQ to SQL data context that map a .Net method to a TSQL function we can use the SQL Server function right within our LINQ to SQL query. To do this we need to implement a method as a partial class to the data context and decorate it with the [Function] attribute like so:
partial class AdsDataClassesDataContext
{
[Function(Name="NEWID", IsComposable=true)]
public Guid Random()
{
throw new NotImplementedException();
}
}
There are two parameters for the [Function] attribute, Name and IsComposable. Name is the name of the SQL Server function (or stored procedure), NEWID in this case, and IsComposable is a boolean expressing whether we’re calling a SQL function or stored procedure (true if a function; false if a stored procedure).
But why are you throwing an exception in the method? It doesn’t really matter whatever goes into the method in this case. Once the method Random is called it will call the SQL Server function NEWID() regardless - just as we’ve attributed it.
Using the above we can get a random set of records like below:
AdsDataClassesDataContext dbContext = DB.GetAdsDataContext();
var a = from ad in dbContext.Ads
where ad.CultureId == cultureId
orderby dbContext.Random()
select ad;
2) Order an object collection using the Random class and the OrderBy extension method
Having a collection of objects we can make use of the Random class and OrderBy method like this:
Random rnd = new Random();
IEnumerable<Ad> ads = myAdsCollection.OrderBy(r => rnd.Next());
That’s two ways. I’m all ears hearing about more ways of achieving the same!
