EF Code First: How to get random rows

Question

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?

See stackoverflow.com/questions/648196/random-row-from-linq-to-sql/… — Ian Mercer
– Ian Mercer, Commented Oct 16, 2011 at 2:23

SLaks · Accepted Answer · 2012-04-16 11:54:41Z

196

Just call:

something.OrderBy(r => Guid.NewGuid()).Take(5)

edited Apr 16, 2012 at 11:54

user142162

answered Oct 16, 2011 at 2:09

SLaks

891k182 gold badges1.9k silver badges2k bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Shaiju T Over a year ago

hi it works fine, but will this be fast when table has more rows, i posted question here

Frédéric Over a year ago

See this question, 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.

Jon Schneider Over a year ago

Not sure I'd trust this for tasks requiring an ironclad set of random rows -- I'd probably go with stackoverflow.com/a/654910/12484 or 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.

drzaus Over a year ago

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

Enes Sadık Özbek Over a year ago

Since .NET 5 this causes weird behavior when using includes

|

drzaus · Accepted Answer · 2013-06-14 16:25:28Z

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

This is the right answer, The marked answer is not recommended as it might cause some performance issues.
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.
@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.

Uttam Ughareja · Accepted Answer · 2022-08-01 05:09:28Z

36

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

something.OrderBy(r => EF.Functions.Random()).Take(5)

answered Aug 1, 2022 at 5:09

Uttam Ughareja

1,4952 gold badges21 silver badges27 bronze badges

3 Comments

Mr Patience Over a year ago

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: dba.stackexchange.com/a/974/245938 Accepted solution is better.

VladL Over a year ago

@MrPatience for me it returns random rows which are randomly sorted

Mr Patience Over a year ago

Well, that wasn't my experience, but I had a complicated query with group by clause - maybe it was something there

Ali NajafZadeh · Accepted Answer · 2021-09-23 16:52:01Z

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

Collectives™ on Stack Overflow

EF Code First: How to get random rows

4 Answers 4

7 Comments

Skip(random number of rows)

Method

Generated SQL

Guid

Method

Generated SQL

3 Comments

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Skip(random number of rows)

Method

Generated SQL

Guid

Method

Generated SQL

3 Comments

3 Comments

Comments

Linked

Related