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?

Just call:

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

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

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