Getting out parameter of stored procedure with C# only works when I put a breakpoint in my code

Question

I have database connection setup with Entity Framework. I've created multiple stored procedures and one of them has an output parameter that I need in my application.

Procedure in c# :

public virtual ObjectResult<Nullable<System.Guid>> AjouterProfesseur(string prenom, string nom, ObjectParameter identity)
{
        var prenomParameter = prenom != null ?
            new ObjectParameter("prenom", prenom) :
            new ObjectParameter("prenom", typeof(string));

        var nomParameter = nom != null ?
            new ObjectParameter("nom", nom) :
            new ObjectParameter("nom", typeof(string));

        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Nullable<System.Guid>>("AjouterProfesseur", prenomParameter, nomParameter, identity);
}

To retrieve the output parameter I use following (this is also where I have to put my breakpoint and step over it for it to work):

public static Guid AddProfesseur(string prenom, string nom)
{
        using (ExamenProjetIntegrationEntities db = new ExamenProjetIntegrationEntities())
        {
            ObjectParameter objParam = new ObjectParameter("identity", typeof(Guid));
            var resultToReturn = db.AjouterProfesseur(prenom, nom, objParam);
            return  Guid.Parse(objParam.Value.ToString());
        }
}

Then I have a business layer who calls that method again :

public static Guid addProfesseur(string prenom, string nom)
{
        try
        {
            var data = Data.AddProfesseur(prenom, nom);
            return data;
        }
        catch (Exception e)
        {
            var sqlex = e.InnerException as SqlException;

            if (sqlex != null)
            {
                switch (sqlex.Number)
                {
                    default:
                        throw new Exception(sqlex.Number + " - " + sqlex.Message);
                }
            }

            throw e;
        }
}

And finally in my API controller I'm using following statement :

var idProfesseur = BL.addProfesseur(professeur.prenom, professeur.nom);

I call the method with ajax in my ASP.NET MVC view.

Does anyone has any idea why this is and how I can solve this issue?

EDIT :

Following link is exactly what I'm doing : Executing SQL Stored Procedure with Output Parameter from Entity Framework .But my problem is the fact that I need to step through it for it to work

Answer 1

Output parameters are not available until the return result has been read completely, or the underlying DbDataReader is closed.

See the Parameters section of ObjectContext.ExecuteFunction, and the second sentence of this section on DataReaders

You can force this by evaluating the result with LINQ.

public static Guid AddProfesseur(string prenom, string nom)
{
        using (ExamenProjetIntegrationEntities db = new ExamenProjetIntegrationEntities())
        {
            ObjectParameter objParam = new ObjectParameter("identity", typeof(Guid));
            var resultToReturn = db.AjouterProfesseur(prenom, nom, objParam).Count();
            return  Guid.Parse(objParam.Value.ToString());
        }
}

You could also use .ToList(), .FirstOrDefault(), or whatever method you prefer to read the underlying result.

Answer 2

Using the underlying SqlParameter would require .Direction = ParameterDirection.Output. ObjectParameter doesn't have an equivalent setting. Instead I believe the parameter direction is configured in your model.

Answer 3

This might not be the solution for all but I found if I disposed the ObjectResult returned from the stored procedure call, the output parameter values became available. Would need extra logic if the values in the ObjectResult were still needed.

ObjectParameter outputParam= new ObjectParameter("outputparam",typeof(string)); 

ObjectResult spResult = db.sp_check_dup_hostname_or_ips(param1, param2, param3, param4, outputParam);

spResult.Dispose();
           
string myOutputString = Convert.ToString(outputparam.Value);