title: Get output parameter value of a stored procedure using EF Core?
body: <p>I am using Asp.net core and EF core in my application. Basically I want to get multiple result set from a single stored procedure. Tried to search it for last 2 days no such luck. Tried to figure out a work around to resolve it..</p>
  
  <p>This is my stored procedure:</p>
  
  <pre><code>SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  ALTER PROCEDURE [dbo].[usp_CustomerAll_sel]
      @SomeOutput int OUT
  AS
  BEGIN
      SET NOCOUNT ON;
  
      SELECT * 
      FROM [dbo].[Customer]
  
      SELECT @SomeOutput = @@rowcount + 25 --This number 25 is a variable from a complex query but always an integer
  END
  </code></pre>
  
  <p>I have 2 records in that table, so basically it should return a table of customer type and output parameter should return 27..</p>
  
  <p>Now from my .net code what I have tried so far </p>
  
  <pre><code>[HttpGet]
  public async Task&lt;Tuple&lt;IEnumerable&lt;Customer&gt;, int&gt;&gt; GetAllCustomer()
  {
      var votesParam = new SqlParameter
              {
                  ParameterName = "SomeOutput",
                  Value = -1,
                  Direction = ParameterDirection.Output
              };
  
      var y = await _customerContext.Customers.FromSql("usp_CustomerAll_sel @SomeOutput out", votesParam).ToArrayAsync();
  
      return new Tuple&lt;IEnumerable&lt;Customer&gt;, int&gt;(y, (int)votesParam.Value);
  }
  </code></pre>
  
  <p>Above one returning me the list but I am not getting the value of output parameter from DB <code>.(int)votesParam.Value</code> is showing null</p>
  
  <p>Now if I use <code>ExecuteNonQueryAsync</code>, then I am getting the output parameter but not the actual data </p>
  
  <pre><code>private async Task ExecuteStoredProc()
  {
      DbCommand cmd = _customerContext.Database.GetDbConnection().CreateCommand();
  
      cmd.CommandText = "dbo.usp_CustomerAll_sel";
      cmd.CommandType = CommandType.StoredProcedure;
  
      cmd.Parameters.Add(new SqlParameter("@SomeOutput", SqlDbType.BigInt) { Direction = ParameterDirection.Output, Value = -1 });
  
      if (cmd.Connection.State != ConnectionState.Open)
      {
          cmd.Connection.Open();
      }
  
      await cmd.ExecuteNonQueryAsync();
  
      long SomeOutput = (long)cmd.Parameters["@SomeOutput"].Value;
  }
  </code></pre>
  
  <p>Is there any way to get both result set and the output parameter and return as a tuple?</p>
  
  <p>When I just put the hard coded value then it's looks like </p>
  
  <pre><code>[HttpGet]
  public async Task&lt;Tuple&lt;IEnumerable&lt;Customer&gt;, int&gt;&gt; GetAllCustomer()
  {
      var votesParam = new SqlParameter
      {
          ParameterName = "SomeOutput",
          Value = -1,
          Direction = ParameterDirection.Output
      };
  
      var y = await _customerContext.Customers.FromSql("usp_CustomerAll_sel @SomeOutput out", votesParam).ToArrayAsync();
      return new Tuple&lt;IEnumerable&lt;Customer&gt;, int&gt;(y, **25**);
  }
  </code></pre>
  
  <p>And result like </p>
  
  <pre><code>{"item1":[{"customerId":1,"customerName":"Cus1"},{"customerId":2,"customerName":"Cus2"}],"item2":27}
  </code></pre>
  
  <p>Basically this is what I am looking for... Any help?</p>