CODEWITHSUNDEEP
c#linqlinq-to-sql
OrElse
OrElse

Reputation: 9969

Select random questions with where clause in linq

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

and so on..

Upvotes: 2

Views: 488

Answers (4)

Shoejep
Shoejep

Reputation: 4849

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

Upvotes: 3

Ivan Stoev
Ivan Stoev

Reputation: 205729

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.

Upvotes: 3

Daniel Forslund
Daniel Forslund

Reputation: 241

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

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

Upvotes: 0

Crekate
Crekate

Reputation: 145

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

Upvotes: 1

Related Questions