I need to select random questions per category
private int[] categoryId = {1, 2, 3, 4, 5, ...};
private int[] questionsPerCategory = {3, 1, 6, 11, 7, ...};
SELECT TOP (@questionsPerCategory) * From Questions WHERE CategoriesID = @categoryId AND
InTest ='1' ORDER BY NEWID()
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.