CODEWITHSUNDEEP
c#entity-frameworklinq-to-entities
NikolaiDante
NikolaiDante

Reputation: 18639

Linq to Entities, random order

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

Upvotes: 41

Views: 44724

Answers (12)

Gianpiero
Gianpiero

Reputation: 3547

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.

Upvotes: -1

Jon Skeet
Jon Skeet

Reputation: 1500665

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.

Upvotes: 56

Fr&#233;d&#233;ric
Fr&#233;d&#233;ric

Reputation: 9854

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.

Upvotes: 4

Mythlandia
Mythlandia

Reputation: 584

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.

Upvotes: 1

Drew Noakes
Drew Noakes

Reputation: 310917

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.

Upvotes: 43

Michael Damatov
Michael Damatov

Reputation: 15623

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

Upvotes: 30

drzaus
drzaus

Reputation: 25004

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

Upvotes: 0

lolo_house
lolo_house

Reputation: 69

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

Upvotes: 0

Segev -CJ- Shmueli
Segev -CJ- Shmueli

Reputation: 1621

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;

Upvotes: -1

Jamie
Jamie

Reputation: 988

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

Upvotes: 0

Migol
Migol

Reputation: 8448

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

Upvotes: 0

Klinger
Klinger

Reputation: 4970

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;

Upvotes: 0

Related Questions