Finance Law

Stored Procedure returns value for a column but FromSqlRaw returns null for the class property

I have a class that is called by multiple methods. This class is:

public class Policy : EntityObject
{
    public Guid PolicyId { get; set; }
    public Guid CustomerId { get; set; }
    public string PolicyNumber { get; set; }
    [NotMapped]
    public string TrimmedPolicyNumber { get; set; }
    public DateTime? PolicyEffectiveDate { get; set; }
    public DateTime? PolicyExpirationDate { get; set; }
    [NotMapped]
    public string PolicyType { get; set; }
    public string InsuranceCompany { get; set; }
    public string WritingCompany { get; set; }
    [NotMapped]
    public string BillMethod_PaymentPlan { get; set; }
    [NotMapped]
    public decimal? FullTermPremium { get; set; }
    [NotMapped]
    public string AccountExecutive { get; set; }
    [NotMapped]
    public string AccountRepresentative { get; set; }

    [NotMapped]
    public string PolicyLineOfBusiness { get; set; }

    [NotMapped]
    public string Status { get; set; }

Now I have the following stored proc:

Create PROCEDURE [GetActivePoliciesByCustomer]
@CustomerId UNIQUEIDENTIFIER
AS
    SET NOCOUNT ON;
    Select
        c.CustId as CustomerId
        , p.PolId as PolicyId
        , p.PolNo as PolicyNumber
        , p.PolTypeLOB as [PolicyLineOfBusiness]
        , p.PolEffDate as PolicyEffectiveDate
        , p.PolExpDate as PolicyExpirationDate
        , cmp.Name as InsuranceCompany
        , wcmp.Name as WritingCompany
        , pr.Description as [Status]
    FROM
        Policies p
        (Further details have been omitted as it is not important)
GO

This stored proc returns data for all the fields listed above. However, when I make this following call:

public async Task<List<Policy>> GetActivePoliciesByCustomerId(Guid customerId)
{
    var activePolicies = await _context.Policy
        .FromSqlRaw<Policy>("EXEC [GetActivePoliciesByCustomer] @customerId={0}", customerId)
        .ToListAsync();

    return activePolicies;
}

During the debug session, I see that Status and PolicyLineOfBusiness is set to null. My suspicion is that the [NotMapped] attribute is preventing these fields from getting mapped and I have validated that. If I remove [NotMapped] attribute, I see that the Status and PolicyLineOfBusiness fields are populated. However, this class (Policy) is used by another call:

public async Task<Policy> GetPolicyByPolicyId(Guid id)
{
    var policyDetails = await _context.Policy
        .FromSqlRaw<Policy>("EXEC [GetPolicyDetailsByPolicyId] @policyId={0}", id)
        .ToListAsync();

    return policyDetails.FirstOrDefault();
}

The stored proc it is calling:

CREATE PROCEDURE [GetPolicyDetailsByPolicyId]
@PolicyId UNIQUEIDENTIFIER
AS
    SET NOCOUNT ON;

    SELECT TOP 1
        p.PolId as PolicyId
        , p.CustId as CustomerId
        , p.PolNo as PolicyNumber
        , p.ShortPolNo as TrimmedPolicyNumber
        , p.PolEffDate as PolicyEffectiveDate
        , p.PolExpDate as PolicyExpirationDate
        , p.PolTypeLOB as PolicyType
        , c.[Name] as InsuranceCompany
        , wc.[Name] as WritingCompany
        , p.BillMethod_PaymentPlan
        , p.FullTermPremium
        , e1.[LastName] as AccountExecutive
        , CONCAT(e.FirstName, ' ', e.LastName) as AccountRepresentative 
    From
        Policies p
        (Further details have been omitted as it is not important)

If I remove the [NotMapped] attribute from the aforementioed properties (PolicyLineOfBusiness and Status) and use the previous method (GetPolicyByPolicyId) to call the above stored proc, I get an exception:

System.InvalidOperationException: The required column 'PolicyLineOfBusiness' was not present in the results of a 'FromSql' operation.

So how do I solve the problem of making attributes optional and at the same time, they should be able to map to the fields returned by two different stored procs? If there’s a better way, I am open for suggestions. Thanks in advance.