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..

Answer 1

Maybe you want something like this, you do a group by then select how many you want from each category.

Edited: As pointed out by Enigmativity in the comments, Guid.NewGuid() shouldn't be used to for randomness only for uniqueness. To produce randomness you should consult this StackOverflow post.

Demo

using System;
using System.Linq;
using System.Collections.Generic;

public class Program
{
    private static int[] categoryIds = new int[] {1, 2, 3, 4, 5};
    private static int[] questionsPerCategory = {3, 1, 6, 11, 7};
    //Part of demo
    private static IEnumerable<QuestionVM> Questions = Enumerable.Range(0,100).Select(x=> new QuestionVM { Question = $"Question - {x}", CategoryId = (x % 5) + 1});


    public static void Main()
    {
        var questions = Questions.Where(x=> x.InTest).GroupBy(x=> x.CategoryId).SelectMany(x=> x.OrderBy(y=> Guid.NewGuid()).Take(GetQuestionTake(x.Key)));
        foreach(var question in questions)
            Console.WriteLine($"{question.Question} - CategoryId: {question.CategoryId}");  
    }

    ///Finds out how many questions it should take by doing a search and then picking the element in the same position
    private static int GetQuestionTake(int categoryId)
    {
        int element =  categoryIds.Select((x, i) => new { i, x }).FirstOrDefault(x => x.x == categoryId).i;
        return questionsPerCategory.ElementAtOrDefault(element);
    }
}


//Part of demo
public class QuestionVM
{
    public string Question {get;set;}
    public int CategoryId {get;set;}    
    public bool InTest {get;set;} = true;
}

Answer 2

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.

Answer 3

I think you are looking for Take() method. You should also pass parameters to the method with category id and how many questions you want to receive. Pass those parameters from your arrays.

private IQuerable<Question> Method(int Id, int questionsCount)
{    
return Questions.Where(c=>c.CategoriesId==Id && c.InTest==1).Take(questionsCount).OrderBy(c=>c.NewId);
}

Answer 4

A common way is to order by Guid.NewGuid(), so to extend Crekate's answer above.

.OrderBy(c=>Guid.NewGuid());