Random

[shapper]

Hello,

I am getting a poll from my database has follows:

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

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

orderby o.Answer

select o.Answer).ToArray())
}).SingleOrDefault();

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

Thanks,
Miguel

 

[Marc Gravell]

One option would be to use Count() to get the number of polls
available, then use randomIndex = random.Next(count) to get a random
number in this range (where random is an instance of Random), then
use .Skip(randomIndex).Take(1) to get this one from the polls.

for info, to get a random row inside TSQL you can order by NEWID(),
although this isn't very efficient. The "count, pick one at random,
select" approach is generally faster.

Marc

 

[shapper]

One option would be to use Count() to get the number of polls
available, then use randomIndex = random.Next(count) to get a random
number in this range (where random is an instance of Random), then
use .Skip(randomIndex).Take(1) to get this one from the polls.

for info, to get a random row inside TSQL you can order by NEWID(),
although this isn't very efficient. The "count, pick one at random,
select" approach is generally faster.

Marc

Yes, I knew about order by NewID() in TSQL.

You mean that in Linq I would to:
orderby Guid.NewGuid()

And why is this not efficient?

I will have at most 100 polls on my table.

Thanks,
Miguel

 

[Marc Gravell]

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]

For info, Guid.NewGuid() is treated as a constant by LINQ. If you want
to use the NEWID approach with LINQ-to-SQL, declare a composable
function on your data context:

public partial class NorthwindDataContext
{
[Function(Name="NEWID", IsComposable=true)]
public Guid Random()
{ // to prove not used by our C# code...
throw new NotImplementedException();
}
}

And then use this in your query:

using (var ctx = new NorthwindDataContext())
{
ctx.Log = Console.Out;
var cust = ctx.Customers
.OrderBy(x => ctx.Random())
.FirstOrDefault();
Console.WriteLine(cust);
}

This results in the TSQL:

SELECT TOP (1) // snip columns
FROM [dbo].[Customers] AS [t0]
ORDER BY NEWID()

Marc

 

[shapper]

For info, Guid.NewGuid() is treated as a constant by LINQ. If you want
to use the NEWID approach with LINQ-to-SQL, declare a composable
function on your data context:

    public partial class NorthwindDataContext
    {
        [Function(Name="NEWID", IsComposable=true)]
        public Guid Random()
        { // to prove not used by our C# code...
            throw new NotImplementedException();
        }
    }

And then use this in your query:

        using (var ctx = new NorthwindDataContext())
        {
            ctx.Log = Console.Out;
            var cust = ctx.Customers
                .OrderBy(x => ctx.Random())
                .FirstOrDefault();
            Console.WriteLine(cust);
        }

This results in the TSQL:

    SELECT TOP (1) // snip columns
    FROM [dbo].[Customers] AS [t0]
    ORDER BY NEWID()

Marc

Ok, I am following your advice. I think what you meant was something
as follows:

List<PollPaper> papers = (from p in database.Polls
where p.IsPublished == true
select new PollPaper {
Poll = p,
Options = string.Join(", ", (from o
in database.Options
join ps in
database.Polls on o.PollID equals ps.PollID
orderby
o.Answer
select
o.Answer).ToArray())
}).ToList();
Random random = new Random();
viewData.PollPaper =
papers.Skip(random.Next(papers.Count())).Take(1).SingleOrDefault();

This was as short as I got and following your tips.

 

[Marc Gravell]

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(random.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