CODEWITHSUNDEEP
c#linqrandom
user972087
user972087

Reputation: 675

linq: order by random

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

Upvotes: 64

Views: 66553

Answers (4)

Gianpiero
Gianpiero

Reputation: 3547

With Entity Framework Core 6+ there's a new function: EF.Functions.Random()

db.Table.Where(...).OrderBy(r => EF.Functions.Random()).Take(50)

The advantage of this approach is it uses the internal function of the database engine and it has been implemented for most databases.

See https://github.com/dotnet/efcore/issues/16141#issuecomment-666641607

Upvotes: 0

Froschkoenig84
Froschkoenig84

Reputation: 616

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();

Upvotes: 5

Tim Schmelter
Tim Schmelter

Reputation: 460118

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

Upvotes: 127

tvanfosson
tvanfosson

Reputation: 532465

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.

Upvotes: 10

Related Questions