CODEWITHSUNDEEP
asp.netlinq-to-sqlrepeater
thatuxguy
thatuxguy

Reputation: 2528

ORDER BY NEWID in LINQ and bind to Repeater control

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 :)

Upvotes: 3

Views: 6132

Answers (6)

Thomas Hess
Thomas Hess

Reputation: 11

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

This will invoke the NEWID() in SQL statement.

Upvotes: 1

Abhimanyu
Abhimanyu

Reputation: 2213

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.

Upvotes: 0

just.another.programmer
just.another.programmer

Reputation: 8815

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);

Upvotes: -1

Jeff Mercado
Jeff Mercado

Reputation: 134571

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.

Upvotes: 4

Alex
Alex

Reputation: 87

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

Upvotes: 0

podiluska
podiluska

Reputation: 51504

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.

Upvotes: 4

Related Questions