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?

Answer 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; }
    }

Answer 2

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

Answer 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

Answer 4

Try removing this public string FirstName { get; set; }. Your stored procedure is not returning the value for Firstname field, but you are trying to accept it in your loginViewModel class in this string Firstname variable.

Answer 5

To addition current answers:

According to last version, FromSql changed. Instead of that we can use eather FromSqlRaw or FromSqlInterpolated

As mentioned in docs: FromSqlInterpolated is similar to FromSqlRaw but allows you to use string interpolation syntax. Just like FromSqlRaw, FromSqlInterpolated can only be used on query roots.

We can achieve excluding property from mapping, using OnModelCreating method:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
     modelBuilder.Ignore<Property>();  //property needed to be excluded
}

or: [NotMapped] attribute