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?
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.
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)];
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];
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.
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.
.Take(1).FirstOrDefault()the.Take(1)portion is entirely redundant.