LINQ to SQL - Select N Random Records

visibility 759 comment 0 access_time 2y languageEnglish

Language-Integrated Query (LINQ) is a set of technologies based on the integration of query capabilities directly into the C# or VB language in .NET. It allows intuitive query against SQL databases, XML, object list, etc. 

This article shows how to return a top N records randomly. 

LINQ to SQL

The following is one example of querying a table in a database via EntityFramework:

var query = from t in entityStore.Entities
	    select t;
var recordCount = 10;
var entityList = await query.AsNoTracking().Take(recordCount).ToArrayAsync().ConfigureAwait(false);

The result will show the top 10 records from the backend table of the entity store.

Random results

To return random results, we can simply add an OrderBy step to the integrated query and the value needs to be random.

For example, the following code snippet simply uses Guid.NewGuid() to return a random value for each record. 

var query = from t in entityStore.Entities
	    select t.Content;
var recordCount = 10;
var entityList = await query.OrderBy(t => Guid.NewGuid()).AsNoTracking().Take(recordCount).ToArrayAsync().ConfigureAwait(false);

In SQL Server, the above code will generate a SELECT statement with a new column derive using T-SQL function NEWID()

For databases that don't support unique GUID data type, the entire records may need to be brought back into memory to perform OrderBy action, which may lead to bad performance. Please take this into consideration when using this approach.

info Last modified by Raymond 2y copyright This page is subject to Site terms.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Tags