title: How to get return values and output values from a stored procedure with EF Core?
body: <pre><code>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 ...
  </code></pre>
  
  <p>EF Repository:</p>
  
  <pre><code> public class EFRepository&lt;T&gt; : IRepository&lt;T&gt; where T : BaseEntity
  {
      private readonly ApplicationDbContext _ctx;
      private  DbSet&lt;T&gt; entities;
      string errorMessage = string.Empty;
  
      public EFRepository(ApplicationDbContext context)
      {
          this._ctx = context;
          entities = context.Set&lt;T&gt;();
      }     
     ...
  
      public IQueryable&lt;T&gt; ExecuteStoredProcedureList(string commandText, params object[] parameters)
      {          
          _ctx.Database.ExecuteSqlCommand(commandText, parameters);
          return entities.FromSql(commandText, parameters);
      }
  }
  </code></pre>
  
  <p>I call this like:</p>
  
  <pre><code>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&lt;MovieItem&gt;.FromSql("dbo.[SearchMovies] " +
                      "@CategoryIds, @Keywords, @PageIndex, @PageSize, @TotalRecords OUTPUT",
                      pCategoryIds, pKeywords, pPageIndex, pPageSize, pTotalRecords);
  </code></pre>
  
  <p><code>query1</code> does get the output <code>pTotalRecords</code> fine, but no return values, and the second <code>query2</code> gets the return values but no output parameter. </p>
  
  <p>In EF 6, we used to have <code>SqlQuery</code> to do both actions in one command, how can I do the same in EF core ?</p>
  
  <p><strong>UPDATED</strong>:</p>
  
  <p>Temporarily, I run 2 query, one to get the output param and one for result set.</p>
  
  <pre><code> public IQueryable&lt;T&gt; ExecuteStoredProcedureList(string commandText, params object[] parameters)
      {          
          _ctx.Database.ExecuteSqlCommand(commandText, parameters);
          return entities.FromSql(commandText, parameters);
      }
  </code></pre>