LINQ to SQL - Select N Random Records

event 2020-09-22 visibility 2,810 comment 0 insights
more_vert
insights Stats
Raymond Raymond .NET Programming

Everything about .NET framework, .NET Core, .NET Standard, .NET 5 and .NET 6. 

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.

More from Kontext
comment Comments
No comments yet.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts