Random

[shapper]

Hello,

I am getting a poll from my database has follows:

viewData.PollPa per = (from p in database.Polls
where p.IsPublished == true
select new PollPaper {
Poll = p,
Options = string.Join(", ", (from
o in database.Option s

join ps in database.Polls on o.PollID equals ps.PollID

orderby o.Answer

select o.Answer).ToArr ay())
}).SingleOrDefa ult();

I would like to pick a random poll from all the published polls. How
can I do this?

Thanks,
Miguel

[Marc Gravell]

Re: Random

No, I wouldn't do that - I would find the count, use Random to pick an
index at random, and select that row.

The NewID() was for reference only, in case you are also using an SP.

For a small table (such as 100 rows) performance is not a concern -
but on huge tables this would force it to sort by an non-indexed,
calculated column. Not horrendous, but unnecessary if we can just say
"pick the 2723th row" (sorting by the clustered index) via Skip() and
Take().

Marc

[Marc Gravell]

Re: Random

You've forced it to get everything (ToList()). I'm not quite sure what
is happening with the middle bit, so I'll stick to a simple example:

var query = from p in database.Polls
where p.IsPublished == true
orderby p.SomeKey
select p;

int count = query.Count();
....
Poll poll = query.Skip(rand om.Next(count)) .FirstOrDefault ();

This does 2 round-trips (one for the count, one for the row-fetch).
but if there is any volume of data involved, it will be much quicker
overall.

You can use SQL traces (or just the context Log, as per my example) to
see what TSQL etc gets invoked.

Marc