why is my query not returning 6 items?

Question

Afternoon,

Can any one see why my query is not returning a random 6 items please?

 public class GetQuestions
 {
   public int qId { get; set; }
   public string question { get; set; }
   public string answer1 { get; set; }
   public string answer2 { get; set; }
   public string answer3 { get; set; }
}

  [WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public List<GetQuestions> Questions()
    {
        using (QuizDataContext dc = new QuizDataContext())
        {
            var query = from q in dc.tblquizs
                        orderby Guid.NewGuid()
                        select new GetQuestions
                        {
                            qId = q.id,
                            question = q.q,
                            answer1 = q.a1,
                            answer2 = q.a2,
                            answer3 = q.a3,
                        };
            return query.Take(6).ToList();
        }

Updated Add the GetQuestions Class

only returns the top 6, out of 30. it does not select random ones like i thought it would. — thatuxguy, Jul 23 '12 at 13:54
There's not really enough information here for someone to (confidently) answer your question. Are you sure there are at least 6 items in `dc.tblquizs`? Are you getting 6 and they are just not random? — Josh Darnell, Jul 23 '12 at 13:54
they are just not random, and there are 30 questions in the DB. — thatuxguy, Jul 23 '12 at 13:55
similar: http://stackoverflow.com/questions/654906/linq-to-entities-random-order — Aristos, Jul 23 '12 at 13:57

score 0 · Answer 1 · answered Jul 23 '12 at 13:57

0

You can't get random order by using

orderby Guid.NewGuid()

You can test this by doing the following query and seeing the result:

from q in dc.tblquizs
select Guid.NewGuid()

answered Jul 23 '12 at 13:57

Aducci

26,101
8
63
67

I managed to get it working earlier. using `(from db in context.Questions order by Guid.NewGuid() select db).Take(6);` and this worked fine, until i needed to get it in a list using the above code. – thatuxguy Jul 23 '12 at 13:59
@thatuxguy - when I run it, I get the exact same GUID repeated – Aducci Jul 23 '12 at 14:03
how could i get 6 random questions to a list then? I mean it was working ok when i had it just going to a string to test it out. – thatuxguy Jul 23 '12 at 14:05
That's probably because Guid.NewGuid() gets evaluated before the query is translated to SQL - it doesn't get translated to newid(), which is probably what you were hoping would happen. – Mark Stafford - MSFT Jul 23 '12 at 15:10
Edit: Entity Framework will translate the Guid.NewGuid() call to newid() - this would definitely be the preferred methodology if your DAL supports it. – Mark Stafford - MSFT Jul 23 '12 at 15:43

Mark Stafford - MSFT · Answer 2 · 2012-07-23T15:47:14.390

Entity Framework 4.3.1 will translate the Guid.NewGuid() call to newid() - this would definitely be the preferred methodology if your DAL supports it. It may be, however, that whatever DAL you're using doesn't translate the call properly (in which case it may get translated to a GUID before it's sent to the database server, resulting in a static value for ordering). You should use a database profiler to see what your DAL is doing.

If the Guid.NewGuid call is not translating to newid() properly, you have two other options:

Use a sproc

Use something like the LINQ below (as a last resort)

    var context = new ScratchContext();
    var products = new List<Product>();
    for (int i = 0; i < num; i++)
    {
        Product product = null;
        while (product == null)
        {
            int randomId = r.Next(context.Products.Count());
            product = context.Products.FirstOrDefault(p => p.ID == randomId);
        }
        products.Add(product);
    }
    return products.AsQueryable();

score 0 · Accepted Answer · answered Jul 25 '12 at 08:13

I used the following code to resolve this issue.

 var qry = from q in dc.tblwhiskysprintquizs.AsEnumerable()
                      orderby Guid.NewGuid()
                      select new GetQuestions
                        {
                            qId = q.id,
                            question = q.q,
                            answer1 = q.a1,
                            answer2 = q.a2,
                            answer3 = q.a3,
                        };
            return qry.Take(6).ToList();

It was as simple as adding .AsEnumerable to the look up.

score -1 · Answer 4 · answered Jul 23 '12 at 14:00

-1

orderby Guid.NewGuid()

generates random numbers that may not exist in your db

answered Jul 23 '12 at 14:00

Mahdi Tahsildari

13,065
14
55
94

1

Explain why this is a problem. The SQL function `NEWID()` does the same thing, and is what the OP wants to emulate. – J. Steen Jul 23 '12 at 14:02
I thought it doesn't work when you sort records base on a number that does not exist – Mahdi Tahsildari Jul 23 '12 at 14:04
That's the very reason -why- it works - you're ordering by random, non-related ids. =) Well, not in the `Guid.NewGuid()` case, but with `NEWID()`. – J. Steen Jul 23 '12 at 14:07
This doesn't, however, seem to be the issue. The `new GetQuestions` seems to be disturbing the SQL query generation somewhat. – J. Steen Jul 23 '12 at 14:10

why is my query not returning 6 items?

4 Answers4