LINQ to SQL - Select N Random Records
insights Stats
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.