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

Venkata Prasad Pinapathruni 1 Reputation point
2020-12-07T13:43:34.837+00:00

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?

C#
C#
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
    2020-12-07T14:57:29.99+00:00

    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
    2020-12-07T15:37:03.743+00:00

    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);  
    
    0 comments No comments