Get output parameter value of a stored procedure using EF Core FromSql(...) is always null.

[irowbin]

Describe what is not working as expected.

Store procedure output parameter is not working as expected. The output value is always null.
I have an statement like this. I've removed other parameters except this OUTPUT param to pointing out the issue. The records are came but the the value of this OUTPUT parameter is null without the exception. Executing the proc in SSMS works just fine but with this FromSql(...) isn't doing anything for output parameter.

-- other param are working as expected but the OUTPUT is not so i hide them.

@todoCounts VARCHAR(MAX) OUTPUT

var items = _context.Set<T>().FromSql("Sp_Todo @todoCounts OUTPUT", 
 new SqlParameter("@todoCounts", SqlDbType.VarChar)
 { Direction = ParameterDirection.Output}).ToList() // no luck

If i add this too, then the output value coming but this is not the correct idea here. I am not seeing any exceptions.

  _context.Database.ExecuteSqlCommand("Sp_Todo @todoCounts OUTPUT", 
 new SqlParameter("@todoCounts", SqlDbType.VarChar)
 { Direction = ParameterDirection.Output});

If you are seeing an exception, include the full exceptions details (message and stack trace).

none

Steps to reproduce

• Create a store procedure with output parameter.

 @todoCounts VARCHAR(MAX) OUTPUT

• Execute the StoreProc like this.

// records retrieved except OUTPUT parameter. 
context.set<MyModel>().FromSql("Sp_Todo @todoCounts OUTPUT", 
 new SqlParameter("@todoCounts", SqlDbType.VarChar)
 { Direction = ParameterDirection.Output}).ToList() 

Further technical details

EF Core version: 1.1.2
Database Provider: 1.1.2
Operating system: Windows 10
IDE: Visual Studio 2017 15.2(2643.16) release

[divega]

We need to investigate if this is working on 2.0. We will consider patching this depending on the impact and the fix.

[smitpatel]

Since fix for this is out in PR, removing milestone so we can discuss about patch.

[mguinness]

Just to clarify that this issue is in relation to SQL Server. The MySQL database provider has the same issue, but there is a known problem which prevents output parameters from being used with Connector/NET.

[CharlesOkwuagwu]

Please is there a work-around for this in the interim?

[ajcvickers]

@CharlesOkwuagwu Probably the best workaround is to get the DbConnection using context.Database.GetDbConnection() and then create the command and execute it directly using DbConnection/DbCommand, etc.

[CharlesOkwuagwu]

@ajcvickers Thanks, will try that option.

[mhsadiqhussain]

Please use the below code, It works:

SqlParameter spOutVar;

var result = context.set().FromSql("Sp_Todo @todoCounts OUTPUT",
spOutVar = new SqlParameter("@todoCounts", SqlDbType.VarChar) { Direction = ParameterDirection.Output}).ToList();

int outputCount = int.Parse(spOutVar.Value.ToString());

Happy Coding :)