How to get return values and output values from a stored procedure with EF Core?

I am working to convert some EF6 code to EF Core, and ran into this same issue. In Microsoft.EntityFrameworkCore version 2.1.0 the following use of FromSql() does return a result set and set the output parameter. You have to use .ToList() to force the proc to execute immediately, thereby returning both the results and the output param.

This is a sample from my DbContext class:

private DbQuery<ExampleStoredProc_Result> ExampleStoredProc_Results { get; set; }
public IEnumerable<ExampleStoredProc_Result> RunExampleStoredProc(int firstId, out bool outputBit)
{
    var outputBitParameter = new SqlParameter
    {
        ParameterName = "outputBit",
        SqlDbType = SqlDbType.Bit,
        Direction = ParameterDirection.Output
    };

    SqlParameter[] parameters =
    {
        new SqlParameter("firstId", firstId),
        outputBitParameter
    };

    // Need to do a ToList to get the query to execute immediately 
    // so that we can get both the results and the output parameter.
    string sqlQuery = "Exec [ExampleStoredProc] @firstId, @outputBit OUTPUT";
    var results = ExampleStoredProc_Results.FromSql(sqlQuery, parameters).ToList();

    outputBit = outputBitParameter.Value as bool? ?? default(bool);

    return results;
}

The stored proc returns the following entity:

public class ExampleStoredProc_Result
{
    public int ID { get; set; }
    public string Name { get; set; }
}

Here's the stored proc:

CREATE PROCEDURE ExampleStoredProc 
    @firstId int = 0,
    @outputBit BIT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SET @outputBit = 1;

    SELECT @firstId AS [ID], 'first' AS [NAME]
    UNION ALL
    SELECT @firstId + 1 AS [ID], 'second' AS [NAME]
END
GO

Hopefully anyone that comes across this post in the future finds this useful.

map into context as keyless entity

    modelBuilder
.Entity<yourentity>(
    eb =>
    {
        eb.HasNoKey();
        eb.ToView("procname");
    });

add a dbset

   public DbSet<yourentity> yourentity { get; set; }

exclude it from migrations

  modelBuilder.Entity<yourentity>().ToTable("procname", t => t.ExcludeFromMigrations());

then in context can execute it into the mapped type

var values = context.yourentity.FromSqlRaw("EXECUTE dbo.procname");

var results = ExampleStoredProc_Results.FromSql(sqlQuery, parameters).ToList();

DbSet.FromSql Enables you to pass in a SQL command to be executed against the database to return instances of the type represented by the DbSet.

This answer will done the trick for me.

While trying to call a stored procedure with one input and one output parameter using ExecuteSqlCommandAsync EF Core. I was avoiding using FromSql on the entity object. Below code worked great from me so just wanted to share on this old post.

    SqlParameter Param1 = new SqlParameter();
    Param1 .ParameterName = "@Param1";
    Param1 .SqlDbType = SqlDbType.VarChar;
    Param1 .Direction = ParameterDirection.Input;
    Param1 .Size = 50;
    Param1 .Value = "ParamValue";

    SqlParameter Param2 = new SqlParameter();
    Param2 .ParameterName = "@Param2";
    Param2 .SqlDbType = SqlDbType.VarChar;
    Param2 .Size = 2048;
    Param2 .Direction = ParameterDirection.Output;

    var sprocResponse = await _dbContext.Database.ExecuteSqlCommandAsync("[dbo].[StoredProcName]  @Param1= @Param1, @Param2 = @Param2 OUTPUT", new object[] { Param1, Param2});
    string outPutVal= urlParam.Value.ToString();

Late response, but may be useful for someone:

My stored procedure:

create procedure GetSomething(@inCode varchar(5), @outValue int OUTPUT)
as
Set @outValue = 1
end

In C#,

SqlParameter[] parameters =
{
   new SqlParameter("@inCode", SqlDbType.Varchar { Direction = ParameterDirection.Input, Value = "InputValue" },
   new SqlParameter("@outValue", SqlDbType.Int { Direction = ParameterDirection.Output }
}
dbContext.Database.ExecuteSqlRaw("exec GetSomething @inCode, @outValue OUTPUT", parameters);
var result - parameters[1].Value;

The keyword OUTPUT is important while calling from .net