title: How do you get the id value of record inserted from a stored procedure using EF Core
body: <p>I'm using EF Core 5 and am having trouble getting back the userId of a record that I inserted into the table. I've tried different ways of doing this but I've had no luck. All I get back is a -1 which I know is for rows affected. I can't seem to retrieve the UserID which is declared as an <code>OUTPUT</code> parameter for the stored procedure.</p>
  <p>Stored procedure:</p>
  <pre><code>ALTER PROCEDURE [dbo].[User.Save]
      (@Return_Code  INT OUTPUT,
       @Error_Description_Code NVARCHAR(50) OUTPUT,
       @idCallerSite INT = 0, --default if not specified
       @idCaller     INT = 0,
       @idUser       INT OUTPUT, 
       @firstName     NVARCHAR(50),
       @middleName    NVARCHAR(50),
       @lastName      NVARCHAR(50),
       @preferredName NVARCHAR(50),
       @username      NVARCHAR(512),
       @password      NVARCHAR(512),
       @isActive      BIT,
       @isSalesperson BIT,
       @userPreferences NVARCHAR(512),
       @phoneNumber     NVARCHAR(25),
       @phoneFax        NVARCHAR(25),
       @email           NVARCHAR(255),
       @photo           NVARCHAR(255),
       @lastEditedBy    INT)
  AS
  BEGIN
      SET NOCOUNT ON
  
      DECLARE @idPermission INT  -- define the permission required to perform this function
      SET @idPermission = 141
      
      /* 
      validate uniqueness
      */
      IF (SELECT COUNT(1)
          FROM Users
          WHERE SiteID = @idCallerSite
            AND (username = @username)
            AND (@idUser IS NULL OR @idUser &lt;&gt; UserID)) &gt; 0
      BEGIN
          SET @Return_Code = 2
          SET @Error_Description_Code = 'UserSave_UserNotUnique'
          RETURN 1 
      END
  
      /*
      insert/update the user
      */
  
      IF (@idUser = 0 OR @idUser IS NULL)
      BEGIN
          -- insert the new user
          INSERT INTO Users (SiteID, firstName, middleName, lastName,
                             preferredName, username,
                             HashedPassword, isActive, IsSalesperson,
                             UserPreferences, PhoneNumber, FaxNumber,
                             EmailAddress, Photo, LastEditedBy)
          VALUES (@idCallerSite, @firstName, @middleName, @lastName,
                  @preferredName, @username, 
                  dbo.GetHashedString('SHA1', @password), @isActive, @isSalesperson,
                  @userPreferences, @phoneNumber, @phoneFax,
                  @email, @photo, @lastEditedBy)
  
          /*
          get the new user's id
          */
  
          SET @idUser = SCOPE_IDENTITY()
          SELECT @idUser
  
          -- return successfully
          SET @Return_Code = 0 --(0 is 'success')
          SET @Error_Description_Code = ''
      END
  </code></pre>
  <p>Controller Call</p>
  <pre><code>var retCode = new SqlParameter { ParameterName = &quot;@Return_Code&quot;, Value = null, SqlDbType = (SqlDbType.Int), Direction = System.Data.ParameterDirection.Output };
  
  var errorDesc = new SqlParameter { ParameterName = &quot;@Error_Description_Code&quot;, Value = null, SqlDbType = SqlDbType.NVarChar, Size = 50, Direction = System.Data.ParameterDirection.Output };
  var idCallerSite = new SqlParameter { ParameterName = &quot;@idCallerSite&quot;, Value = 1, SqlDbType = (SqlDbType.Int), Direction = System.Data.ParameterDirection.Input };
  var idCaller = new SqlParameter { ParameterName = &quot;@idCaller&quot;, Value = 0, SqlDbType = (SqlDbType.Int), Direction = System.Data.ParameterDirection.Input };
  var idUser = new SqlParameter { ParameterName = &quot;@idUser&quot;, Value = user.UserID, SqlDbType = (SqlDbType.Int), Direction = System.Data.ParameterDirection.Output };
  var fName = new SqlParameter { ParameterName = &quot;@firstName&quot;, Value = user.FirstName.ToString(), SqlDbType = SqlDbType.NVarChar, Size = 50, Direction = System.Data.ParameterDirection.Input };
  var mName = new SqlParameter { ParameterName = &quot;@middleName&quot;, Value = user.MiddleName.ToString(), SqlDbType = SqlDbType.NVarChar, Size = 50, Direction = System.Data.ParameterDirection.Input };
  var lName = new SqlParameter { ParameterName = &quot;@lastName&quot;, Value = user.LastName.ToString(), SqlDbType = SqlDbType.NVarChar, Size = 50, Direction = System.Data.ParameterDirection.Input };
  var pName = new SqlParameter { ParameterName = &quot;@preferredName&quot;, Value = user.PreferredName.ToString(), SqlDbType = SqlDbType.NVarChar, Size = 50, Direction = System.Data.ParameterDirection.Input };
  var userName = new SqlParameter { ParameterName = &quot;@username&quot;, Value = user.UserName.ToString(), SqlDbType = SqlDbType.NVarChar, Size = 512, Direction = System.Data.ParameterDirection.Input };
  var password = new SqlParameter { ParameterName = &quot;@password&quot;, Value = user.HashedPassword.ToString(), SqlDbType = SqlDbType.NVarChar, Size = 512, Direction = System.Data.ParameterDirection.Input };
  var isActive = new SqlParameter { ParameterName = &quot;@isActive&quot;, Value = user.IsActive, SqlDbType = (SqlDbType.Bit), Direction = System.Data.ParameterDirection.Input };
  var isSalesperson = new SqlParameter { ParameterName = &quot;@isSalesperson&quot;, Value = user.IsSalesperson, SqlDbType = (SqlDbType.Bit), Direction = System.Data.ParameterDirection.Input };
  var userPref = new SqlParameter { ParameterName = &quot;@userPreferences&quot;, Value = user.UserPreferences.ToString(), SqlDbType = SqlDbType.NVarChar, Size = 512, Direction = System.Data.ParameterDirection.Input };
  var phone = new SqlParameter { ParameterName = &quot;@phoneNumber&quot;, Value = user.PhoneNumber.ToString(), SqlDbType = SqlDbType.NVarChar, Size = 25, Direction = System.Data.ParameterDirection.Input };
  var fax = new SqlParameter { ParameterName = &quot;@phoneFax&quot;, Value = user.FaxNumber.ToString(), SqlDbType = SqlDbType.NVarChar, Size = 25, Direction = System.Data.ParameterDirection.Input };
  var email = new SqlParameter { ParameterName = &quot;@email&quot;, Value = user.EmailAddress.ToString(), SqlDbType = SqlDbType.NVarChar, Size = 255, Direction = System.Data.ParameterDirection.Input };
  var photo = new SqlParameter { ParameterName = &quot;@photo&quot;, Value = user.Photo.ToString(), SqlDbType = SqlDbType.NVarChar, Size = 255, Direction = System.Data.ParameterDirection.Input };
  var lastEditedBy = new SqlParameter { ParameterName = &quot;@lastEditedBy&quot;, Value = user.LastEditedBy, SqlDbType = (SqlDbType.Int), Direction = System.Data.ParameterDirection.Input };
  
  var _user = await _db.Database.ExecuteSqlRawAsync(&quot;EXEC [dbo].[User.Save] @Return_Code, @Error_Description_Code, @idCallerSite, @idCaller, @idUser OUT, @firstName, @middleName, @lastName, @preferredName, @username, @password, @isActive, @isSalesperson, @userPreferences, @phoneNumber, @phoneFax, @email, @photo, @lastEditedBy&quot;, retCode, errorDesc, idCallerSite, idCaller, idUser, fName, mName, lName, pName, userName, password, isActive, isSalesperson, userPref, phone, fax, email, photo, lastEditedBy);
  
  return Ok(_user);
  </code></pre>
  <p>I used <code>.FromSql</code> and got the following error.</p>
  <pre><code>var _user = _db.Users.FromSqlRaw(&quot;EXEC [dbo].[User.Save] @Return_Code, @Error_Description_Code, @idCallerSite, @idCaller, @idUser OUT, @firstName, @middleName, @lastName, @preferredName, @username, @password, @isActive, @isSalesperson, @userPreferences, @phoneNumber, @phoneFax, @email, @photo, @lastEditedBy&quot;, retCode, errorDesc, idCallerSite, idCaller, idUser, fName, mName, lName, pName, userName, password, isActive, isSalesperson, userPref, phone, fax, email, photo, lastEditedBy);
  </code></pre>
  <blockquote>
  <p>System.InvalidOperationException: The required column 'UserID' was not present in the results of a 'FromSql' operation.</p>
  <p>at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable<code>1.BuildIndexMap(IReadOnlyList</code>1 columnNames, DbDataReader dataReader)<br />
  at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable<code>1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func</code>4 operation, Func<code>4 verifySucceeded, CancellationToken cancellationToken)   at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable</code>1.AsyncEnumerator.MoveNextAsync()<br />
  at Microsoft.AspNetCore.Mvc.Infrastructure.AsyncEnumerableReader.ReadInternal[T](Object value)<br />
  at Microsoft.AspNetCore.Mvc.Infrastructure.AsyncEnumerableReader.ReadInternal[T](Object value)<br />
  at Microsoft.AspNetCore.Mvc.Infrastructure.ObjectResultExecutor.ExecuteAsyncEnumerable(ActionContext context, ObjectResult result, Object asyncEnumerable, Func`2 reader)<br />
  at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|29_0[TFilter,TFilterAsync](ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)<br />
  at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context)<br />
  at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State&amp; next, Scope&amp; scope, Object&amp; state, Boolean&amp; isCompleted)<br />
  at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|27_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)<br />
  at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)<br />
  at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)<br />
  at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)<br />
  at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)<br />
  at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)<br />
  at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)<br />
  at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)</p>
  </blockquote>
  <p>Headers:</p>
  <pre><code>Accept: */*
  Accept-Encoding: gzip, deflate, br
  Accept-Language: en-US,en;q=0.9
  Connection: keep-alive
  Content-Length: 301
  Content-Type: application/json
  Cookie: fblo_329420117573382=y
  Host: localhost:62374
  Referer: http://localhost:62374/swagger/index.html
  User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36
  sec-ch-ua: &quot; Not;A Brand&quot;;v=&quot;99&quot;, &quot;Google Chrome&quot;;v=&quot;91&quot;, &quot;Chromium&quot;;v=&quot;91&quot;
  sec-ch-ua-mobile: ?0
  Origin: http://localhost:62374
  Sec-Fetch-Site: same-origin
  Sec-Fetch-Mode: cors
  Sec-Fetch-Dest: empty
  </code></pre>