Linq to Entities, random order

Question

Hi,

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.

Ordering is O(n log n) of course. You could instead load everything into a list and then shuffle the list, which is only O(n). That will take a little bit more code though :)

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

Answer 2

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 3

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 4

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

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