97

How can I build a query where I would retrieve random rows?

If I were to write it in SQL then I would put an order by on newid() and chop off n number of rows from the top. Anyway to do this in EF code first?

I have tried creating a query that uses newid() and executing it using DbSet.SqlQuery(). while it works, its not the cleanest of solutions.

Also, tried retrieve all the rows and sorting them by a new guid. Although the number of rows are fairly small, its still not a good solution.

Any ideas?

Mel
  • 3,058
  • 4
  • 26
  • 40
  • 2
    See http://stackoverflow.com/questions/648196/random-row-from-linq-to-sql/3345272#3345272 – Ian Mercer Oct 16 '11 at 02:23
  • possible duplicate of [Linq to Entities, random order](http://stackoverflow.com/questions/654906/linq-to-entities-random-order) – Frédéric Jul 06 '15 at 14:02

4 Answers4

191

Just call:

something.OrderBy(r => Guid.NewGuid()).Take(5)
SLaks
  • 868,454
  • 176
  • 1,908
  • 1,964
  • hi it works fine, but will this be fast when table has more rows, i posted question [here](http://stackoverflow.com/q/30752514/2218697) – Shaiju T Jun 10 '15 at 14:11
  • 3
    See [this question](http://stackoverflow.com/q/7952147/1178314), it is unfortunately broken. It looks like `OrderBy` assumes the ranking function to be stable, which is not the case with a random generator. Linq to entities translate this to a sql query which may get different ranking for the same entity (as soon as your queries use `Include`). Then it causes the entity to get duplicated in the result list. – Frédéric Jul 06 '15 at 09:51
  • 1
    Not sure I'd trust this for tasks requiring an ironclad set of random rows -- I'd probably go with http://stackoverflow.com/a/654910/12484 or http://stackoverflow.com/a/648247/12484 instead -- but this simple approach worked just fine for my need which called for a single pseudo-random row for a non-customer-facing feature. +1. – Jon Schneider May 04 '16 at 13:37
  • @Toolkit probably not so strange, if Entity doesn't have an Oracle equivalent of `Guid.NewGuid()` (meaning, LinqToSql or whatever turns that into `NEWID()` but nobody programmed the same for Oracle). – drzaus Jun 27 '17 at 19:09
  • Is this approach efficient? I found somewhere else that this method is -for some performance consideration- discouraged. – Mohammed Noureldin Jul 12 '18 at 12:31
  • @drzaus of course oracle has an equivalent of Guid.NewGuid() – Toolkit Dec 29 '18 at 11:52
  • 2
    Since .NET 5 this causes weird behavior when using includes – Enes Sadık Özbek Apr 09 '21 at 16:55
49

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
drzaus
  • 24,171
  • 16
  • 142
  • 201
  • 2
    Thanks for the comparing, it really helps – Haobo Jul 07 '16 at 08:19
  • This is the right answer, The marked answer is not recommended as it might cause some performance issues. – Jacob Jun 20 '17 at 19:00
  • 1
    The question states "rows" in plural, how would you apply your solution to that? To me it seems like I will have to execute the same SQL multiple times, because `OrderBy(o => o.ID).Skip(skip).Take(5)` won't be really random, which might become a bottleneck in the performance. – Mike Mat Apr 11 '19 at 14:00
  • @MikeMat Just remove the "`.First()`". I was presenting a comparison with between some other answers I'd seen which don't appear anymore, so your point is doubly validated. But the `NewGuid` solution won't have the problem you describe. – drzaus Jul 19 '19 at 15:52
22

ef core 6 + there's a new function : EF.Functions.Random()

something.OrderBy(r => EF.Functions.Random()).Take(5)
Uttam Ughareja
  • 842
  • 2
  • 12
  • 21
  • 1
    NOTE: In case of SQL Server this function is using `RAND()` function which is executed once per all rows - this won't be random. See: https://dba.stackexchange.com/a/974/245938 Accepted solution is better. – Mr Patience Jan 22 '23 at 17:19
  • 1
    @MrPatience for me it returns random rows which are randomly sorted – VladL Jan 27 '23 at 21:46
  • Well, that wasn't my experience, but I had a complicated query with group by clause - maybe it was something there – Mr Patience Jan 28 '23 at 08:29
0

you can try follow method:

 public static String UdfGetRandomText()
        {
            using (Models.DbContextModel db = new Models.DbContextModel())
            {
                try
                {
                    Entity.tblRandomTexts t = new Entity.tblRandomTexts();
                    t = db.tblRandomTexts.OrderBy(r => Guid.NewGuid()).First();
                    return (t.TextBuddy + Environment.NewLine + t.TextWriter);
                }
                catch (Exception ee)
                {
                    return ee.Message;
                }
            }
        }

Provided you have a class in EF like the one below that creates the table.

public partial class tblRandomTexts
{
    [Key]
    public long TextRowID { get; set; }
    [MaxLength(1500)]
    public String TextBuddy { get; set; }
    [MaxLength(100)]
    public String TextWriter { get; set; }
}
Ali NajafZadeh
  • 51
  • 7
  • 20