1

i have a query which will result 10 or 20 or any number number of rows. Below is the query.

   var q = (from c in session.DB.Question where c.Level='1' && c.Group='1' select c);

This query can give me any number of rows.

But i have to show just 1 row from the result. I can select top 1/first but i would like select randomly.

i saw a topic about this: How to request a random row in SQL? but i want it in LinQ

Please help me how to get random row from the result.

Community
  • 1
  • 1
Ali
  • 98
  • 2
  • 14
  • 3
    Technically, `First()` without a sort will get you an 'random' row ;) – Rob Feb 11 '16 at 03:28
  • Possible duplicate of [EF Code First: How to get random rows](http://stackoverflow.com/questions/7781893/ef-code-first-how-to-get-random-rows) – Rob Feb 11 '16 at 03:28
  • Do you have any identity keys from your questionnaires? – Chris Feb 11 '16 at 03:40

4 Answers4

3

Sort items by random value and select first:

var q = (from c in session.DB.Question 
         where c.Level =='1' && c.Group =='1' 
         select c)
        .OrderBy(o => SqlFunctions.Rand())
        .First();
Backs
  • 24,430
  • 5
  • 58
  • 85
1

Thanks all. i found it and working:

        var q = (from c in Session.DB.WinQuestionSet
                 where c.Level == "easy" && c.Grade == "1"
                 select c).OrderBy(x => Guid.NewGuid()).Take(1).Single();
        label1.Text = q.Text;
Ali
  • 98
  • 2
  • 14
0

I assume that your questions has an Id. Please see example below. I used Random class to generate random number.

        List<Question> questions = new List<Question>
        {
            new Question { Id = 10, Name = "What?" },
            new Question { Id = 12, Name = "How?" },
            new Question { Id = 32, Name = "When?" },
            new Question { Id = 41, Name = "Where?" },
        };
        var q = (from c in questions select c);

        int i = 1;
        Dictionary<int, int> questionKeys = new Dictionary<int, int>();

        foreach (var item in questions)
        {
            questionKeys.Add(i, item.Id);
            i++;
        }

        Random rdm = new Random();
        int randomRow = rdm.Next(1, q.Count());

        var questionId = questionKeys.Where(x => x.Key == randomRow).Select(x => x.Value).Single();

        var result = q.Where(x => x.Id == questionId).Single();

        Console.WriteLine(result.Name);
Chris
  • 599
  • 7
  • 23
  • tnx, But maybe the numbers are not in sequence, so int randomRow = rdm.Next(1, q.Count()); may not contains ID in result – Ali Feb 11 '16 at 04:38
  • what if Id is not sequence from 1 to `Count`? – Backs Feb 11 '16 at 04:38
  • @Backs yes,exactly – Ali Feb 11 '16 at 05:07
  • Put your collection or the not sequence id to a dictionary and populate the key as sequence then use the random number – Chris Feb 11 '16 at 05:47
  • as you can see, author need a LinqToSQL solution. so, using dictionaries can be a bad one, when you suddenly get 1mln rows instead of 10-20 – Backs Feb 11 '16 at 07:25
  • yes. 1 million questions is very bad :). my solution only can accommodate only few hundreds. Well done mate. – Chris Feb 11 '16 at 08:22
-1

Assuming data is your data rows:

Random rand = new Random();
var row = data.ElementAt(rand.Next(data.Count));

Note that this does not work for Linq to SQL, and thus should be used after you query your db.

Alex R.
  • 185
  • 1
  • 7
  • LinqToSql does not support `ElementAt` http://stackoverflow.com/questions/5147767/why-is-the-query-operator-elementat-is-not-supported-in-linq-to-sql – Backs Feb 11 '16 at 04:40
  • I did not consider that, good catch. I will update my answer. – Alex R. Feb 11 '16 at 05:07