Using entity structure with MySQL database and model designer does not perceive stored process parameters

Question

I have the latest Mysql connector that allows me to use the Visual Studio Entity Framework constructor. It works fine, but I just added the saved procedure.

The server explorer loaded it with the specified parameters, but then I added it to the Entity Model, and the code that it generates does not have any input parameters.

Here is the stored procedure

CREATE PROCEDURE `GetViewableMenuNodes`(IN siteId INT, IN parentId INT, IN userName varchar(255)) BEGIN select m.* from menunode m where m.siteid = siteId and m.showinmenu = 1 and m.parentid = parentId and m.viewername = userName; END 

and this is the code generated by the model

 public global::System.Data.Objects.ObjectResult<MenuNode> GetViewableMenuNodes() { return base.ExecuteFunction<MenuNode>("GetViewableMenuNodes"); } 

Answer 1

Check out this error:

http://bugs.mysql.com/bug.php?id=44985

Sorry for your luck. Welcome to the club. Obviously, proper support for stored procedures in MySQL from the MySQL Connector / .NET Entity Framework is not available. As you can see from the dates in the stream, there was an incredibly slow response to introducing this feature.

Answer 2

In case you find this useful, here is the approach I use to work with stored procedures with parameters in MySQL from the MySQL Connector / .NET Entity Framework provider. I call ExecuteStoreQuery (). This frees me from the need to solve the problems of mapping procedures with parameters in the model. This works for our needs.

  public IList<SearchResultsMember> SearchMembers(int memberID, string countryCode, string regionCode, string cityCode, float distanceKm, int genderID, int ageMin, int ageMax, int offsetRowIndex, int maxRows) { MySqlParameter[] queryParams = new MySqlParameter[] { new MySqlParameter("memberIDParam", memberID), new MySqlParameter("countryCodeParam", countryCode), new MySqlParameter("regionCodeParam", regionCode), new MySqlParameter("cityCodeParam", cityCode), new MySqlParameter("distanceKmParam", distanceKm), new MySqlParameter("genderIDParam", genderID), new MySqlParameter("ageMinParam", ageMin), new MySqlParameter("ageMaxParam", ageMax), new MySqlParameter("offsetRowIndexParam", offsetRowIndex), new MySqlParameter("maxRowsParam", maxRows) }; StringBuilder sb = new StringBuilder(); sb.Append("CALL search_members(@memberIDParam, @countryCodeParam, @regionCodeParam, @cityCodeParam, @distanceKmParam, @genderIDParam, @ageMinParam, @ageMaxParam, @offsetRowIndexParam, @maxRowsParam)"); string commandText = sb.ToString(); var results = _context.ExecuteStoreQuery<SearchResultsMember>(commandText, queryParams); return results.ToList(); } 

Answer 3

Here is a small extension we made to execute StoredProcedures in our DbContext:

  public static List<T> ExecuteStoredProcedure<T>(this DbContext dbContext, string storedProcedureName, params object[] parameters) { string storedProcedureCommand = "CALL " + storedProcedureName + "("; List<object> augmentedParameters = parameters.ToList(); storedProcedureCommand = AddParametersToCommand(storedProcedureCommand, augmentedParameters); storedProcedureCommand += ");"; return dbContext.Database.SqlQuery<T>(storedProcedureCommand).ToList<T>(); } public static List<T> ExecuteStoredRecursiveProcedure<T>(this DbContext dbContext, string storedProcedureName, params object[] parameters) { string storedProcedureCommand = "SET max_sp_recursion_depth = " + maxRecursionCount + "; CALL " + storedProcedureName + "("; List<object> augmentedParameters = parameters.ToList(); storedProcedureCommand = AddParametersToCommand(storedProcedureCommand, augmentedParameters); storedProcedureCommand += ");"; return dbContext.Database.SqlQuery<T>(storedProcedureCommand).ToList<T>(); } private static string AddParametersToCommand(string storedProcedureCommand, List<object> augmentedParameters) { for (int i = 0; i < augmentedParameters.Count(); i++) { storedProcedureCommand = AddParameterToCommand(storedProcedureCommand, augmentedParameters, i); } return storedProcedureCommand; } private static string AddParameterToCommand(string storedProcedureCommand, List<object> augmentedParameters, int i) { if (augmentedParameters[i].GetType() == typeof(string)) { storedProcedureCommand += "'"; } storedProcedureCommand += (augmentedParameters[i].ToString()); if (augmentedParameters[i].GetType() == typeof(string)) { storedProcedureCommand += "'"; } if (i < augmentedParameters.Count - 1) { storedProcedureCommand += ","; } return storedProcedureCommand; } 

Answer 4

I like that this option is more secure since it can deal with sql input problems. I made it generalized as follows:

 public DbRawSqlQuery<T> ExecuteStoredProcedure<T>(string storedProcedureName, params object[] parameters) { string storedProcedureCommand = "CALL " + storedProcedureName + "("; List<object> augmentedParameters = parameters.ToList(); MySqlParameter[] queryParams; storedProcedureCommand = AddParametersToCommand(storedProcedureCommand, augmentedParameters, out queryParams); storedProcedureCommand += ");"; return ((DbContext)this).Database.SqlQuery<T>(storedProcedureCommand, queryParams); } private string AddParametersToCommand(string storedProcedureCommand, List<object> augmentedParameters, out MySqlParameter[] queryParams) { string paramName = ""; queryParams = new MySqlParameter[augmentedParameters.Count()]; for (int i = 0; i < augmentedParameters.Count(); i++) { paramName = "p" + i; queryParams[i] = new MySqlParameter(paramName,augmentedParameters[i]); storedProcedureCommand += "@" + paramName; if (i < augmentedParameters.Count - 1) { storedProcedureCommand += ","; } } return storedProcedureCommand; } 

Answer 5

Also pay attention to one important thing when working with the mysql database; always specify the parameters of stored procedures in different ways with the column names of the table, otherwise they are confused in the query inside sp.

For instance:

 CREATE PROCEDURE `authenticate_user`( IN p_login_name varchar(50), IN p_password varchar(80) ) BEGIN SELECT `user`.`user_id`, `user`.`login_name`, `user`.`password` from users where `login_name` = p_login_name and `password` = p_password; END 

Answer 6

Here is an example of my code that made it work with VS2012 and the Mysql 6.8.3 connector, just generate your datamodel in the same way as usual, and don't forget to include stored procedures.

Hope this helps someone.

 public static IList<MyClass> GetCustOrderHist(string someParameter) { IList<MyClass> data = ((IObjectContextAdapter)TestDashboardEntities).ObjectContext.ExecuteStoreQuery<MyClass>("CALL CustOrderHist({0});", someParameter).ToList(); return data; } public class MyClass { public string ProductName { get; set; } public int TOTAL { get; set; } } 

Answer 7

It was my decision to call a stored procedure. Hope this is helpful.

 MovieDbEntities db = new MovieDbEntities(); int id = 2; var result = db.Movie.SqlQuery($"CALL SP_ReadMovie({id})").ToList();