The output parameter got a value 'null' of Stored Procedure.

[faust21]

I have a own-defined DbSet<> class, after i call the FromSql method, i got a value null.

public IQueryable<SPCreateDirectOrder> CreateDirectOrder(string userName, string goodsJson, out int errorCode)
        {
            SqlParameter[] prms = new SqlParameter[]
            {
                new SqlParameter{ParameterName = "UserName", DbType = DbType.StringFixedLength, Size=50, Value = userName},
                new SqlParameter{ParameterName = "GoodsJson", DbType = DbType.String, Value = goodsJson},
                new SqlParameter{ParameterName = "ErrorCode", DbType = DbType.Int32, Direction = ParameterDirection.Output}
            };

            var qryResult = SPCreateDirectOrder.FromSql("EXECUTE dbo.procCreateDirectOrder @UserName,@GoodsJson,@ErrorCode OUTPUT", prms);

            errorCode = Convert.ToInt32(prms[2].Value);

            return qryResult;
        }

I can ensure the procCreateDirectOrder runs well, but the prms[2].Value is null.

[smitpatel]

Duplicate of #9277

Which version of EF Core are you using? This has been fixed in 2.0.1 version.

[faust21]

@smitpatel But i'm using the 2.0.1 version.

[smitpatel]

Is value of errorCore is null or prms[2].Value is null?

[faust21]

@smitpatel The prms[2].Value is null.

[smitpatel]

@Whatsaf - Please share a repro code which can demonstrate the issue.

In above code snippet,
qryResult is IQueryable so query is constructed and not executed. Untill the query is executed (perhaps by calling ToList on it), the out parameter value will not be populated.
prms is local variable in the function, so outside of the function it cannot be accessed especially after actually executing the query returned.
errorCode is field/property in the class enclosing this method but it takes value of prms[2].Value before query is executed which will be null at that time. So checking value of errorCode will give null only even though later the value is populated.

[faust21]

@smitpatel

1. Create a procedure like this:

CREATE PROCEDURE procOutputValue
  @ReturnCode int OUTPUT
  AS

  set @ReturnCode=1
  SELECT 'succed' as Message
GO

2. Scaffold the DbContext and create a class to hold the procedure's return value.

public class ProcResult
    {
        public string Message { get; set; }
    }

3. In the DbContext class, create a property:
   private DbSet<ProcResult> ProcResult { get; set; }
   and in the OnModelCreating method place the below codes:

modelBuilder.Entity<ProcResult>(entity =>
            {
                entity.HasKey(e => e.Message);
            });

Finally, call the FromSql method like this:

public List<ProcResult> ExecProc(out int returnCode)
        {
            var parameters = new[] {
                new SqlParameter{ ParameterName = "ReturnCode", DbType = DbType.Int32, Direction = ParameterDirection.Output }
            };

            var result = ProcResult.FromSql("EXECUTE procOutputValue @ReturnCode OUTPUT", parameters);

            returnCode = Convert.ToInt32(parameters[0].Value);

            return result.ToList();
        }

and the returnCode will be 0, not 1 expected.

[ajcvickers]

@Whatsaf As @smitpatel said above, the output parameter will not be set until after the query has been enumerated. In other words, you'll need to run result.ToList() before getting the value:

var queryResults = result.ToList();

returnCode = Convert.ToInt32(parameters[0].Value);

return queryResults;

This is the behavior of the SQL Server provider for .NET (SqlClient) and is not something that EF can change.

[faust21]

@ajcvickers I'm so sorry about that, i just couldn't understood what smitpatel said. But now with the codes, i can understand that clearly. Now, the codes runs well. Apologize for my foolish.