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

Venkata Prasad Pinapathruni 1 Reputation point


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 in my console application?

Model Class:
public class LoginViewModel
public int UserID { get; set; }

    public string Username { get; set; }

    public string Password { get; set; }

    public string ProtectedID { get; set; }

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

Test Stored Procedure:

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


Backend Integration:

List<LoginViewModel> data= _context.LoginMembers.FromSqlRaw("[dbo].[aaa_TopXXUsersTest]")


public class TestDbContext : DbContext
            public TestDbContext (DbContextOptions<TestDbContext > options) : base(options)

            public DbSet<LoginViewModel> LoginMembers { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)

Entity Configuration:

class EntityConfig
        public static void Configure(EntityTypeBuilder<LoginViewModel> entityBuilder)
            entityBuilder.Property(o => o.Username);
            entityBuilder.Property(o => o.Password);
            entityBuilder.Property(o => o.ProtectedID);
           //tried IsRequired false but not wroked
            entityBuilder.Property(o => o.FirstName).IsRequired(false); 
            entityBuilder.Property(o => o.LastName);

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?

An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,179 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 111.6K Reputation points

    Consider this approach:

    select top(@NumToReturn) UserID, NULL as FirstName, LastName, Username . . .

    You can also add a parameter to stored procedure to optionally include the values.

    Or maybe define several models, perhaps using inheritance.

    0 comments No comments

  2. Karen Payne MVP 35,026 Reputation points

    Hello @Venkata Prasad Pinapathruni

    You can create wrappers for stored procedures (one way is using EF Core Power tools). In the following example I used a stored procedure with joins but the same works without joins. Full source.

    CREATE PROCEDURE [dbo].[uspCustomersByCountryIdentifier](@CountryIdentifier int)  
    SELECT        Cust.CustomerIdentifier, Cust.CompanyName, Cust.ContactId, CT.ContactTitle, C.FirstName, C.LastName, Cust.Street, Cust.City, Cust.Region, Cust.PostalCode, Cust.Phone, Cust.ContactTypeIdentifier, Cust.ModifiedDate,   
                             Cust.CountryIdentifier, CO.Name  
    FROM            Customers AS Cust INNER JOIN  
                             Contacts AS C ON Cust.ContactId = C.ContactId INNER JOIN  
                             ContactType AS CT ON Cust.ContactTypeIdentifier = CT.ContactTypeIdentifier AND C.ContactTypeIdentifier = CT.ContactTypeIdentifier INNER JOIN  
                             Countries AS CO ON Cust.CountryIdentifier = CO.CountryIdentifier  
    WHERE        Cust.CountryIdentifier = @CountryIdentifier  

    This class is used for returning data which uses a wrapper to get only first names as a list of string.

    The following gets first names

    await using var context = new NorthwindContext();  
    var storedProcedures = new StoredProcedures(context);  
    var results = await storedProcedures.CustomersByCountryIdentifier(12);  
    0 comments No comments