Linq to Entities, random order

A simple way of doing this is to order by Guid.NewGuid() but then the ordering happens on the client side. You may be able to persuade EF to do something random on the server side, but that's not necessarily simple - and doing it using "order by random number" is apparently broken.

To make the ordering happen on the .NET side instead of in EF, you need AsEnumerable:

IEnumerable<MyEntity> results = context.MyEntity .Where(en => en.type == myTypeVar) .AsEnumerable() .OrderBy(en => context.Random()); 

It would be better to get the unordered version in a list and then shuffle that though.

Random rnd = ...; // Assume a suitable Random instance List<MyEntity> results = context.MyEntity .Where(en => en.type == myTypeVar) .ToList(); results.Shuffle(rnd); // Assuming an extension method on List<T> 

Shuffling is more efficient than sorting, aside from anything else. See my article on randomness for details about acquiring an appropriate Random instance though. There are lots of Fisher-Yates shuffle implementations available on Stack Overflow.

Jon's answer is helpful, but actually you can have the DB do the ordering using Guid and Linq to Entities (at least, you can in EF4):

from e in MyEntities orderby Guid.NewGuid() select e 

This generates SQL resembling:

SELECT [Project1].[Id] AS [Id], [Project1].[Column1] AS [Column1] FROM ( SELECT NEWID() AS [C1], -- Guid created here [Extent1].[Id] AS [Id], [Extent1].[Column1] AS [Column1], FROM [dbo].[MyEntities] AS [Extent1] ) AS [Project1] ORDER BY [Project1].[C1] ASC -- Used for sorting here 

In my testing, using Take(10) on the resulting query (converts to TOP 10 in SQL), the query ran consistently between 0.42 and 0.46 sec against a table with 1,794,785 rows. No idea whether SQL Server does any kind of optimisation on this or whether it generated a GUID for every row in that table. Either way, that would be considerably faster than bringing all those rows into my process and trying to sort them there.

The NewGuid hack for sorting it server side unfortunately causes entities to get duplicated in case of joins (or eager fetching includes).

See this question about this issue.

To overcome this issue, you may use instead of NewGuid a sql checksum on some unique value computed server side, with a random seed computed once client side to randomize it. See my answer on previously linked question.

(cross-posting from EF Code First: How to get random rows)

Comparing two options:

Skip(random number of rows)

Method

private T getRandomEntity<T>(IGenericRepository<T> repo) where T : EntityWithPk<Guid> { var skip = (int)(rand.NextDouble() * repo.Items.Count()); return repo.Items.OrderBy(o => o.ID).Skip(skip).Take(1).First(); } 

• Takes 2 queries

Generated SQL

SELECT [GroupBy1].[A1] AS [C1] FROM (SELECT COUNT(1) AS [A1] FROM [dbo].[People] AS [Extent1]) AS [GroupBy1]; SELECT TOP (1) [Extent1].[ID] AS [ID], [Extent1].[Name] AS [Name], [Extent1].[Age] AS [Age], [Extent1].[FavoriteColor] AS [FavoriteColor] FROM (SELECT [Extent1].[ID] AS [ID], [Extent1].[Name] AS [Name], [Extent1].[Age] AS [Age], [Extent1].[FavoriteColor] AS [FavoriteColor], row_number() OVER (ORDER BY [Extent1].[ID] ASC) AS [row_number] FROM [dbo].[People] AS [Extent1]) AS [Extent1] WHERE [Extent1].[row_number] > 15 ORDER BY [Extent1].[ID] ASC; 

Guid

Method

private T getRandomEntityInPlace<T>(IGenericRepository<T> repo) { return repo.Items.OrderBy(o => Guid.NewGuid()).First(); } 

Generated SQL

SELECT TOP (1) [Project1].[ID] AS [ID], [Project1].[Name] AS [Name], [Project1].[Age] AS [Age], [Project1].[FavoriteColor] AS [FavoriteColor] FROM (SELECT NEWID() AS [C1], [Extent1].[ID] AS [ID], [Extent1].[Name] AS [Name], [Extent1].[Age] AS [Age], [Extent1].[FavoriteColor] AS [FavoriteColor] FROM [dbo].[People] AS [Extent1]) AS [Project1] ORDER BY [Project1].[C1] ASC 

So in newer EF, you can again see that NewGuid is translated into SQL (as confirmed by @DrewNoakes https://stackoverflow.com/a/4120132/1037948). Even though both are "in-sql" methods, I'm guessing the Guid version is faster? If you didn't have to sort them in order to skip, and you could reasonably guess the amount to skip, then maybe the Skip method would be better.

lolo_house has a really neat, simple and generic solution. You just need to put the code in a separate static class to make it work.

using System; using System.Collections.Generic; using System.Linq; namespace SpanishDrills.Utilities { public static class LinqHelper { public static IEnumerable<T> Randomize<T>(this IEnumerable<T> pCol) { List<T> lResultado = new List<T>(); List<T> lLista = pCol.ToList(); Random lRandom = new Random(); int lintPos = 0; while (lLista.Count > 0) { lintPos = lRandom.Next(lLista.Count); lResultado.Add(lLista[lintPos]); lLista.RemoveAt(lintPos); } return lResultado; } } } 

Then to use the code just do:

var randomizeQuery = Query.Randomize(); 

So simple! Thank you lolo_house.

How about this:

  var randomizer = new Random(); var results = from en in context.MyEntity where en.type == myTypeVar let rand = randomizer.Next() orderby rand select en;  

Toro's answer is the one I would use, but rather like this:

static IEnumerable<T> Randomize<T>(this IEnumerable<T> source) { var list = source.ToList(); var newList = new List<T>(); while (source.Count > 0) { //choose random one and MOVE it from list to newList } return newList; } 

Here is a nice way of doing this (mainly for people Googling).

You can also add .Take(n) on the end to only retrieve a set number.

model.CreateQuery<MyEntity>( @"select value source.entity from (select entity, SqlServer.NewID() as rand from Products as entity where entity.type == myTypeVar) as source order by source.rand"); 

I think it's better not to add properties to the class. Better to use the position:

public static IEnumerable<T> Randomize<T>(this IEnumerable<T> pCol) { List<T> lResultado = new List<T>(); List<T> lLista = pCol.ToList(); Random lRandom = new Random(); int lintPos = 0; while (lLista.Count > 0) { lintPos = lRandom.Next(lLista.Count); lResultado.Add(lLista[lintPos]); lLista.RemoveAt(lintPos); } return lResultado; } 

And the call will (as toList() or toArray()):

var result = IEnumerable.Where(..).Randomize();

Theoretically speaking (I haven't actually tried it yet), the following should do the trick :

Add a partial class to your context class :

public partial class MyDataContext{ [Function(Name = "NEWID", IsComposable = true)] public Guid Random() { // you can put anything you want here, it makes no difference throw new NotImplementedException(); } } 

implementation :

from t in context.MyTable orderby context.Random() select t; 

With Entity Framework Core 6+ there's a new function: EF.Functions.Random()

context.MyEntities .Where(en => en.type == myTypeVar) .OrderBy(en => EF.Functions.Random()); 

or

from en in context.MyEntities where en.type == myTypeVar orderby EF.Functions.Random() select en; 

EDIT

The advantage of this approach is it uses the internal function of the database engine and one single query. Finally Microsoft implemented it for SQL Server, SQLite, and Azure Cosmos DB. See here and [here2

Moreover it has the additional advantage to allow you to take a random subset of MyEntities by using Take(n) at the end of the query.