title: How to execute stored procedure with input and output parameters using EF?
body: <p>I am using EF code first approach and I have created stored procedure using migration as following:</p>
  
  <pre><code>public override void Up()
      {
          Sql(@"CREATE TYPE IdsList AS TABLE   
                      ( 
                      Id Int
                      )
                      GO
  
                      Create Procedure getChildIds(
                      @IdsList dbo.IdsList ReadOnly
                      )
                      As
                      Begin
                      WITH RecursiveCTE AS
                      (
                          SELECT Id
                          FROM dbo.PhysicalObjects
                          WHERE ParentId in (Select * from @IdsList)
                          --Where Id=108
                          UNION ALL
  
                          SELECT t.Id
                          FROM dbo.PhysicalObjects t
                          INNER JOIN RecursiveCTE cte ON t.ParentId = cte.Id
                      )
                      SELECT * FROM RecursiveCTE
                      End");
      }
  
      public override void Down()
      {
          Sql(@"Drop Type IdsList
                  Go
                  Drop Procedure getChildIds");
      }
  </code></pre>
  
  <p>Now If I go to sql server management studio and execute the following scripts:</p>
  
  <pre><code>Declare @Ids dbo.IdsList
  
  Insert into @Ids
  SELECT 1
  
  Exec getChildIds @Ids
  </code></pre>
  
  <p>It will execute successfuly, but Now I am trying to execute that stored procedure as following:</p>
  
  <pre><code> var idsList = new SqlParameter {ParameterName = "idsList",  Value = new int[] { 1,2,3,4,5} };
  
   var idParams = new SqlParameter("idParams", SqlDbType.Structured)
              {
                  Direction = System.Data.ParameterDirection.Output
              };
  
  var results = dbContext.Database.SqlQuery&lt;int&gt;("getChildIds @idsList, @idParams out", idsList,idParams) ;
  
  var idsResult = (List&lt;int&gt;)idParams.Value;
  </code></pre>
  
  <p>It does not return anything.</p>
  
  <p>So how I could execute stored procedure with input and output parameters of type Table?</p>