Return random object from DB (LINQ query)

Question

I have following query:

Banner banner = Database.Banners.Where(b => b.IsPublish.Value && 
         b.Category.Value == (int) CategoryBanner.Banner &&
         b.PeriodShowCountAlready < b.PeriodShowCount ||
         b.ShowNext < DateTime.Now ).Take(1).FirstOrDefault();

I retrieve only one record. Suppose that in the DB 3 records satisfy this query and LINQ will return always same record. I want that returned random record, how can I do this?

http://stackoverflow.com/questions/648196/random-row-from-linq-to-sql — Tim Schmelter, Jan 06 '12 at 08:46
In `.Take(1).FirstOrDefault()` the `.Take(1)` portion is entirely redundant. — spender, Jan 06 '12 at 08:54

Gleno · Accepted Answer · 2012-01-06T09:17:07.430

Depending on your database, this might work:

var banners = Database.Banners.Where(b => b.IsPublish.Value && 
         b.Category.Value == (int) CategoryBanner.Banner &&
         b.PeriodShowCountAlready < b.PeriodShowCount ||
         b.ShowNext < DateTime.Now );

Then just skip a random number of banners...

var skip = new Random().Next(banners.Count() - 1);
var banner = banners.Skip(skip).FirstOrDefault();

As Amar points out, this is approach is less than optimal since it causes two trips to the databank. From my own experience profiling, the round-trip to another server most often costs much-much more than executing a simple SQL statement.

So, what options are there?

Fetch the entire set, and select a random banner locally. This is not optimal because the set might be very large. If you know the set to be small (less than 1000 records without binary fields [like the banner image], or less than 10k all in all should be negligible)
Ask the database how many object there are first, and then ask the database for the n:th object. This is bad because it causes two trips.
Make the database do everything, which entrails leaving the comfort of your ORM and writing a stored procedure manually. To that end you utilize newid() [on sql server] as order by parameter and everything is fast and good, but you have to do this on the server and not in your C# app.

This is interesting. Good one :) – Amar Palsapure Jan 06 '12 at 09:19 — Amar Palsapure, Jan 06 '12 at 09:19

score 2 · Answer 2 · edited May 23 '17 at 12:31

Update: The (far better) answer in the duplicated question provides an example with Skip. The benefit there is the empty list handling. Plus, this answer discusses issues with large result sets which won't be handled well in my solution...

You could return more than one from the db and select a random entry from the result.

List<Banner> list = Database.Banners.Where(b => b.IsPublish.Value && 
         b.Category.Value == (int) CategoryBanner.Banner &&
         b.PeriodShowCountAlready < b.PeriodShowCount ||
         b.ShowNext < DateTime.Now ).Take(10).ToList();

Random r = new Random();
Banner banner = list.Count == 0 ? null : list[r.Next(0, list.Count)];

But supposing that instead of taking a random row from the first 10 results, OP wants one from a set of 1 million results? That's pulling quite a lot of unnecessary data. — spender, Jan 06 '12 at 08:56

Amar Palsapure · Answer 3 · 2012-01-06T08:54:57.987

0

Get all the records and do random on them

Try doing this:

 var records = Database.Banners.Where(b => b.IsPublish.Value && 
 b.Category.Value == (int) CategoryBanner.Banner &&
 b.PeriodShowCountAlready < b.PeriodShowCount ||
 b.ShowNext < DateTime.Now ).ToList();
 var random = new Random();
 var count = random.Next(records.Count - 1);
 Banner banner = records[count];

edited Jan 06 '12 at 08:54

answered Jan 06 '12 at 08:48

Amar Palsapure

9,590
1
27
46

"the range of return values ordinarily includes zero but not maxValue",so using `random.Next(records.Count - 1)` will never create an index for the last item. Also, does `IQueryable` support index operators? I don't think so. – spender Jan 06 '12 at 08:53
There's actually nothing wrong with this example, save for the -1 and the fact that you may not want to fetch all the banners if you only need one. @spender - note that the records were ToList()-ed. – Gleno Jan 06 '12 at 08:57
Forgot to do ToList on the result. IQueryable supports Count() though – Amar Palsapure Jan 06 '12 at 08:58
@Gleno I forgot to put the ToList earlier, edited my post. My bad. – Amar Palsapure Jan 06 '12 at 08:58
@Gleno: `ToList` wasn't present at the time of the comment. Even with `ToList`, in the case of a large result set, this would be somewhat sub-optimal. – spender Jan 06 '12 at 09:00
@spender, Absolutely. That's why you should Skip(), or use newid() on the server for fastest possible randoms. – Gleno Jan 06 '12 at 09:02
@Gleno, in your approach, the result must be store in some local variable, coz when Count() and Skip() will cause two calls to DB. Any thoughts. – Amar Palsapure Jan 06 '12 at 09:08

Return random object from DB (LINQ query)

3 Answers3