Random order with Entity Framework and MySQL

Question

Summary: I want to find a way to make random order with Entity Framework and MySQL (that's important). That solution shouldn't use raw sql queries or ordering AFTER loading all values from database.

What I've tried:

I took idea about using NewGuid() for random order from that answer.

Code:

var query = from e in context.Table
orderby Guid.NewGuid()
select e;
var test = query.FirstOrDefault();

Always thrown exception:

An error occurred while executing the command definition. See the inner exception for details.
Inner exception:
FUNCTION MyDatabase.NewGuid does not exist System.Exception {MySql.Data.MySqlClient.MySqlException}

Seems that problem is that MySQL doesn't have function NewGuid().

How can I order by MySQL function RAND() instead of NewGuid(). In other words, how to use custom function RAND in Entity Framework?

Answer 1

Seems that problem is that MySQL doesn't have function NewGuid().

Call me lazy, but the problem seems to be that MySql does not have a function called NewGuid. So wouldn't the easiest solution be to create a function in MySql called NewGuid?

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `NewGuid`() RETURNS char(36)
BEGIN
RETURN UUID();
END$$
DELIMITER ;

Problem is solved for me after creating a MySQL NewGuid function.

Edit: The Questions states "solution shouldn't use raw sql queries" and this does not require a raw SELECT/INSERT statement. It does require the database function to be created though.

Extra note: I would say that this is technically a bug in the MySQL implementation. Canonical Functions are said to be "supported by all data providers" by Microsoft. NewGuid is listed under Other Canonical Functions

Answer 2

Use the below query:

var query = from e in context.Table
            orderby SqlFunctions.Rand(1) 
            select e).Take(10);

And hope your MySql Data Providor cannot be recognized by VS.

I know that Vs doesn't support MySQL to LINQ directly.. So maybe you can use something like.

And perhpas you need to download this kind of data providor and have a try

download them and have a try:

1. ALinq.
2. ORDesigner_VS2008 or ORDesigner_VS2010.
3. MySQL ADO.NET Data Provider (If installed, no need to reinstall).

---

---

And If you still fail, this can be right Suppose Students is a model from Student table

using (ConsoleApplications.DataClasses1DataContext dd = new ConsoleApplications.DataClasses1DataContext())
{
   var result = (from stu in dd.Students.AsEnumerable()
      select new
      {
       stu.StudentName,
       RandomId = new Random(DateTime.Now.Millisecond).Next(dd.Students.Count())
      }).OrderBy(s => s.RandomId);

      foreach (var item in result)
      {
        Console.WriteLine(item.StudentName);
      }
}