Order By missing in linqtosql select random row

Question

Hi,

I have the following linq query :

IQueryable<Message> messagesQuery = (from message in _context.Db.Messages
                                where message.MessageListId == item.MessageListId
                                         &&
                                         !_context.Db.ScheduleXMessages.Any(x => x.MessageId == message.MessageId && x.ScheduleId == item.ScheduleId)
                                select message);
        if (randomSendMessage) 
            return (from mq in messagesQuery orderby Guid.NewGuid() select mq).FirstOrDefault();
        return (from mq in messagesQuery orderby mq.OrderIndex select mq).FirstOrDefault();

Now, if randomSendMessage is true, the order by doesn't get added to the select. if it's false, then the Order By OrderIndex is added to the select.

Any ideas on what might be going on ?

Thanks.

Edit Nope, it doesn't work if I select the column upfront. It just sends the guid as a parameter and instead of doing an orderby newid() , it does it by using one single guid

Answer 1

Hey,

Not exactly sure what is going on, but my guess would be it doesn't support that type of ordering... possibly because it's not defined within the select list. Try the alternative:

IQueryable<Message> messagesQuery = (from message in _context.Db.Messages 
                                where message.MessageListId == item.MessageListId 
                                         && 
                                         !_context.Db.ScheduleXMessages.Any(x => x.MessageId == message.MessageId && x.ScheduleId == item.ScheduleId) 
                                select new { Message = message, ID = Guid.NewGuid() }); 
        if (randomSendMessage)  
            return (from mq in messagesQuery orderby mq.ID select mq.Message).FirstOrDefault(); 
        return (from mq in messagesQuery orderby mq.Message.OrderIndex select mq.Message).FirstOrDefault(); 

to see if that resolved it by storing the ID within the record.

Answer 2

I guess it should have bee bloody obvious that L2S wouldn't know what to do with Guid.NewGuid() :).

I found an answer here : http://weblogs.asp.net/fmarguerie/archive/2008/01/10/randomizing-linq-to-sql-queries.aspx

It works.

Answer 3

If you want to get random results with LINQ to SQL you can use Marc Gravell's solution here. I recently explained how to setup a partial class to use it in this post. Otherwise you may have to setup a stored procedure or UDF.

EDIT: I took a look at the blog post you mentioned. The author actually pointed to Marc's solution as an alternative :)