Select random questions with where clause in linq

Question

I need to select random questions per category

private int[] categoryId = {1, 2, 3, 4, 5, ...};

private int[] questionsPerCategory = {3, 1, 6, 11, 7, ...};

Before linq i achieved it by using

SELECT TOP (@questionsPerCategory) * From Questions WHERE CategoriesID = @categoryId  AND 

InTest ='1' ORDER BY NEWID()

Which also was not correct, since i had to call this for every categoryId.

How can i have the desired results with linq in a single query?
All i need is fetch

3 random questions, with categoryId = 1 and InTest = true,

1 random question, with categoryId = 2 and InTest = true,

6 random questions, with categoryId = 3 and InTest = true

and so on..

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

Since Guid.NewGuid is not supported by LINQ to SQL, first you need to get access to NEWID function by using the trick from the accepted answer to Random row from Linq to Sql by adding the following to your context class:

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

Then the query for single CategoryID and question count would be:

var query = db.Questions
    .Where(e => e.CategoriesID == categoryId[i] && e.InTest)
    .OrderBy(e => db.Random())
    .Take(questionsPerCategory[i])

To get the desired result for all category / question count pairs, you could build a UNION ALL SQL query by using Concat of the above single query for i = 0..N like this:

var query = categoryId.Zip(questionsPerCategory,
    (catId, questions) => db.Questions
        .Where(q => q.CategoriesID == catId && q.InTest)
        .OrderBy(q => db.Random())
        .Take(questions)
    ).Aggregate(Queryable.Concat)
    .ToList();

This should produce the desired result with single SQL query. Of course it's applicable if the count of the categoryId is relative small.

Select random questions with where clause in linq

Sign up