Retrive Random Record/Row from DataBase Table Using LINQ 2 SQL?

Question

0.00/5 (No votes)

See more:

hi,
I have a "Quote of the Day Label" on my Default.aspx Page My requirement is that I want a random quote to be selected from the database table "Quotes" and assign the text to the "Quotelabel", I am Using Linq 2 Sql??

here's my GetAll() Linq 2 Sql query

XML

public static List<Quotes> GetAll()
{
    List<Quotes> list = new List<Quotes>();
    var query = from c in new HsInternetDBDataContext().Quotes
                orderby c.DateCreated ascending
                select c;
    foreach (DAL.Quote dr in query.ToList())
    {
        l.Add(new Quotes(dr));
    }
    return list;
}

how do i modify the above query and get a single Random record/Quote from database??
or use this GetAll() method and select a single random record on the client side to assign to my QuoteLabel??

Thank you,
Chetan.

Posted 28-Jul-11 5:52am

cnjadhav

Add a Solution

3 solutions

Solution 2

http://www.dailycoding.com/Posts/random_sort_a_list_using_linq.aspx[^] explains how to random sort a list. Once you do that, you can select the top value.

Posted 28-Jul-11 15:07pm

Christian Graus

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

dasblinkenlight · Accepted Answer · 2011-07-28T15:05:00

Reading all quotes and dropping all but one seems wasteful. I would use Linq2Sql capabilities to retrieve a single item, like this:

First, I would query the number of quotes using Count(). Then, I would generate a random integer R between 0 and Count()-1, inclusive. Finally, I would use Link2Sql to get R-th quote, like this:

C#

var quote = new HsInternetDBDataContext()
    .Quotes
    .OrderBy(q => q.DateCreated)
    .Skip(R)
    .Take(1)
    .Single();

Good luck!

cnjadhav · Accepted Answer · 2011-07-29T03:09:00

Solution 3

Thanks guys, got it working here's my solution:

C#

public static Quotes GetRandomQuotes()
{
    HsInternetDBDataContext db = new HsInternetDBDataContext();
    
    var query = from c in db.Quotes select c;
    int count = query.Count();
    if (query.Count() > 0)
    {
        Random r = new Random();
        return new Quotes(query.ToList()[r.Next(0, count)]);
    }
    else
    {
        return new Quotes();
    }
}

Posted 29-Jul-11 3:09am

cnjadhav

Updated 29-Jul-11 3:12am

v2

Comments

dasblinkenlight 29-Jul-11 12:29pm

Your solution may get rather slow once the database accumulates 10,000+ quotes, or when you access RDBMS over a slow connection.
This is because ToList() loads all quotes into memory. If you replace the return line with this one
return new Quotes(query.Skip(r.Next(0, count)).Take(1).Single());
the selection process would happen in the database, and the whole process would go at roughly the same speed, no matter how many quotes there are in your database.