CODEWITHSUNDEEP
c#mysqlentity-framework
Samra
Samra

Reputation: 2013

Mysql stored procedure result is different when called from code

I am using EF 6.0 and Mysql. My stored procedure is

delimiter $$
use ge_wv8g73$$
CREATE PROCEDURE Get_UserMapSelections(IN p_userId int)
BEGIN

SELECT @DefaultMap := ValueLong FROM userselections WHERE section = 'Last 
Selections' and userid = p_userId and Description like 'LastSelectedMapID';

SELECT CONCAT(

(SELECT IFNULL( 
@DefaultMap ,'0')
) ,',', (

SELECT ValueLong FROM userselections where section = 'Last Selections' and userid = p_userId
and Description like concat('MapPage', @DefaultMap)),',',(

SELECT ValueLong FROM userselections where section = 'Last Selections' and userid = p_userId
and Description like concat('MapRow', @DefaultMap)),',',(

SELECT ValueLong FROM userselections where section = 'Last Selections' and userid = p_userId
and Description like concat('MapCol', @DefaultMap))) as selections;

END$$

When i Call it in mysql workbench it returns different result

59,147,8,579

but when i call it through code using EF as follows the result is 59 only

GE_Context.cs

 public string Get_UserMapSelections(int userId)
    {
        StringBuilder spCommand = new StringBuilder();

        MySqlParameter[] mySqlParams = new MySqlParameter[] { new MySqlParameter("p_userId", userId)
                                                            };

        spCommand.Append("CALL Get_UserMapSelections(@p_userId);");

        try
        {
            return this.Database.SqlQuery<string>(spCommand.ToString(), mySqlParams).First();
        }
        catch { return null; }
    }

what could be the issue? Sometimes it has happened before when i used parameter name the same as field name in table e.g if had i used 

select * from maps where userid = userid

but this is not the case here.

Upvotes: 0

Views: 80

Answers (2)

Samra
Samra

Reputation: 2013

I would like to share what exactly i did.

So my stored procedure was returning two result sets which i did not notice, but at the same time only the first result set was being received in the code. So i could not do something like

return this.Database.SqlQuery<string>(spCommand.ToString(), mySqlParams).ElementAt(1);

I have changed my stored procedure as follows

CREATE PROCEDURE Get_UserMapSelections(IN p_userId int)
BEGIN

SET @DefaultMap := (SELECT ValueLong FROM userselections WHERE section = 'Last Selections' and userid = p_userId and Description like 'LastSelectedMapID');

SELECT CONCAT(

(SELECT IFNULL( 
@DefaultMap ,'0')
) ,',', (

SELECT ValueLong FROM userselections where section = 'Last Selections' and userid = p_userId
and Description like concat('MapPage', @DefaultMap)),',',(

SELECT ValueLong FROM userselections where section = 'Last Selections' and userid = p_userId
and Description like concat('MapRow', @DefaultMap)),',',(

SELECT ValueLong FROM userselections where section = 'Last Selections' and userid = p_userId
and Description like concat('MapCol', @DefaultMap))) as selections;

END$$

so it was a matter of adding parenthesis in the query and using SET instead of SELECT

Upvotes: 0

Sunil
Sunil

Reputation: 3424

Your EF framework code:

return this.Database.SqlQuery(spCommand.ToString(), mySqlParams).First();

You are returning only First row. Hence you are getting only 1 result

Upvotes: 2

Related Questions