ORDER BY NEWID in LINQ and bind to Repeater control

Question

Morning,

I would like to know how to write the following SQL statement in LINQ.

 SELECT TOP 6 * FROM Questions
 ORDER BY NEWID()

I would also like to know, how i could bind this to a asp.net repeater control to display the 6 questions.

Many thanks :)

Did you want to be able to do this at the database level? The answers you've gotten would not work with LINQ to SQL since the orderby clauses would be ignored. — Jeff Mercado, Jul 23 '12 at 08:26
I have just made a view, which does the above, and called that in LINQ, which seems to work. Next up i need to get the corresponding answers. — thatuxguy, Jul 23 '12 at 08:34
Is your goal to select 6 random questions (as it appears to be)? — just.another.programmer, Jul 23 '12 at 10:03
For efficiency reasons, you may want to consider changing to the random sort recommended by @Ian Mercer linked in my answer. — just.another.programmer, Jul 23 '12 at 10:47

score 4 · Accepted Answer · answered Jul 23 '12 at 08:12

4

The Linq style would be

 Questions.OrderBy(q=>Guid.NewGuid()).Take(6)

then you attach that to a repeater by setting its DataSource property to the above, and calling the DataBind method.

answered Jul 23 '12 at 08:12

podiluska

50,950
7
98
104

cool, thanks, but it only seems to take the first 6 questions – thatuxguy Jul 23 '12 at 08:15
1

Try adding .ToList() between Questions and OrderBy...? – podiluska Jul 23 '12 at 08:22
Sorry, I misread the code when I made that comment. For a moment I thought the `Take()` call came first. But having to call `ToList()` just to make it work isn't much better as it's now a LINQ to objects query. – Jeff Mercado Jul 23 '12 at 09:04
This will cause a very inefficient sort because it has to generate a GUID for every record. On very large data sets that could be devestating. Also, a call to ToList will cache the data as a list *before* it applies the Take operator, pulling your entire DB into memory just throw it away! – just.another.programmer Jul 23 '12 at 10:00
1

@just.another.programmer .... which is exactly what the original SQL would do. The question is not "Is this the best way" or "Is LINQ the best solution" – podiluska Jul 23 '12 at 10:05
1

@podiluska We always try to address the *intention* of the OP in addition to how he has considered doing it. See http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem on meta about this kind of problem. Otherwise you propogate bad advice on SO and lower the quality of the site! – just.another.programmer Jul 23 '12 at 10:10

score 4 · Answer 2 · edited May 23 '17 at 11:52

You would have to be able to invoke the NEWID() function to generate your random guids. To do so, you could take some hints here and first create a pseudo-method mapped to the NEWID() function on your data context.

[System.Data.Linq.Mapping.Function(Name="NEWID", IsComposable=true)] 
public Guid NewId() 
{
    throw new NotImplementedException(); 
}

Once that is set, you could then write your query to use this function:

var query = dc.Questions
    .OrderBy(question => dc.NewId())
    .Take(6);

You can inspect the SQL query generated for this and it should match.

score 1 · Answer 3 · answered Sep 18 '21 at 07:37

1

Questions.OrderBy(q=>Sql.NewGuid()).Take(6)

This will invoke the NEWID() in SQL statement.

answered Sep 18 '21 at 07:37

Thomas Hess

11
3

score 0 · Answer 4 · answered Jul 23 '12 at 08:18

0

(from db in context.Questions
order by Guid.NewGuid()
select db).Take(6);

answered Jul 23 '12 at 08:18

Alex

87
1
3
8

1

Why the downvote? Seems like a reasonable answer to the question. – Enigmativity Jul 23 '12 at 08:31
1

i did not down vote any answers. Someone else is, not sure why tbh. – thatuxguy Jul 23 '12 at 08:32

score 0 · Answer 5 · answered Jun 24 '16 at 06:31

I know answer is already selected, but still I'm adding my way to achieve this. Faced same situation today and tried couple of ways, used questions.OrderBy(q => Guid.NewGuid()).ToList() and couple of more suggestions. Later I thought to add a new field string RandomOrder in view model and assigned Guid.NewGuid().ToString() in loop and then used questions.OrderBy(i => i.RandomOrder).ToList() and this worked great.

I had requirement to shuffle questions if author selected option shuffleAlways while creating assessment. If not then sort on regular sorting order. Here is complete solution:

    private List<AssessmentQuestionsViewModel> LoadAllQuestions(string assessmentId, bool shuffleQuestions)
    {
        List<AssessmentQuestionsViewModel> questions = new List<AssessmentQuestionsViewModel>();

        var items = assessmentQuestionRepository.GetAll().Where(i => i.AssessmentId == assessmentId).ToList();

        foreach (var item in items)
        {
            questions.Add(new AssessmentQuestionsViewModel
            {
                Id = item.Id,
                AssessmentId = item.AssessmentId,
                QuestionText = item.QuestionText,
                HintText = item.HintText,
                QuestionType = item.QuestionType,
                MaxMarks = item.MaxMarks,
                SortOrder = item.SortOrder,
                RandomOrder = Guid.NewGuid().ToString(),
                Answers = LoadAllAnswers(item.Id)
            });
        }

        if (shuffleQuestions)
        {
            questions = questions.OrderBy(i => i.RandomOrder).ToList();
        }
        else
        {
            questions = questions.OrderBy(i => i.SortOrder).ToList();
        }

        return questions;
    }

And this worked like charm. Hope this help others.

score -1 · Answer 6 · edited May 23 '17 at 11:58

-1

I assume you are using ORDER BY NEWID() as a way to select random data from your questions? If so, you should avoid using NEWID() (or it's LINQ equivalent), causes tons a new guid to be generated for every record in your table. On a large dataset, that's devestating.

Instead, see Linq Orderby random ThreadSafe for use in ASP.NET for an optimized solution to random sorts. Then just add a take operator and your set.

Random random = new Random();
int seed = random.Next();

var RandomQuestions = Questions.OrderBy( s => (~(s.Shuffle & seed)) & (s.Shuffle | seed)); // ^ seed); 

return RandomQuestions.Take(6);

edited May 23 '17 at 11:58

Community

1
1

answered Jul 23 '12 at 08:17

just.another.programmer

8,579
8
51
90

Did you make sure to add the Shuffle column to your database (as indicated in the linked answer)? What error are you getting. – just.another.programmer Jul 23 '12 at 19:29
int. See the linked answer from @Ian Mercer for more – just.another.programmer Jul 24 '12 at 11:20

ORDER BY NEWID in LINQ and bind to Repeater control

6 Answers6