Order By missing in linqtosql select random row

Question

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

Community · Accepted Answer · 2017-05-23 12:18:39Z

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 :)

nice :) - I didn't actually read the last line in the blog. Thanks.

Brian Mains · Accepted Answer · 2010-03-03 19:31:06Z

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.

Community · Accepted Answer · 2020-06-20 09:12:55Z

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.

Collectives™ on Stack Overflow

Order By missing in linqtosql select random row

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related