Using SqlFunctions or the equivalent EdmFunctions collection with MySql and Entity Framework

Question

I am using Linq for Entities supported by MySql. I would like to be able to use various MySql built-in functions such as rand . If I used MS SQL Server, I could use the SqlFunctions class, but this does not work with MySql; I get an error message:

Rand () for type 'System.Data.Objects.SqlClient.SqlFunctions' cannot be translated into a LINQ to Entities expression.

I figured out how to create a user-defined function in my database that wraps the built-in RAND:

 CREATE FUNCTION Random () RETURNS real NOT DETERMINISTIC RETURN RAND(); 

Then I update my model from the database (I use .ebmx) and create a static class as follows:

 public static class MyUserFunctions { [EdmFunction("MyModelNamespace.Store", "Random")] public static double Random() { throw new ArgumentNullException(); } } 

And this allows me to call MyUserFunctions.Random inside the .Where clause over my Entities class:

 using (MyEntities entities = new MyEntities()) { // Yes, I know ORDER BY RAND() is slow return entities.products.OrderBy(prod => MyUserFunctions.Random()).Take(4); } 

So the question is, can I do this without creating a stupid UDF shell and just make all the built-in MySql functions more accessible directly (possibly by setting the EdmFunction Namespace property EdmFunction (in the case of SqlFunctions the namespace is "SqlServer" ).

Answer 1

there are some Canonical Functions that all providers should support them, see this: Canonical functions , but I think there is no Random , if there is Random in the list, you can just create a CLR Method and map it to the Random attribute EdmFunction and Edm NamespaceName , and there is no need to create a UDF and <function> element in your model ... if there is no canonical function Random , you can create a function element in the conceptual definition circuit language (CSDL) containing the DefiningExpression element and write your statement (probably in TSQL ) in this section instead of defining UDF in a DataBase and defining input parameter input parameter and return type for it and mapping the CRL function to EdmFunction , and calling from linq , see this: Calls defined by the model in queries