Getting stored procedure output parameter with Entity Framework is throwing a mapping error: The data reader is incompatible with the specified

Question

I am working on a legacy application and I am trying to make a call to a stored procedure that returns an integer but I am getting a mapping error. The error itself is this one:

The data reader is incompatible with the specified IF_Insert_Incidents_Citizen'. A member of the type, 'intID', does not have a corresponding column in the data reader with the same name.

I tried to change the name to _intId, knowing that Entity Framework translates all the names that starts with @variable to a _variable.

The weird thing is if I make use LinqPad and make a call to the stored procedure I get the correct value of @intID

This is the stored procedure:

CREATE PROCEDURE [dbo].[IF_Insert_Incidents_Citizen]
    @chvFolio varchar(50),
    @chvOwner_Name varchar(100),
    @chvOwner_Address varchar(100),
    @dtsDate_Created smalldatetime, 
    @intUser_ID integer, 
    @chvDescription varchar(250), 
    @chvEmail varchar(50), 
    @intIncident_Type_ID integer, 
    @intIncident_Status integer, 
    @intID int Output
AS
    SET nocount on

    DECLARE @intErrorCode INT
    DECLARE @intApp_ID INT

    SELECT @intIncident_Type_ID = CAST(Param_Value AS INT)
    FROM IF_Parameters
    WHERE Param_Name = 'Citizen_Incident_Type_ID'

    BEGIN
        --Get an application id
        INSERT INTO MasterApplication (DateCreated) 
        VALUES (getdate())

        SELECT @intApp_ID = SCOPE_IDENTITY()

        INSERT INTO IF_Incidents
        (
            Folio, 
            Owner_Name,
            Owner_Address,
            Date_Created, 
            User_ID, 
            Description, 
            Incident_Type_ID, 
            Incident_Status,
            App_ID
        )
        VALUES 
        (
            @chvFolio, 
            @chvOwner_Name,
            @chvOwner_Address,
            @dtsDate_Created, 
            @intUser_ID, 
            @chvDescription ,
            @intIncident_Type_ID, 
            @intIncident_Status,
            @intApp_ID 
        )
        Select @intErrorCode = @@Error, @intID = SCOPE_IDENTITY()

        --Insert Complaint
        INSERT INTO IF_Complaints
        (
            Incident_ID, 
            Complainant_ID, 
            Description ,  
            User_ID, 
            Officer_Assigned_ID, 
            Complaint_Status_ID, 
            Date_Made,
            Email_Complainant
        )
        VALUES 
        (
            @intID, 
            Null, 
            @chvDescription + ' at ' + @chvOwner_Address, 
            1, 
            1, 
            1, 
            @dtsDate_Created ,
            @chvEmail
        )
        Select @intErrorCode = @@Error--, @intID = SCOPE_IDENTITY()
    End

    Return @intErrorCode

As you can see in the stored procedure, the intId always gets an incremental value after the insert, calling SCOPE_IDENTITY()

Now I am trying to call my stored procedure using Entity Framework the following way:

var bptRep = DependencyFactory.GetDependency<ICetRepository<IF_Insert_Incidents_Citizen>>();
var parameters = GetIfInsertIncidentsCitizenParamenters();
var res = bptRep.GetAllFromStoredProcedure(storedProcedure, parameters);

The repository use this class

//class with just the return value
public class IF_Insert_Incidents_Citizen
{
    public int? intID { get; set; }
}

Here I get all the parameters for the stored procedure

 private IEnumerable<SqlParameter> GetIfInsertIncidentsCitizenParamenters()
 {
        // Create Parameters
        var parameters = new List<SqlParameter>();

        var param1 = CreateSqlParameter("@chvFolio", SqlDbType.NVarChar, ParameterDirection.Input, criteria["chvFolio"].Value, 50); 

        parameters.Add(param1);

          ....

        var paramX = CreateSqlParameter("@intIncident_Status", SqlDbType.Int, ParameterDirection.Input, 10);
        parameters.Add(paramX);

        var outparam = CreateSqlParameter("@intID", SqlDbType.Int, ParameterDirection.InputOutput, DBNull.Value);
        parameters.Add(outparam);
}

public IQueryable<TEntity>GetAllFromStoredProcedure(string storedProcedure, IEnumerable<SqlParameter> parameters)
{
    var result = Context.Database.SqlQuery<TEntity>(storedProcedure, parameters).AsQueryable();    
    return result;
}

I followed the approach that @GertArnold suggested but still not working, this are my parameters and the code

var returnCode = new SqlParameter("@ReturnCode", SqlDbType.Int);
returnCode.Direction = ParameterDirection.Output;

var sql = "exec IF_Insert_Incidents_Citizen @chvFolio, @chvOwner_Name, @chvOwner_Address, @dtsDate_Created, @intUser_ID, @chvDescription, @chvEmail, @intIncident_Type_ID, @intIncident_Status, @intID OUT";

var data = ctx.Database.SqlQuery<object>(sql, returnCode, parameters);

var result = data.FirstOrDefault();

Answer 1

declare your output parameter as your desired type then use this code

SqlParameter p1 = new SqlParameter { ParameterName = "p1", Value = [something], SqlDbType = SqlDbType.VarChar };
SqlParameter pResult = new SqlParameter { ParameterName = "pResult", Value = [something], SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output };
var sql = "exec [your-sp-name] @p1,@pResult OUT";
var spresult = _entities.Database.ExecuteSqlCommand(sql, p1, pResult);
string result = pResult.SqlValue.ToString();

Answer 2

Finally @GertArnold put me on the right direction, if you haven't read this post, please do it, will save you a lot of time Stored procedures with output parameters using SqlQuery in the DbContext API

I just added to my @intId the OUT on the exec call, there is no need to create a ReturnCode variable and call exec @ReturnCode = ....

var returnCode = new SqlParameter("@ReturnCode", SqlDbType.Int);
returnCode.Direction = ParameterDirection.Output;

My code finally looks like this, please be aware to call .ToArray method on the parameters instead of passing a collection of IEnumerable or IList to the method

var sql = "exec IF_Insert_Incidents_Citizen @chvFolio, @chvOwner_Name, @chvOwner_Address, @dtsDate_Created, @intUser_ID, @chvDescription, @chvEmail, @intIncident_Type_ID, @intIncident_Status, @intID OUT";

var data = ctx.Database.SqlQuery<object>(sql, returnCode, parameters.ToArray());

var result = data.FirstOrDefault();