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

This solution could help you out.

First, we need to use inheritance.

Second, we have to create a discriminator to handle the SPs and classes.

Finally, we set the discriminator in the stored procedures.

Let's see some code.

1. Using your class:

public class Policy : EntityObject
{
    public Guid PolicyId { get; set; }
    public Guid CustomerId { get; set; }
    public string PolicyNumber { get; set; }    
    public DateTime? PolicyEffectiveDate { get; set; }
    public DateTime? PolicyExpirationDate { get; set; }
    public string InsuranceCompany { get; set; }
    public string WritingCompany { get; set; }
}

public class CustomerPolicy : Policy
{
    public string Status { get; set; }
    public string PolicyLineOfBusiness { get; set; }        
}

public class DetailedPolicy : Policy
{    
    public string TrimmedPolicyNumber { get; set; }
    public string PolicyType { get; set; }
    public string BillMethod_PaymentPlan { get; set; }    
    public decimal? FullTermPremium { get; set; }    
    public string AccountExecutive { get; set; }
    public string AccountRepresentative { get; set; }
}

2. Setting the discriminator:

public class Policy : EntityObject
{
    public Guid PolicyId { get; set; }
    // ...
    [NotMapped]  
    public string discriminator { get; set; } 
}

Then, fluent API settings

// ...
public DbSet<Policy> Policy { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
  // here you can use enum, byte, int, etc. instead of string.
  modelBuilder.Entity<Policy>()
    .HasDiscriminator<string>("discriminator")
    .HasValue<Policy>(nameof(Policy)) 
    .HasValue<CustomerPolicy>(nameof(CustomerPolicy))
    .HasValue<DetailedPolicy>(nameof(DetailedPolicy));
  // ...
}

3. In this step we have to pass the discriminator (class name) to the stored procedures.

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 

        // Add the discriminator here
        ,'DetailedPolicy' discriminator

    From
        Policies p
        (Further details have been omitted as it is not important)

Repeat in the other sp.

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]

        // Add the discriminator here
        'CustomerPolicy' discriminator
         
    FROM
        Policies p
        (Further details have been omitted as it is not important)
GO

EF Context calls:

using Microsoft.EntityFrameworkCore;
using Microsoft.Data.SqlClient;

// ...

var activePolicies = await context.Policy
    .FromSqlRaw("exec [GetActivePoliciesByCustomer] @customerId", 
        new SqlParameter("customerId", customerId))
    .AsAsyncEnumerable();

var policyDetails = await context.Policy
    .FromSqlRaw("exec [GetPolicyDetailsByPolicyId] @policyId", 
        new SqlParameter("policyId", id))
    .AsAsyncEnumerable();

Note: you may need to use AsAsyncEnumerable or IQueryable to cast your class correctly.

Update:

Note2: if the compiler shows problems, you have to fill all fields with null from the other derived class.

For instance:

CREATE PROCEDURE [GetPolicyDetailsByPolicyId]
// ...
        ,'DetailedPolicy' discriminator
        ,null Status
        ,null PolicyLineOfBusiness 
// ...

Here's an example with .NET Core 5 and EF Core 5:

public class Policy
{
  public int field1 { get; set; }    
  [NotMapped]
  public string discriminator { get; set; } 
}

public class Policy2 : Policy
{
  public int? field2 { get; set; }
}

public class Policy3 : Policy
{
  public int? field3 { get; set; }
}

Fluent API:

// ...
public DbSet<Policy> Policies { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
  modelBuilder.Entity<Policy>().HasNoKey();
  
  modelBuilder.Entity<Policy>()
    .HasDiscriminator<string>("discriminator")
    .HasValue<Policy>(nameof(Policy))
    .HasValue<Policy2>(nameof(Policy2))
    .HasValue<Policy3>(nameof(Policy3));

  //...
}

Custom stored procedure:

create procedure GenPolicyQuery
    @id int,
    @source varchar(10)
as

declare @field1 int, @field2 int, @field3 int

if @source =  'Policy'
    set @field1 = @id
else if @source = 'Policy2'
    select @field1 = 10, @field2 = @id
else if @source = 'Policy3'
    select @field1 = 10, @field3 = @id

select @field1 field1, @field2 field2, @field3 field3, @source discriminator

go

Results:

using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Data.SqlClient;
using Newtonsoft.Json;
// ...

var policy1 = db.Policies
  .FromSqlRaw("exec GenPolicyQuery @id, @source", 
    new SqlParameter("id", 10), 
    new SqlParameter("source", "Policy"))        
  .AsEnumerable();

var policy2 = db.Policies
  .FromSqlRaw("exec GenPolicyQuery @id, @source", 
    new SqlParameter("id", 20), 
    new SqlParameter("source", "Policy2"))
  .AsEnumerable();

var policy3 = db.Policies
  .FromSqlRaw("exec GenPolicyQuery @id, @source", 
    new SqlParameter("id", 30), 
    new SqlParameter("source", "Policy3"))
  .AsEnumerable();
    
Console.WriteLine(JsonConvert.SerializeObject(policy1));
Console.WriteLine(JsonConvert.SerializeObject(policy2));
Console.WriteLine(JsonConvert.SerializeObject(policy3))

// Result
/*
  [{"field1":10,"discriminator":"Policy"}]
  [{"field2":20,"field1":10,"discriminator":"Policy2"}]
  [{"field3":30,"field1":10,"discriminator":"Policy3"}]
*/

For the separate stored procedure you will need to have separate models as the column are different in both the stored procedures.

Your Policies class you have both PolicyType and PolicyLineOfBusiness properties.

in GetActivePoliciesByCustomer procedure the column p.PolTypeLOB is aliased as PolicyLineOfBusiness but in GetPolicyDetailsByPolicyId procedure the column p.PolTypeLOB is aliased as PolicyType

So, the origin column is the same for both properties, I'm not understanding the reason, but to avoid the exception on 2nd procedure you can modify it adding also the required output for PolicyLineOfBusiness :

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
        , p.PolTypeLOB as PolicyLineOfBusiness /* *** ADD THIS LINE  *** */
        , 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)

As PolicyLineOfBusiness property is present at your domain model. So the both SP must return that property value if you remove the attribute(NotMapped). But by removing that attribute your second SP will give an exception and the first SP will get data. So it means that the second SP does not want to return PolicyLineOfBusiness property.

Unfortunately, you can not do that as you write PolicyLineOfBusiness property mandatory by removing the attribute(NotMapped) at the domain Model property. So you have to return the property from the second SP if you want to keep the domain model the same.

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
        , "" As PolicyLineOfBusiness --Have to add this as the domain model contain this property
        , 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 you do not want to do this then create 2 different models for 2 SPs.