MySql Entity Framework Select

Question

I am having trouble with an MySql query.

string strSql = "select SQL_CALC_FOUND_ROWS *, pv.* from products pv WHERE pv.name = 'Teddy Bear';";

strSql += "SET @resultCount = FOUND_ROWS();"

MySqlParameter parm = new MySqlParameter("@resultCount",MySqlDbType.Int32)
parm.Direction = ParameterDirection.Output;

var result = ObjectContext.ExecuteStoreQuery<Product>(strSql,parm);
return result;

The Error returned is

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 'NULL = FOUND_ROWS()' at line 1

How do I get @resultCount to return the total record count

Answer 1

When creating the parameter I think you need to specify the name without @.

Also, you'll want to specify the parameter direction; I think that by default it's input only, meaning you need to provide a value that will be inserted into the command; what you want is output. You can specify the direction through some overloads when creating the parameter or by setting a property - don't know if the syntax differs from other providers as I haven't worked with the MySql one in quite some time, but it should be:

parm.Direction = ParameterDirection.Output;