linq: order by random

Question

How can I change below code, to each time get 50 different random data from database?

return (from examQ in idb.Exam_Question_Int_Tbl
      where examQ.Exam_Tbl_ID==exam_id
      select examQ).OrderBy(x=>x.Exam_Tbl_ID).Take(50);

score 125 · Accepted Answer · edited May 23 '17 at 10:31

125

http://msdn.microsoft.com/en-us/library/system.guid.newguid.aspx

return (from examQ in idb.Exam_Question_Int_Tbl
      where examQ.Exam_Tbl_ID==exam_id
      select examQ).OrderBy(x => Guid.NewGuid()).Take(50);

If this is LINQ-to-SQL you could simply add a ORDER BY NEWID() to your SELECT statement.

As commented it might be better to use an algorithm like Fisher-Yates Shuffle, here is an implementation: https://stackoverflow.com/a/375446/284240

edited May 23 '17 at 10:31

Community

1
1

answered Feb 26 '12 at 00:36

Tim Schmelter

450,073
74
686
939

I don't think this will work with a database anyway (no translation to SQL) and it ends up being O(nlogn) at best. In any event your comparator needs to provide a total ordering (it doesn't). You could fix it by selecting a new GUID (once) for each element, then sorting by that, but Fisher-Yates Shuffle ( Durstenfeldt's version) is a better choice if you're doing an in memory random selection. – tvanfosson Feb 26 '12 at 00:56
10

-1 http://blogs.msdn.com/b/ericlippert/archive/2011/01/20/spot-the-defect-bad-comparisons-part-one.aspx. in time t: A>B is true because of your `x => Guid.NewGuid()`. in time t+n: A Guid.NewGuid()`. Sort functions are not designed to shuffle things and it could result in an infinite loop in worst case. – L.B Feb 26 '12 at 00:56
1

To expand on my suggestion, `.Select( x => new { Guid = Guid.NewGuid, Question = x } ).OrderBy( x => x.Guid ).Select( x => x.Question ).Take(50);` would work for an in memory selection BUT you have to sort it which is on average O(nlogn) for the best algorithms. Durstenfeldt's algorithm works by taking a collection and swapping each element in turn with a random element in the collection that you haven't iterated over yet. This provides a random ordering but only requires O(n) operations. – tvanfosson Feb 26 '12 at 01:07
tanx both methods work for me, sorry to not able to vote as do not have enough reputation – user972087 Feb 28 '12 at 19:00
3

Remember `ORDER BY NEWID()` will be awfully slow on tables with lots of rows. If you think about how it works, it generates a random number for each row, and then it sorts them. Sorting na entire table is *slow*. If that is the case, use another strategy such as http://msdn.microsoft.com/en-us/library/cc441928.aspx (remember to not use `TOP`, use `ORDER BY NEWID()` on the resulting lines first), or use the `TABLESAMPLE` clause, available in SQL Server >=2005. This clause makes it return random pages of data, so only a portion of the data is ever touched. Very fast. – NothingsImpossible Dec 14 '13 at 09:04
Please mention that this will work only if you `.Take(n)` with `n>elements.Count ` – Bellash Mar 04 '14 at 14:47
@Bellash: why? [MSDN](http://msdn.microsoft.com/en-us/library/bb503062(v=vs.110).aspx): _"Take enumerates source and yields elements until count elements have been yielded or source contains no more elements. **If count exceeds the number of elements in source, all elements of source are returned**."_ – Tim Schmelter Mar 04 '14 at 14:50
I mean, when you Take 50 items from a set of 30 entries, you cannot order them randomly using your(msdn's) tip... I don't know why, but when I Take less than 30, for example 20, then I can order them at random. Do you understand what I mean? – Bellash Mar 04 '14 at 15:25
@Bellash: are you using LINQ-To-SQL? I haven't noticed that it makes a difference in sql. http://dba.stackexchange.com/questions/955/what-is-the-best-way-to-get-a-random-ordering I guess that your actual query is more complex, are you using sub-queries? Do you have any documentation acc. to this issue? – Tim Schmelter Mar 04 '14 at 15:33
Was only testing and saw that it works when the set' elements are less than the number of selected elements. here is my LINQ to SQL `var medias = (from media in db.MediaContent.OrderByDescending(e=>e.Occurrence.Value) where (string.IsNullOrEmpty(filter.MediaType)|| media.MediaType == filter.MediaType)&& (!filter.Year.HasValue || media.Occurrence.Value.Year == filter.Year) select new MediaContentModels(media)).OrderBy(x => Guid.NewGuid()).Take(50);` – Bellash Mar 04 '14 at 15:47
1

@Bellash: in LINQ-To-SQL the order seems to be dropped, have a look at this answer which is for you: http://stackoverflow.com/a/648247/284240 The above code works in Linq-To-Entities or Linq-To-Objects (so you'd need a `ToList` after the `Where` which could be slow). – Tim Schmelter Mar 04 '14 at 16:03
@L.B Eric Lippert's disputes your interpretation here: http://stackoverflow.com/questions/1651619/optimal-linq-query-to-get-a-random-sub-collection-shuffle#comment49921584_3169165 . Summary is that using a random _key_ is fine; using a guid is not. – Paul Du Bois Oct 15 '15 at 18:43

score 10 · Answer 2 · edited May 23 '17 at 11:47

How big is the collection? Can you select them all into memory then choose a random collection? If so, then the Shuffle algorithm at Is using Random and OrderBy a good shuffle algorithm? would be a good choice.

return idb.Exam_Question_Int_Tbl
          .Where( e => e.Exam_Tbl_ID == exam_id )
          .ToList()
          .Shuffle()
          .Take( 50 );

If not, then I would suggest a stored procedure that does an ordering by newid() ( SQL Server Random Sort ). I don't think there is any way to translate an expression based on a random number generator in C# to LINQ to SQL/Entities.

score 5 · Answer 3 · edited Sep 12 '17 at 16:12

If you've the same problem, I had...

int Limit = 24;
return (from Q in Context.table
where Q.some_key == 1234
select new classDataType() { 
    FirstAttribute = Q.FirstCol,
    SecondAttribute = Q.SecondCol,
    ThirdAttribute = Q.ThirdCol
}).ToList().OrderBy(x => Guid.NewGuid()).Take(Limit).ToList();

After sql-linq it needs to be a LIST, so maybe U need changing to a list, before you're using the OrderBy-NewGuid-Method:

return (...-SQL-SELECT-LINQ-...)
    .ToList() //****
    .OrderBy(x => Guid.NewGuid()).Take(Limit).ToList();

linq: order by random

3 Answers3

Linked

Related