EF Code First: How to get random rows
EF Code First: How to get random rows
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?
possible duplicate of Linq to Entities, random order
– Frédéric
Jul 6 '15 at 14:02
2 Answers
2
Just call:
something.OrderBy(r => Guid.NewGuid()).Take(5)
hi it works fine, but will this be fast when table has more rows, i posted question here
– stom
Jun 10 '15 at 14:11
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.– Frédéric
Jul 6 '15 at 9:51
OrderBy
Include
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.
– Jon Schneider
May 4 '16 at 13:37
strangely it doesn't work with Oracle
– Toolkit
Apr 20 '17 at 16:28
@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
Guid.NewGuid()
NEWID()
Comparing two options:
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();
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;
private T getRandomEntityInPlace<T>(IGenericRepository<T> repo)
return repo.Items.OrderBy(o => Guid.NewGuid()).First();
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
Thanks for the comparing, it really helps
– Haobo
Jul 7 '16 at 8: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
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
See stackoverflow.com/questions/648196/random-row-from-linq-to-sql/…
– Ian Mercer
Oct 16 '11 at 2:23