Primer código EF: cómo obtener filas aleatorias

¿Cómo puedo crear una consulta en la que recuperaría filas aleatorias?

Si tuviera que escribirlo en SQL, pondría un orden por newid () y cortaría n número de filas desde la parte superior. De todos modos para hacer esto en el código EF primero?

He intentado crear una consulta que usa newid () y ejecutarla usando DbSet.SqlQuery (). mientras funciona, no es la solución más limpia.

Además, intenté recuperar todas las filas y ordenarlas por un nuevo guid. Aunque el número de filas es bastante pequeño, todavía no es una buena solución.

¿Algunas ideas?

Solo llama:

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

Comparando dos opciones:


Omitir (número aleatorio de filas)

Método

 private T getRandomEntity(IGenericRepository repo) where T : EntityWithPk { var skip = (int)(rand.NextDouble() * repo.Items.Count()); return repo.Items.OrderBy(o => o.ID).Skip(skip).Take(1).First(); } 
  • Toma 2 consultas

SQL generado

 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

Método

 private T getRandomEntityInPlace(IGenericRepository repo) { return repo.Items.OrderBy(o => Guid.NewGuid()).First(); } 

SQL generado

 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