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?

Probably the way described [here](http://stackoverflow.com/a/34764698/315935) will be good for you. It allows to use EF7 without the requirement to define class like `LoginViewModel`. You will get the `dynamic` object (which you can cast to object) with all properties returned by you stored procedure. — Oleg, Apr 21 '16 at 19:08
I'd hoped that the easy way was workable on this, but your link provided what I needed. Thanks Oleg — Scott Taylor, Jun 02 '16 at 20:32

score 22 · Answer 1 · answered Sep 13 '18 at 07:16

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

Don't forget to include using System.ComponentModel.DataAnnotations.Schema; I found this answer very helpful, thanks Sanjay Katiyar!!!!! :) — Dinglemeyer NeverGonnaGiveUUp, Nov 08 '18 at 21:38

score 3 · Answer 2 · answered Oct 15 '19 at 13:19

3

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

answered Oct 15 '19 at 13:19

snnpro

193
2

score 2 · Answer 3 · answered Apr 23 '22 at 11:09

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

score 0 · Answer 4 · edited May 30 '19 at 07:56

0

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

edited May 30 '19 at 07:56

Alexandre Tranchant

4,426
4
43
70

answered May 30 '19 at 06:24

user3876914

1
1

score 0 · Answer 5 · edited Sep 23 '20 at 14:00

0

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.

edited Sep 23 '20 at 14:00

Dharman

30,962
25
85
135

answered Sep 23 '20 at 13:54

vh123

1

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

5 Answers5

Linked