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

Question

Hello,

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
AS
BEGIN
    SET NOCOUNT ON;
    select top(@NumToReturn) UserID, LastName, Username,Password, ProtectedID from Users where Deleted = 0

END

Backend Integration:

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

DBContext:

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

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

Entity Configuration:

class EntityConfig
    {
        public static void Configure(EntityTypeBuilder<LoginViewModel> entityBuilder)
        {
            entityBuilder.HasKey(o=>o.UserID);
            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?

Answer 1

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.

Answer 2

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)  
AS  
BEGIN  
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  
END  

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