Bug #64999 Syntax error when running an existing routine in a code first scenario
Submitted: 16 Apr 2012 21:05 Modified: 14 May 2013 22:53
Reporter: Fernando Gonzalez.Sanchez Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: Roberto Ezequiel Garcia Ballesteros CPU Architecture:Any

[16 Apr 2012 21:05] Fernando Gonzalez.Sanchez
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.
[18 Jun 2012 21:25] John Russell
Added to changelog for 6.5.5: 

A call to a stored procedure or function in an application using the
Code First entity framework could result in an error:

Unhandled Exception: MySql.Data.MySqlClient.MySqlException: You have an error
in your SQL syntax; ...

The code change allows syntax such as the
following to invoke a stored procedure, without using the CALL
statement and without using CommandType.StoredProcedure:

int count = myContext.Database.SqlQuery("GetCount").First();
[24 Aug 2012 23:45] John Russell
Also added to changelog for 6.4.6, 6.6.0.