Ef Code First: How to Get Random Rows

EF Code First: How to get random rows

Just call:

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

Picking random record from Entity Framework database without OrderBy

You can have something like :

personToCall = db.Persons.OrderBy(r => Guid.NewGuid()).Skip(toSkip).Take(1).First();

You should use FirstOrDefault to be mode defensive.

Here the dark lord teaching the force to yoda! what is the world coming to!

Get random number of rows from SQL Server table

If you want to convert Method 2 into Linq To Entities just use the solution answered by jitender which look like this:

var randomCoustmers = context.Customers.OrderBy(x => Guid.NewGuid()).Take(5);

But for Method 1 which is very fast following your benchmarking, you need to do the following C# code because Linq To Entities doesn't have a LINQ equivalent for this SQL statement TABLESAMPLE(1000 rows).

var randomCoustmers = context.Customers.SqlQuery("Select TOP 5 customer_id, customer_name from Customer TABLESAMPLE(1000 rows) order by newid()").ToList();

You can move the SQL statements into a SQL View or Stored Procedure which will receive the number of customers to take.

UPDATE

For Method 4 which seems to be very fast (always by following your benchmark), you can do the following Linq To Entities:

var randomCoustmers = context.Customers.OrderBy(c => SqlFunctions.Checksum(Guid.NewGuid()).Take(5);

Entity Framework can translate into SQL all functions that are defined into SqlFunctions class. In those functions we have Checksum function which will do what you want.

If you want to join with other tables you can do it without difficulty with Linq To Entites so I just simplified my version by querying only the Customers DbSets.

Linq to Entities, random order

The simple solution would be creating an array (or a List<T>) and than randomize its indexes.

EDIT:

static IEnumerable<T> Randomize<T>(this IEnumerable<T> source) {
  var array = source.ToArray();
  // randomize indexes (several approaches are possible)
  return array;
}

EDIT: Personally, I find the answer of Jon Skeet is more elegant:

var results = from ... in ... where ... orderby Guid.NewGuid() select ...

And sure, you can take a random number generator instead of Guid.NewGuid().

How to take random rows from database, so that they are not duplicated, throughout multiple calls

If you have to execute the query more than once, you can't order by newid() because it's random and you won't have the same result for the next execution.

So you need to have a pseudo random value. Hash is a kind of pseudo random value. Let's order by hash value.

DECLARE @randomKey nvarchar(max) = 'randomValue';
SELECT *,
       HASHBYTES('SHA1', @randomKey + cast(rowId as nvarchar(max))) as [Order]
FROM dbo.Table
ORDER BY [Order] DESC
OFFSET  5 ROWS 
FETCH NEXT 5 ROWS ONLY 

Using this code the result will randomly be ordered by @randomKey.

In order to let Entity Framework generate the HashBytes call for you, without writing T-SQL. You can have a look at this project : Store Functions for Entity Framework Code First

How do I get a random number from numbers available?

How about something like this?

// get the count
var count = context.Fruits.Count();
// generate a random fruit index
var random = new Random().Next(count);
// skip by random
var randomFruit = context.Fruits
                         .Skip(random)
                         .Take(1)
                         .FirstOrDefault();

---

Related Topics