Linq to Entities, random order

Question

How do i return matching entities in a random order?
Just to be clear this is Entity Framework stuff and LINQ to Entities.

(air code)

IEnumerable<MyEntity> results = from en in context.MyEntity
                                where en.type == myTypeVar
                                orderby ?????
                                select en;

Thanks

Edit:
I tried adding this to the context:

public Guid Random()
{
    return new Guid();
}

And using this query:

IEnumerable<MyEntity> results = from en in context.MyEntity
                                where en.type == myTypeVar
                                orderby context.Random()
                                select en;

But i got this error:

System.NotSupportedException: LINQ to Entities does not recognize the method 'System.Guid Random()' method, and this method cannot be translated into a store expression..

Edit (Current code):

IEnumerable<MyEntity> results = (from en in context.MyEntity
                                 where en.type == myTypeVar
                                 orderby context.Random()
                                 select en).AsEnumerable();

Answer 1

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.

Answer 2

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.

Answer 3

The simple solution would be creating an array (or a List<T>) and than randomize its indexes.

EDIT:

static IEnumerable<T> Randomize<T>(this IEnumerable<T> source) {
  var array = source.ToArray();
  // randomize indexes (several approaches are possible)
  return array;
}

EDIT: Personally, I find the answer of Jon Skeet is more elegant:

var results = from ... in ... where ... orderby Guid.NewGuid() select ...

And sure, you can take a random number generator instead of Guid.NewGuid().

Answer 4

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.

Answer 5

The solutions provided here execute on the client. If you want something that executes on the server, here is a solution for LINQ to SQL that you can convert to Entity Framework.

Answer 6

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.

Answer 7

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;

Answer 8

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;
}

Answer 9

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");

Answer 10

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();

Answer 11

(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.

Answer 12

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;