Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 535
Mel Avatar asked Oct 16 '11 02:10

Mel


People also ask

How do I select random rows?

To get a single row randomly, we can use the LIMIT Clause and set to only one row. ORDER BY clause in the query is used to order the row(s) randomly. It is exactly the same as MYSQL. Just replace RAND( ) with RANDOM( ).

How does SQL determine EF generated?

For use this, you're going to want to set a breakpoint below your EF Core query. Breakpoint hit after the query. Once the breakpoint is hit hover over query to expand then click on > Debug View > The dropdown beside the magnifying glass> Text Visualizer. There you have it, the SQL that will be sent to the database.

How do I get the last record of EF model data?

Answer: If you want to get the most recent record from the database using EF Core or Entity Framework then you must order the result then get FirstOrDefault() record from the dataset returned. See the code below: var MyRecentRecord = _context. MyDataModalOrPOCO.


2 Answers

Just call:

something.OrderBy(r => Guid.NewGuid()).Take(5) 
like image 128
SLaks Avatar answered Sep 20 '22 10:09

SLaks


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 
like image 36
drzaus Avatar answered Sep 22 '22 10:09

drzaus