The required column 'UserId' was not present in the results of a 'FromSql' operation

Question

I am using VS 2019, SQL Server 2016, NET 5.0 in a NET Core application. I am getting on Postman the error:

The required column 'UserId' was not present in the results of a 'FromSql' operation

From I searched it seems that the stored procedure has to return an id, so I modified it, but still the error remains

The model:

public class User
{
    public User()
    {
        Relatives = new HashSet<Relative>();
    }

    [Key]
    public int UserId { get; set; }
    public DateTime? DateOfBirth { get; set; }
    public string UserNames { get; set; }
    public string UserLastNames { get; set; }
    public string UserPlatformName { get; set; }
    public string UserPassword { get; set; }
    public string UserGender { get; set; }
    public int UserDocumentNumber { get; set; }

    public virtual ICollection<Relative> Relatives { get; set; }
}

The procedure on the controller:

// POST: Users/UpdateUser
[HttpPost("UpdateUser")]
public async Task<ActionResult<User>> UpdateUser([FromBody] User user)
{
     var message = new Message<User>();
     string StoredProc = "exec SPUpdateUser " +
                               "@UserId = " + user.UserId + "," +                    
                               "@DateOfBirth = '" + user.DateOfBirth + "'," +
                               "@UserNames = '" + user.UserNames + "'," +
                               "@UserLastNames = '" + user.UserLastNames + "'," +
                               "@UserPlatformName = '" + user.UserPlatformName + "'," +
                               "@UserPassword = '" + user.UserPassword + "'," +
                               "@UserGender = '" + user.UserGender + "'," +
                               "@UserDocumentNumber = " + user.UserDocumentNumber + "";

    var user2 = await dataContext.Users.FromSqlRaw(StoredProc).ToListAsync();

    // message.Data = await dataContext.Users.FromSqlRaw(StoredProc).ToListAsync();
    message.ReturnMessage = "User updated";
    message.Data = user;
    message.IsSuccess = true;

    return Ok(message);
}

The context:

public class DataContext : DbContext
{
    public DataContext(DbContextOptions<DataContext> options)
            : base(options)
    {
    }

    public virtual DbSet<Relative> Relatives { get; set; }
    public virtual DbSet<User> Users { get; set; }
}

The stored procedure:

ALTER PROCEDURE [dbo].[SPUpdateUser]
    (@UserId INTEGER,
     @DateOfBirth DATE,
     @UserNames NVARCHAR(30),
     @UserLastNames NVARCHAR(30), 
     @UserPlatformName VARCHAR(30),
     @UserPassword NVARCHAR(15),
     @UserGender NVARCHAR(9),
     @UserDocumentNumber INT,
     @id INT = NULL OUTPUT)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE Users
    SET DateOfBirth = @DateOfBirth,
        UserNames = @UserNames,
        UserLastNames = @UserLastNames,
        UserPlatformName = @UserPlatformName,
        UserPassword = @UserPassword,
        UserGender = @UserGender,
        UserDocumentNumber = @UserDocumentNumber
    WHERE  
        UserId = @UserId   

    SET @id = SCOPE_IDENTITY()

    RETURN @id
END

I have to make a change in the stored procedure? Or in the code?

Answer 1

Looking at the code, it is updating the user that already has the Id, so, I'm not sure what you are trying to return from SP.

If you want to insert user via SP, change the SP and use the suggestions below.

Well, the reason you are getting this error is that you are using incorrect object to run your execute SP script. The code is using Users to execute SP that just returns an id. Ef cannot map the entity here.

You should do the following:

1. Never trust the data from the HTTP body it might be the cause of the SQL injection. Always use SQLParameter to store the value.
2. I would also remove the output param from the SP and just use simple SELECT to return the id.
3. To call the SP from Ef you should use dbContext.Database property.

Amend your SP code and use the following select instead of SET and RETURN. And you can delete the output param as well.

SELECT SCOPE_IDENTITY() AS [USER_ID]

In your c# code replace the lines

string StoredProc = "exec SPUpdateUser " +
                "@UserId = " + user.UserId + "," +                    
                "@DateOfBirth = '" + user.DateOfBirth + "'," +
                "@UserNames = '" + user.UserNames + "'," +
                "@UserLastNames = '" + user.UserLastNames + "'," +
                "@UserPlatformName = '" + user.UserPlatformName + "'," +
                "@UserPassword = '" + user.UserPassword + "'," +
                "@UserGender = '" + user.UserGender + "'," +
                "@UserDocumentNumber = " + user.UserDocumentNumber + "";
        var user2 = await dataContext.Users.FromSqlRaw(StoredProc).ToListAsync();

With the following:

var parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter("@UserId", user.UserId));
// and so on…add all params this way
var userId = await dataContext.Database.SqlQuery<int>(“SPUpdateUser”, parameters.ToArray()).ToListAsync();

Also, be careful with converting your model values to sql params, you might get errors related to date conversion from the datetime.