Description:
As originally reported by a user at http://forums.mysql.com/read.php?38,519565,519565#msg-519565
Invoking a stored procedure or function in CodeFirst scenario returns an error:
Unhandled Exception: MySql.Data.MySqlClient.MySqlException: You have an error in
your SQL syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near 'GetCount' at line 1
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& i
nsertedId)
at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affected
Rows, Int32& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior
)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior be
havior)
at System.Data.Common.DbCommand.ExecuteReader()
at System.Data.Objects.ObjectContext.ExecuteStoreQueryInternal[TElement](Stri
ng commandText, String entitySetName, MergeOption mergeOption, Object[] paramete
rs)
at System.Data.Entity.Internal.InternalContext.ExecuteSqlQuery[TElement](Stri
ng sql, Object[] parameters)
at System.Data.Entity.Internal.InternalContext.ExecuteSqlQueryAsIEnumerable[T
Element](String sql, Object[] parameters)
at System.Data.Entity.Internal.InternalContext.ExecuteSqlQuery(Type elementTy
pe, String sql, Object[] parameters)
at System.Data.Entity.Internal.InternalSqlNonSetQuery.GetEnumerator()
at System.Data.Entity.Internal.InternalSqlQuery`1.GetEnumerator()
at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
at CodeFirstFunctionMySql.Program.Main(String[] args) in C:\src\MainSource\fe
atures\spdebugger7\CodeFirstFunctionMySql\CodeFirstFunctionMySql\Program.cs:line
18
How to repeat:
Having routine defined as
CREATE PROCEDURE GetCount()
BEGIN
SELECT count(*)
FROM mytable;
END$$
And C# code:
int count = myContext.Database.SqlQuery<int>("GetCount").First();
MySql log reveals a query with just the name of routine "GetCount".
Here are other variants:
int count = db.Database.SqlQuery<int>("select GetCount()").First();
This works when defined as function
Or using the funtion definition:
int count = db.Database.SqlQuery<int>("select count(*) FROM movies ").First();
works
Or adding parenthesis:
int count = db.Database.SqlQuery<int>("GetCount()").First();
does not works
Or using call when defined as stored procedure:
int count = db.Database.SqlQuery<int>("call GetCount()").First();
Works
NOTE: Defining routine as function makes no difference, for example using:
delimiter //
CREATE function GetCount() returns int
DETERMINISTIC
READS SQL DATA
BEGIN
declare cnt int;
return (select count(*) FROM movies );
END //
Suggested fix:
Make syntax
int count = myContext.Database.SqlQuery<int>("GetCount").First();
work as in MSSQL.