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

Question

ALTER PROCEDURE [dbo].[SearchMovies]
    --@Year     int = null,
    @CategoryIds varchar(50) = null,
    @Keywords nvarchar(4000) = null,
    @PageIndex int = 1, 
    @PageSize int = 2147483644,
    @TotalRecords int = null OUTPUT
As ...

EF Repository:

 public class EFRepository<T> : IRepository<T> where T : BaseEntity
{
    private readonly ApplicationDbContext _ctx;
    private  DbSet<T> entities;
    string errorMessage = string.Empty;

    public EFRepository(ApplicationDbContext context)
    {
        this._ctx = context;
        entities = context.Set<T>();
    }     
   ...

    public IQueryable<T> ExecuteStoredProcedureList(string commandText, params object[] parameters)
    {          
        _ctx.Database.ExecuteSqlCommand(commandText, parameters);
        return entities.FromSql(commandText, parameters);
    }
}

I call this like:

var pCategoryIds = new SqlParameter()
            {
                ParameterName = "@CategoryIds",
                Value = commaSeparatedCategoryIds,
                DbType = DbType.String
            };
var pKeywords = new SqlParameter()
            {
                ParameterName = "@Keywords",
                DbType = DbType.String,
                Value = name
            };
var pPageIndex = new SqlParameter()
            {
                ParameterName = "@PageIndex",
                DbType = DbType.Int32,
                Value = pageIndex
            };
var pPageSize = new SqlParameter()
            {
                ParameterName = "@PageSize",
                DbType = DbType.Int32,
                Value = pageSize
            };

var pTotalRecords = new SqlParameter();
pTotalRecords.ParameterName = "@TotalRecords";
pTotalRecords.Direction = ParameterDirection.Output;
pTotalRecords.DbType = DbType.Int32;

var query1 = _ctx.Database.ExecuteSqlCommand("dbo.[SearchMovies] " +
                "@CategoryIds, @Keywords, @PageIndex, @PageSize, @TotalRecords OUTPUT", 
                pCategoryIds, pKeywords, pPageIndex, pPageSize, pTotalRecords);

var query2 = _ctx.Set<MovieItem>.FromSql("dbo.[SearchMovies] " +
                    "@CategoryIds, @Keywords, @PageIndex, @PageSize, @TotalRecords OUTPUT",
                    pCategoryIds, pKeywords, pPageIndex, pPageSize, pTotalRecords);

query1 does get the output pTotalRecords fine, but no return values, and the second query2 gets the return values but no output parameter.

In EF 6, we used to have SqlQuery to do both actions in one command, how can I do the same in EF core ?

UPDATED:

Temporarily, I run 2 query, one to get the output param and one for result set.

 public IQueryable<T> ExecuteStoredProcedureList(string commandText, params object[] parameters)
    {          
        _ctx.Database.ExecuteSqlCommand(commandText, parameters);
        return entities.FromSql(commandText, parameters);
    }

Answer 1

I did something like this :-

-- My stored procedure:

CREATE PROCEDURE p1
AS
     BEGIN            
        RETURN 29
     END
GO

C# code

SqlParameter[] @params = 
{
   new SqlParameter("@returnVal", SqlDbType.Int) {Direction = ParameterDirection.Output}
 };   


_stagingContext.Database.ExecuteSqlCommand("exec @returnVal=" + storedProcName, @params);

var result = @params[0].Value; //result is 29 

Hope this helps