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

Question

I've just starting learning MVC6 with EF7. I have a stored proc that I'd like to return a portion of the fields that are in my model. If I don't return every field in my model, I'm getting "The required column 'FirstName' was not present in the results of a 'FromSql' operation".

Is there a way to get make some columns not required so I can return just a portion of the fields in my model?

model:

public class LoginViewModel
{
    [Key]
    public int UserID { get; set; }

    [Required]
    [Display(Name = "Username")]
    public string Username { get; set; }

    [Required]
    [DataType(DataType.Password)]
    [Display(Name = "Password")]
    public string Password { get; set; }

    [Required]
    [DataType(DataType.Password)]
    [Display(Name = "Protected ID")]
    public string ProtectedID { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }
}

My proc for testing:

CREATE PROCEDURE [dbo].[aaa_TopXXUsersTest]
@NumToReturn int = 10
AS
BEGIN
    SET NOCOUNT ON;
    select top(@NumToReturn) UserID, LastName, Username,Password, ProtectedID from Users where Deleted = 0

END

and last, my controller code:

public IActionResult Index()
{
    var user = _context.Set<LoginViewModel>().FromSql("dbo.aaa_TopXXUsersTest @NumToReturn = {0}", 20);

    return View(user);
}

If I include all the fields of my model in my stored proc the call work fine, but I can't seem to return just a subset. Is there a way to make some of the fields not required?

Solution 1

Used [NotMapped] attribute with first name.

The NotMappedattribute can be applied to properties of an entity class for which we do not want to create corresponding columns in the database.

public class LoginViewModel
    {
        [Key]
        public int UserID { get; set; }

        [Required]
        [Display(Name = "Username")]
        public string Username { get; set; }

        [Required]
        [DataType(DataType.Password)]
        [Display(Name = "Password")]
        public string Password { get; set; }

        [Required]
        [DataType(DataType.Password)]
        [Display(Name = "Protected ID")]
        public string ProtectedID { get; set; }

        [NotMapped]
        public string FirstName { get; set; }

        public string LastName { get; set; }
    }

Solution 2

this means that the column 'FirstName' is not being returned in the result set. Do a 'SELECT * FROM TABLE' to solve the issue.

Solution 3

It requires a Id column to be returned from the SP.

select top(@NumToReturn) 0 AS 'Id', UserID, LastName, Username,Password, ProtectedID 
  from Users 
 where Deleted = 0

Or Change UserID to Id from select and model

Comments

[Dinglemeyer NeverGonnaGiveUUp]

Don't forget to include using System.ComponentModel.DataAnnotations.Schema; I found this answer very helpful, thanks Sanjay Katiyar!!!!! :)